what I am trying to do with that table is get a sum of all assets, a sum of all Customers, and a sum of all Employees only counting each Comp ID once. Each company that we deal with carries multiple of our products. So as you can see I currently have it in the table where it shows the different data for each product over 2011, 2012, and 2013. Obviously for this I cannot just sum the entire column, as I would have the same company summed multiple times. The thing that is making this challenging for me is that I need it summed for each individual year (2011, 2012, 2013), with the end result being a total number of employees, assets, and customers.
I was thinking that to do this I would need a Sumif formula with several other formulas inside of it, but cannot figure out what exactly I would need to do. I know that if I took it into excel I would be able to do it, but we have over 7000 different customers, each having anywhere from 3-20 different products, and for each of the years 2011-2013... so the data is just too massive to try to convert and manipulate in excel.
The reason that I am hoping to get a count of their customers, assets, and employees is so that we could then take the info from individual customers and find out that particular customer's portion of the total assets, customers, or employees. I guess the mock table that I made doesn't keep the formatting when I add spaces, so imagine that each pair of l l is a column and match that with the columns above.
I also need another column that has the 2012 and 2013 results
l Comp ID l Year l Prod Fam l Prod Group l Prod Code l Assets l Customers l Employees l Assets for 2011 l Customers for 2011 l Employees for 2011 l
l 1101232 l 2011 l Family A l Group A l Prod A l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group A l Prod A l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group A l Prod A l 51000 l 39 l 25 l
l 1101232 l 2011 l Family A l Group C l Prod B l 59000 l 33 l 28 l
l 1101232 l 2012 l Family A l Group C l Prod B l 55000 l 36 l 23 l
l 1101232 l 2013 l Family A l Group C l Prod B l 51000 l 39 l 25 l
l 1101232 l 2011 l Family B l Group B l Prod C l 59000 l 33 l 28 l
I have created a query which gives me a COUNT for a field type GROUP: SELECT AllPairAssignmentsQ.COID, Count(AllPairAssignmentsQ.COID) AS CountOfCOID FROM AllPairAssignmentsQ GROUP BY AllPairAssignmentsQ.COID;
AllPairAssignmentsQ is based on a union query of 5 tables. The problem I am having is that I am getting some duplicates which I need to eliminate. Each listing contains several fields. However their are two fields in the query which can be used to determine that entries are duplicated. If the combination of MAINCOUNT and COID are identical in an entry then only one of the listings needs to be counted.
The following is an example of my problem: MAINCOUNT COID 1201 HNVL 1202 HNVL 1203 HNVL 1203 HNVL 1203 COAC 1204 COAC
My existing query will return the following: COID COUNT HNVL 4 COAC 2
Since 1203 HNVL is duplicated I would like for the query to count only one entry so that the query returns the following: COID COUNT HNVL 3 COAC 2
Thanks for any help that can be provided in solving this problem
I am building a query that pulls from one databases but I am running into duplicates.
Here is the skinny:
From the [tbl_databases], I am setting criteria that restricts a column containing “Visio”, more specifically, “Not Like “*Visio*”. I am also setting criteria that restricts a column containing “OLD” (it is a Yes/No field).
.
The problem is that there are duplicate rows that contain both “Visio” and “Old. So, simply suing my criteria restriction are pulling out the Visio and Visio/Old orders as well as Old and Old/Visio order.
Can I create a subquery that contain the combination of “Visio” and “Old” to restrict the subset from being extracted twice?
I think I have the logic but not sure not to set it up.
OK, first time posting so I'll try to be clear here. I work for a special education agency and have created a database to track student/therapist information. Now, I have reports showing which kids are in which schools and who their therapists are. What I can't figure out is how to print a report, grouped by school, that will display which therapists are active in that school.... any ideas?
I have a field in a database that needs the data entered without any spaces and I'm trying to figure out how to set either the validation rules or the masks so that the system will either automatically delete any spaces or warn the user not to enter them.
My preference would be for them to automatically be deleted.
Any ideas, suggestions?
I can figure out how to make either all numbers or all letters, but the data is both so that doesn't work.
I have a query I'm creating that is using a joined view. I'm joining two tables and whereas the first contains unique data to each field the second contans class information so for example while all users are unique many may be assigned to the same class. When i use the join it therefore dupliactes all the class information in the query.
how can i set up the query so that the class information is only output once and not duplicated along with a user every time ?
I work with an Access database with about 20 tables that requires considerable manual data entry. Although it is time consuming, the bigger problem is the keypunching errors that inevitably occur. A colleague of mine said that some databases can be set up so that two (or more) individuals can enter the same data into the database (basically creating two separate, temporary databases). At the conclusion of this double data entry, the two databases can be compared to see where they differ so that corrections can be made...the logic being that it's extremely unlikely two people entering the same data independently will make the same keypunching error in the same field.
Just wondering if Access has this capability, or if a reasonable facsimile of this approach exists.
I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;
These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).
I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).
Creating a Query that would give me results showing in the attached file column D. I am trying to build volume incentive database to track how much we are going to save once we hit certain volume and rate drops.
I've become frustrated trying to work out a problem. I have a database of equipment which is loaned out, then returned and may then be subsequently loaned out again. I have a table called equipment inventory which has equipment listed as equipmentID. I can obviously create a list of what is the total list of equipment.
I have two other tables called Loan Occasions and Loan details. One loan occasion may have multiple pieces of equipment going out at the same time. When the Equipment is returned, the Return date is recorded and this may then go back into stock. However, the same piece of equipment may then be loaned out again and of course is no longer in stock but on loan. Subsequently, the same may happen again.
I have tried multiple ways to produce a list of what is actually in stock and what is on loan but I keep getting the wrong answers. I keep ending up with a list with duplicate items of the same equipment which has been lent out, returned and then lent out and returned again. I have set my Return date default to null but this doesn't seem to help.
Does anyone have a suggestion of how to solve this dilemma.
I am have trouble eliminating the $0.00 balances in a query. From what I can figure out is that even though the Balance appears as $0.00 in reality it could be anywhere from $0.0012 to $0.009 or something like that.
What I am trying to do re reconcile the Balance field. If the balance reads $0.00 I want it out of the query. Then I would like to find all the balances that appear to be $0.01 to a negative $0.01.
It seem that no matter what is use as critiera I still can't what I want.
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
I have a query for clients who come to our office looking for services. I only need to report 1 visit per client during the month, in other words if the client comes several times during the month, we only count 1.
When I run the query, it gives all the times the client came to the office, but I only need to show once.
Clients are being sorted by ClientID. What do I need to write in the Criteria field to filter clients from showing again ?
I have many queries that all do the same thing. The problem is that they're connected to specific controls on a form, so I have to make a new query everytime I make a new form to perform the same task as the others. This would be eliminated if there was a way to use the criteria the same way you would in code, like Me!controlname. Is there anyway possible to make these queries more universal so that they can be used by many forms?
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
hi, I have 2 Tables: tablea & tableb. The field names in both tables are: user_id, firstname, lastname, from these i have 2 querys (querya & queryb) both of which just look at the fields from the repective tables, & from this i have 1 form (entry form) that pics up the fields from querya. I would like to be able to enter details (records) into the entry form which puts all the data into tablea then automatically copies all the information into tableb, then automatically checks to make sure there are no duplicate records in tableb and finally deletes all the records out of tablea (probably after the form closes). This might sound a bit strange why i want to do this but I hope if its possible it could be used in a larger database.
I think i need to do querys but not sure what I need to do, Any advise would be very helpful.
I have two tables, tblCandidate and tblNewHire. I am trying to transfer candidates to tblNewHire based on the date they are hired. However, I would like the database to skip any records that are already in tblNewHire, just in case someone inadvertently puts in the wrong date when running the query.
I created an append query (SQL below) but this enters the data regardless of duplication. I need to be able to check both SSN and LastName against the new table and can't set either one to be unique values as it's possible there could be two candidates with the same last name.
Any way to do this by creating a recordset and looping through to check for dupes.
INSERT INTO tblNewHire ( SSN, FirstName, MiddleName, LastName, Phone, Email, EOD, HiringMechanism ) SELECT tblCandidate.SSN, tblCandidate.FirstName, tblCandidate.MiddleName, tblCandidate.LastName, tblCandidate.Phone, tblCandidate.Email, tblCandidateTracking.ActionDate, tblCandidateTracking.HireMechanism FROM tblCandidate INNER JOIN tblCandidateTracking ON tblCandidate.SSN = tblCandidateTracking.SSN WHERE (((tblCandidateTracking.ActionDate)=[forms]![frmNewHireMain]![txtEODDate]) AND ((tblCandidateTracking.LastAction)="EOD"));
Each month I get the previous months data. In that data, I have a list of names that have duplicates in them. Some of the names have last, first middle. Some have just last, first. I want to combine the like names and leave the unlike names not combined.
I want to combine in this situation ex. Smith, Peter Smith, Peter W
I do not want to combine in this situation ex. Smith, Peter M Smith, Peter W
My goal is to get the original name in a table with the combined name next to it.
Original NameNew Name Smith, PeterSmith, Peter Smith, Peter WSmith, Peter
I have produced a label printing query which eliminates duplicate addresses from a mail list. It works beautifully.
However, when I try to replicate with a different field criteria the new query fails to produce any output at all.
The SQL code I am using is as follows:
SELECT a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List] FROM [Mail List] AS a GROUP BY a.[Member ID], a.[Address 1], a.[Member Name], a.[Address 2], a.Town, a.PostCode, a.[e-Mail List], CStr([a].[Member ID])+[a].[Address 1] HAVING (((a.[e-Mail List]) Is Null) AND ((CStr([a].[Member ID])+[a].[Address 1])=(Select cStr(Min(b.[Member ID])) + b.[Address 1] From [Mail List] as b Where b.[Address 1] = a.[Address 1] Group By [Address 1])));
The working version has 'HAVING' e-Mail News =False instead of e-Mail List is Null. The former searches for an empty check box and the latter for an empty field.
I've been working on a procedure to step through the recordset and add the data one record at a time so I can get rid of the duplicates.
I've tried a few approaches, but this is where I'm at now.
Code:
Dim rs As DAO.Recordset Dim rsHH As DAO.Recordset Dim rsPhone As DAO.Recordset Dim rsEmail As DAO.Recordset Dim rsAddress As DAO.Recordset Dim rsPerson As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb
With a particular client, they want to display a subform in datasheet mode. They do not want an alternative like continuous forms. If I am displaying the subform in datasheet mode, is there any way to eliminate the header row which contains the names of the columns in the subform? I know how to remove the captions themselves but the header row is still there. I don't think it can be done but I wanted to check here with those more familiar with this.
Board Member(Lookup,integer) SAMGA(Lookup,integer) 44 Smith smith@abc.co.za 44 Board Member ADHTY 44 Smith smith@abc.co.za 44 Grower SAMGA 44 Smith smith@abc.co.za 44
I only want to show 1 row, based on the duplication of E-mail address. I know i should be using the row_number function, but cannot get to the result I want.
I have a master table which shows all transactions per record (person) over a financial year.
Each record person has a seperate package period over which their spend needs to be measured. Therefore although I have all their transactions for the year, I only want to sum their transactions between their given [start date] and [end date] which are in columns.
I need to be able to create a field which sums all expenditure per record between the start and end dates
Name Start Date End Date Invoice Date Amount
Matt 15/5/11 15/9/11 1/11/11 £100 Matt 15/5/11 15/9/11 7/7/11 £200 Matt 15/5/11 15/9/11 12/12/11 £200
In this case I would only want to sum 7/7/11 as this is between the start and end dates
I want to write something like sumif([Invoice Date] is between [start date] and [end date] - not sure where or how exactly
(The start date and end date will always be the same per person)
