Counting Unique ID's Without Duplicates
Jun 29, 2006
Hi,
I have a problem with getting the right result with a query.
To this topic I attached a part of my database including 2 queries. The queries are almost the same, except the first field. In query 1 is the total 'Group By' and in query 2 'Count'.The other fields are parameters, which are the same in both queries.
My problem:
If I run query 1 then the result is 31 rows with unique ID's. When I run query 2 the result is 35, because the query counts 4 duplicates.
The correct result I want is 31. That means count the unique ID's. I am able to show the ID's with query 1, but I am not able to count them correctly. I tried to add 'DISTINCT' in SQL view but that didn't help.
Also other solutions written in this forum didn't work.
Thank you if someone (with more query/SQL experience) is able to help me... :o
View Replies
ADVERTISEMENT
Jun 13, 2006
Hello everyone,
I have been working on a database that involves Clients and Group Meetings. I have a table for Clients (which includes their ID, Name, ect) and also a table for Groups(Group ID,Name,ect).This is set to keep the attendance of each group meeting.
I would like to do a count of all the members that belong to each group. Right now I have the query set to count the Client ID with a group by of the Group name. This ends up counting a Client ID more than once each time they attend that groups meeting. Once the client belongs to a group I would like it to not count them more than once.
It's probably something simple that I'm overlooking. I have showed other people and nobody has had any answers about being able to a count without counting duplicates.
Here is my SQL:
SELECT DISTINCT Count([tblMaster-GroupMeetDates].ClientID) AS CountOfClientID, [tblMaster-Group].GroupName
FROM [tblMaster-Group] INNER JOIN [tblMaster-GroupMeetDates] ON [tblMaster-Group].GroupId = [tblMaster-GroupMeetDates].GroupId
GROUP BY [tblMaster-Group].GroupName;
Thank you to anyone that can help
View 1 Replies
View Related
Jan 18, 2007
Hi guys,
I'm actually starting to use Access and I have a question about a query.
Basically I have a table that says:
ITEM ORDER CAT
A 1200 01
A 1200 02
B 1200 01
B 1100 01
I'd like to count the number of Orders per each item, without counting duplicates. The Output should be:
A = 1 Order
B = 2 Orders
But when I use the Count option on my query table under ORDER it calculates:
A = 2
B = 2
As if is counting the number of records.
How can I solve this problem?
Thanks so much
dante
View 3 Replies
View Related
Aug 16, 2006
Hi,
I have been trying for the last couple of week or so to get a query working.
Basically the query is used to show Hours worked by Post Code. Everything works fine and the query returns 'Hours Worked By Postcode' and Number of Records that the data was created from. (See Report in Attached DB)
However I have now been asked also to show the number of unique Members who by PCode make up the records.
So the report would look like:
By Post Code (See Report)
Number Of Members - Number Of Records - TotalTime
I am having problems returning the number of Unique Members who make up the data, in the query you will see Test and Test1 where I have tried to implement a unique count with no success.
Any help would be appreciated.
Thanks
Daz......
View 4 Replies
View Related
Jun 1, 2006
I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.
SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;
qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.
(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)
Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.
Any help is greatly appreciated. Thanks.
View 4 Replies
View Related
Feb 27, 2014
I have imported a large number of emails into a table tbl_requests.
I had intended to have unique file tbl_requests.date_opened unique, but have ended up with a lot of duplicate records (i.e. tbl_requests.date_opened is not unique !). How to delete any duplicates? I have 15,000 records...
View 3 Replies
View Related
Aug 4, 2015
I have a unique query which lists all the films that we are screening over the next 3 months. I have added a COUNT field so that I can see how many of each films we are screening.
The problem is that i get duplicates of some films - and this may be because we may hold several copies of some films. I have attached two images which might explain this better!
What I could do with is knowing how to make it so that i get a list of films booked and how many of each, regardless of which copy of the film is used.
The SQL is:
Code:
SELECT DISTINCTROW dbo_Films.[film name], Count(dbo_Films.[film name]) AS [CountOffilm name]
FROM ((dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN dbo_EventsFlicks ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID) INNER JOIN dbo_Venues ON dbo_EventsFlicks.venueID = dbo_Venues.ID
WHERE (((dbo_EventsFlicks.datefield)>=#8/1/2015# And (dbo_EventsFlicks.datefield)<#1/1/2016#))
GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised
ORDER BY dbo_Films.[film name];
View 4 Replies
View Related
Sep 11, 2013
I'm attempting to create an append query that will add new records only if there isn't an equivalent record already existing. Typically I would add the existing table to the query, and only add new records if the same do not exist. In this case, the table is maintaining records over time (start date and end date).
I have 4 tables:
[t_employees]
employee_ID
employee_name
[t_cities]
city_ID
citiy_name
[t_city_assignment]
city_assignment_ID
start_date
end_date
employee_ID
city_ID
[t_temp_employees]
effective_date
employee_ID
city_ID
I'm checking if [t_temp_employees].[effective_date] <> [t_city_assignment].[start_date]. However, if the employee has historical entries it will still add a record (in fact, it'll add multiple records).
How can I append a new record only if one for the same time period does not exist?
View 1 Replies
View Related
Jul 19, 2014
I have a report which is based on a query. The query combines information from TBLDwgReg and TBLDwgRegDtls. The unique field linking these two tables is DrawingNo. The query - QRYDtldDwgReg - is showing 99% of the information I want to see.
My problem is this - one drawing can be revised several times. I have my report grouped by IssuedBy and then grouped by DrawingNo. In the group footer for IssuedBy I want to count the number of drawings issued by a particular consultant. For example...the structural engineer has issued 17 drawings for a particular project but when I do a count it is returning a total 27 because some of those drawings have been revised.
I tried grouping in the query and counting the DrawingNo field there but that's not working either.
View 14 Replies
View Related
May 17, 2013
I have duplicate data in a cell, I want to hide duplicate data and display only non-duplicate data.. I changed the property sheet to only show unique values, but it keeps showing data I don't want to see...
View 3 Replies
View Related
Aug 13, 2013
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
View 6 Replies
View Related
Nov 2, 2014
I have a client database that has recently had multiple duplicate entries. I need to reduce or negate this erroneous activity. I have a client table where I record amongst others, the following;
key
[christian_name]
[family_name]
[dob]
......
I believe that to prevent duplicate entrie via form I have created an additional field called "unique" given it as a unique index which I want to have populated with the joined fields first_name & last_name & dob (IE johndoe01/01/90), and then as user enters a new client it wont allow a duplicate.
However I need to fill all the existing customers (3600+) with the relevant joined existing data. If I create an expression I can cajoin the fields in a select query but when I try to make an update query the same syntax comes up with empty fields.
select query sql that worked to show field ...
SELECT divers.christian_name, divers.family_name, divers.dob, [christian_name] & [family_name] & [dob] AS Expr1
FROM divers;
update query that was empty ..
UPDATE divers SET divers.[unique] = [christian_name] & [family_name] & [dob];
View 4 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
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.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
Dec 8, 2005
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
View 14 Replies
View Related
Jan 26, 2007
I have a report due the first of each week in which I need the cases open and cases closed for the previous week, the week two weeks prior and the 2007 and 2006 year to date on two different types of cases. I have a case management table with a field for Type of Case, date assigned and date closed that I uses in my queries. Presently I have two query, one that generates only Type 1 cases from the Case Management Table and another for Type 2. I then use the Type 1 Query in another query that limits the results for Type 1 cases to those opened last week, one for those open two weeks ago, one for 2006 YTD and one for 2007 YTD. In these 4 queries I have one field [Type of Cases] and I have the query count. I then do this for Type 2 cases and then go through the whole process to do Closed Cases. All my queries have criteria to automatically filter the dates to the time periods mentioned above. I then have one report query that I put all the number in for my report. This query has 16 fields with the numbers for each period, last week open and closed, 2 weeks open and closed, etc. I then generated a report that takes these numbers from my report query and puts it in a report format automatically. As you can imagine this takes some time to go through each query to generate these numbers, so I was wondering how I may do this differently. Also, I have experienced a problem when a field produces no records I get a blank sceen with nothing under the Count of column and get the same thing for my report. How can I fix this.
View 1 Replies
View Related
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
Nov 1, 2014
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
View 2 Replies
View Related
Jul 5, 2007
Hi. I have two tables, of which i have run a duplicate query, removed the duplicates by doing an "is null" criteria, and keeping that new table.
I know though there are still more duplicates but the names may not be exactly the same, say barclays and barclays plc. so i run another query on both tables to show me the first five letters of table 1 and table 2. I can run another "is null" criteria query to find the non duplicates there. these are the unique values.
But even if there are duplicates up to the first five letters the next letter may change, for instance barcleys tractors and barclays bank. So these arent duplicates.
So can anyone come up with a way of taking the duplicate ones according to the first five letters, and maybe duplicate ones up to 10 letters, and working out which ones are the unique ones. my brain is frying.
thanks
alex
View 7 Replies
View Related
Jul 14, 2005
Hi
I have imported a 1 column table from an external source for the sole purpose of deleting duplicates. There are over 400,000 rows. What do i need to do to identify the duplicates?
Thanks
View 2 Replies
View Related
Apr 11, 2007
I work at a hospital and deal with several thousand account numbers a day. I am working on an Access database that will handle some of my workload. Here is what I am facing. Lets say on some of the tables that I import there are duplicate account numbers with different totals in the end...
acct info info info amount
123456 xxxxx xxxxx xxxx 5.50
123456 xxxxx xxxxx xxxx 17.50
123456 xxxxx xxxxx xxxx 900.00
753951 xxxxx xxxxx xxxx xxxxxxxx
and so on...
is there a way that i can only show one account number but the total of the amount for the account? please help...this is desperately needed.
JG
View 6 Replies
View Related
Oct 9, 2007
Hey All, i'm looking for some help from one of the heavy hitters in the Access Forum. (Keep in mind i'm self-taught in access..never picked up a book on the stuff) So far i have done ok. I'v made many databases and in my opinion i have a firm grasp on the fundamentals.
I have come accross a database that I did not create which i am suppose to work on. I can do the work just find but i'm a little confused on the structure. when i looked at the relationships i noticed that many of the field names in several tables were bold (showing they were set as a primary key) So i looked at the design view of a few of these tables and noted that the primary key was set on 3 different fields. i also noticed that each field allowed duplication. Furthermore i noticed that on at least one of the tables, in the relationship window there was a relationship using the ID field which was a 1 to many. however there was a relationship on the other side of that table still using the ID field that was a many to one. How can a field have a one to many relationship and a many to one?? it can either be duplicated or it can't...right???
Anyways i also tried to duplicate this in a test database and found that when you make a table and then right click at the side of the field you wish to make as Primary key, that works fine, but doing it on another field simply switches the primary key to that field and takes it off the first one.
(as a side bar..is the only way to search this forum to go manually through all the threads to see if you can find what you need? aka...no search tools?? or did i just miss them?):confused:
View 9 Replies
View Related
Aug 11, 2006
I have a table that has country_code, Mfr_num, Item_num, catalog_code, mfr_obsolete_code. the key is on country_code, mfr_num, and item_num. I need to find duplicates of mfr_num and catalog_code. Can anyone point me in the right direction on this?
View 14 Replies
View Related
Jan 22, 2007
I have the data from a membership DB which has quiet a few duplicated Names and addresses (though they may have a 'type' field which is different)
I have identified these using the find duplicates wizard. Is it possible to use this query to delete the duplicated entries and leave one record.
Thanks
RussG
View 3 Replies
View Related
Jun 8, 2007
:confused: Duplicates
I have all my master data in my table which has a lot of duplicates (700 entries). I ran the find duplicate query offered by access, but exported the data to excel to delete all the duplicates.
My question is how do I import that updated excel list back into the duplicates query so that it updates my master data in my table?
Thanks
View 1 Replies
View Related
Sep 6, 2007
I do not understand why this is happening but I am getting some, not all, records duplicated in the Select query on two tables. (see attached)
Can anyone explain why AND how I can make it return without duplicates.
Cheers
View 3 Replies
View Related
Oct 31, 2006
Hi,
I have got a table that has following fields display on a form:
ClassNo Class Date ClassType ExecutionDate Order Quantity
The Table CLASSES has got many records which are duplicate. Mostly ClassNo is duplicated. I could have stopped duplication by restricting table not to accept duplicate values, but my requirement is such that i dont want to stop the table from accepting duplicate values. When i display the same table data on the form, all the duplicate entries are displayed.
All i need is to find a way to stop the FORM showing duplicate ClassNo Field. The Duplication restriction i wish to set only on ClassNo. If there are more than one ClassNo reptition than it should show that entry only once. For example the form looks like this:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
1
1
1
1
1
2 20-10-2006 A 20-11-2006 Ok 100
2
2
2
2
I wish to see the form this way:
ClassNo Class Date ClassType ExecutionDate Order Quantity
1 12-12-2006 A 12-01-2007 Ok 10
2 20-10-2006 A 20-11-2006 Ok 100
Regards,
Darno
View 3 Replies
View Related
