Remove duplicates in a query Results

I have a component on a database where users enter details about a specific incident. Upon clsoure of the incident form a query is run that append (or is supposed to append) certain field to another table. The second table has an auto number field but this is not included in the append query. The SQL for the query is...

INSERT INTO Observations ( referral_id, [Date], Observation, Logger )
SELECT Incident.referral_id, Incident.[date time], "Refer to Incident Report " & [forms].[incidentreport].[incidentid] AS Observation, [forms].[incidentreport].[logged by] AS Logger
FROM Incident
WHERE (((Incident.IncidentID)=[forms].[incidentreport].[incidentid]) AND ((Incident.ObsLogUpdate)=No));

There is only one primary key in the table to be appended to and that is the autonumber field and I have left this to Access to insert an autonumber when it appends the new data. Whenever I run the query I receive a key violation error. If the primary key is removed from the second table (that which is being appended to) I will get duplicate numbers created in what was the auto number field but no longer receive an error message.

Can anyone advice me please

Thanking you


just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.

What I want to display is only one record per entry containing an exact match for criteria X.

Finding it hard to articulate this but I would need something that defines:

If criteria X is the same, just display one record.

Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.


I have a query linked to a main frame database. One of the fields is [significance] and gets a number 1-20. Usually when this data is entered, it gets multiple significance numbers. This causes my query to return separate records for each significance number. For example if case number 123 is given significance codes 1, 5 and 12, then my query returns 3 records.

I need a query that will show all records one time that have a significance code other than 12. This would be easy if there were not duplicate entries for the same case number because I could simply say "Not 12". So in the example above, my query returns 2 records showing significance codes 1 and 5. But I don't want to see the record for case number 123 because it also has a 12 significance code.

So what is the best way to handle this?

I appreciate the help.


I have a list of clients in a query field and I want get a count of the number of unique clients. Some records have duplicate clients. Lets say there are a hundred records but only 50 unique clients. When I apply "unique values" in the query's properties, and then enter "Count" in the Sum field, it gives me a count of 100 not 50. When I remove the Sum field, it lists the unique values correctly (50).

Is there a way to find and remove duplicate entries in tables by using queries.....if so, how specifically?


My last thread was a mess and had incorrect bits so I've deleted it and redone it....

I’ve been trying to figure out this query in the design window and sql but I can’t. I would like to remove duplicates from the first two fields. The pracid field can have the same value as long as each (sub) value from patid has a different (unique) value. So if there are many duplicates matching only the first 2 fields I will only keep one of these (doesn't matter which).
So for example in the table below I would keep only the records in red.


pracid patid Readcode Eventflag Date
a6897 01p3 G311500 2 11/02/2008
a6897 01p3 G311100 2 25/02/2008
a6897 01p3 G311500 2 05/02/2008
a6897 01p3 G33..00 2 08/11/2007
a7840 00wD 662K000 2 08/05/2008
a7840 00wD G6...00 2 02/05/2008
a7840 00wD G64z.00 2 08/04/2008
a6897 01NQ 662K200 2 06/03/2008
a6897 01p3 7929400 2 10/04/2008


I have a linked Excel file which contains many duplicative values. I need to get the unique values of 1 field and put them into an existing table with an update query. I could figure out how to create a select query that removes duplicates but I can't use this as the source to update an existing table since I always get the message: "Operation must use an updateable query". I can create a "create table" query but I'd need an update query since I need to define a primary key on the table that is to be updated.

Is there anyway to do that....and is it possible to do it in 1 phase rather than creating a select query that filters out duplicate and then another query that would update my table?

Thanks a lot

I am having problems with duplicate records in a query. The reason for this is hard to explain and it's a very complciated database. I do not want to get rid of the "duplicate entries" (they aren't, really), but I do want to remove them from my report.

What can I do in a query to limit the report to one record per unique field of my choosing?

Thanks in advance for any help.

Hi All,

I have a table that has multiple records that are basically duplicates that differ in one field only. I want to get rid of all duplicates leaving 1 record which is the one with the highest value in this field. Can this be done with a query? or do I need to use VBA code to generate this result.


Hello All,

I am trying to remove duplicate rows of data in an access table where the duplicate data is from a different column.

For example, you'll from my example below (i hope it shows ok) that this output gives all the combination for 3 items of data (0000000001, 0000000478, 0000000664)

system_id1 system_id2
1 0000000001 0000021880
2 0000000001 0000000478
3 0000000478 0000000001
4 0000000478 0000021880
5 0000021880 0000000001
6 0000021880 0000000478

I want to create a query/method of elimating the duplicate rows so that we are left with (in this example) just the 3 unique combinations i.e rows 1,2 and 4.

I've tried self joins and other ideas but cannot find a way to show only the unique combinations.

Any ideas are much appreciated

I've searched the forum for the below question and have not found an answer. My apologies if I've missed it somewhere. I have limited Access knowledge and I have a database that's purpose is to count the number of cases an employee has closed.

The table I'm dealing with has three fields:
Staff_ID, Case_ID, Date_Closed

I want to produce a query that will return the Case IDs for the current month ONLY if they weren't worked in the previous month by the same Staff ID. It is possible for the same Staff member to touch the same case in multiple months but we only want to give credit to that Staff member if they haven't touched it in the past 30 days. It's also possible for multiple staff members to touch the same case. We want to give each credit, so long as they haven't touched within 30 days.

Staff_ID Case_ID Date_Closed
26001 44456748 9/22/09
26001 77888485 10/1/09
26001 44456748 10/2/09
27005 44456748 10/2/09

So if I'm pulling information for October on the above data then I would want the query to return:

Staff_ID Case_ID Date_Closed
27005 44456748 10/2/09
26001 77888485 10/1/09

The other records would be removed because the same employee touched the same case in the past 30 days.

Can someone advise how I would create the query? I would so much appreciate any help!!!

MS Access is proving to be a right pain. Can anyone help me with this been frying my head.

Got to create a report that shows one line per company with details A,B,C,D,E from my query showing me the relationship between Table X and Y.

For the love of god I cannot get this query to remove the duplicate rows based on colomn A. I have search high and low and tried all kinds of SQL codes.

Has anyone got any ideas? I tried Distinct but that doesn't work.

Here is the simplified basic code for the query I am running atm.



doesnt seem to do anything!? sorry quite frustrated and pressure is building up.

Hi Wondering if anyone can help.

I have a form, which is set up so that the user can select the search criteria from drop downs. The problem is I would like the date drop down to only allow the user to select an available date from a table of data hence I have linked the date dropdown to the table data.

Here is where the problem is:

I have duplicate dates for various bits of information stored in the table and when it is linked to the form dop down I will obviously get say 10 lots of 01/01/2009 and 7 lots of 02/03/2009 etc..etc.

Is there any way to filter out the duplicates in the form dropdown or is there another way i.e. to do query on the table data and have that feed into the form drop down or something. I just want one date for each available date instead of multiples.

Thanks in advance

Can I have a query find all duplicate recors in a single table? If so, how do I do this?

I am building a database to keep track of when equipment needs to be serviced and my format is something like this:

ID - Date ID - Equipment - Date Serviced - Date Due
1 ------- 1 ----- Item 1 --------- 1/1/09 --- 1/1/10
1 ------- 2 ----- Item 1 --------- 1/1/10 --- 1/1/11
2 ------- 3 ----- Item 2 --------- 2/2/09 --- 2/2/10
2 ------- 4 ----- Item 2 --------- 2/2/10 --- 2/2/11

I am trying to develope a query that would tell me all the items that are due, closest date first. My problem is I don't want I only want the newest due date for each item, so the previous years data would not show up. So my query for the items above would look like:

ID - Date ID - Equipment - Date Serviced - Date Due
1 ------- 2 ----- Item 1 --------- 1/1/10 --- 1/1/11
2 ------- 4 ----- Item 2 --------- 2/2/10 --- 2/2/11

I understand that it would be easier not to hold on to previous years dates but I need to keep them in there for our records. The only queries I've been able to build so far show me every single record. Does anyone have any idea on how I could do this?

I have an access database where I am trying to use a query to remove duplicates from a table. My Fields are: MRN, APPT MADE DATE, Division, and User in the table Failed Complex Scheduling.

I have selected the delete query to use, but I am unsure what my criteria should be for this situation. I need MRN and APPT MADE DATE to be duplicated in one or more rows. However, in those rows Division and User cannot be duplicated.

I started by doing a duplicate query so that all of my MRN and APPT MADE DATEs are narrowed down to just duplicates, now I am unsure how to get rid of duplicates in the Division and User fields.

I have been Googling and playing around with this for hours to no avail. I am fairly new at Access, VBA, and SQL (gotta start somewhere!). If anyone could lend a hand - it would really be appreciated.

Edited for more info: Adding Distinct after Select in SQL has not worked. Also, going to Property Sheet and changing Unique Values (and Unique Records) to yes did not work either.


I am hoping someone is able to help me, I have setup a new database but need some assistance in setting up some SQL code which I havent done before.

I have two tables, one is linked to Sharepoint (Completions) and one is linked to an excel file (Import). I need to run a query that goes through the Import table and removes any duplicate lines where the [Matter Number] and [Completion Date] are the same so that only one line is remaining. Once this has been done I need to compare the import table against the Completions table and again remove any entries from the import file where the [Matter Number] and [Completion Date] already appear in the Completions table.

Then anything that is left in the Import table needs to be moved across to the Completions table.

Thanks in advance for your assistance.


I am using Access 2010, and have come across this problem before which miraculously solved itself, but now it's come up again. The db is about 800MB in size, and the table I am using has just over 1000 records in it.

I have a list of samples, each with a unique id. When I go to add a new sample to the list, it tells me it can't do it as it would create duplicate values in the index, primary key, or relationship. I know for certain it is not a duplicate number, and I can put in completely random values and it always gives the same message. I normally add values to the table using an append query, but whether I use the query or try to type manually, it won't let me add the field. I have also removed the primary key from the sample number field, and it still gives the same message.

I am wondering if it is a relationship issue, I use the table in a query but it has worked fine for months and so I don't know why it would all of a sudden stop working now. If it is a relationship issue I wouldn't know how to start looking for it though.

Any help would be appreciated.

Hi Experts,

Need a small help in the below database attached , i need a query to delete the records as explained below :

in the database we have ID, Country Code, Country Tag, Service_Req, in these fields except the ID the other fields are having duplicate's

What i want is ?

If the ID is different and the country code is same and Country Tag is also same but has a different Service Number then it should not be deleted as shown below

I am working with MS Access 2007 and want to learn how I can remove duplicate entries from a table.

My table has two fields; one has markets, the second has products, and there are over a million rows of data. I now there are several thousand instances where one product appears twice in a given market.

Is there a way to query this table to eliminate all the duplicate entries within each market?

Thank you for any help.

Not finding an answer? Try a Google search.