Selecting records where a field is duplicated

[MattFitzgerald]

Can you help I want to reduce the number of queries I use?

I have a union query which combines data from 2 different databases located at different sites. Both databases are identical each of these databases does not allow duplication of field [Vol Ref] this does not stop their being a duplication as that Vol Ref could be entered once at each site and I have a duplicate when they are merged. I am trying to create a report that highlights duplicates. Below is the start of the code for my query:-

SELECT Union_Combined_Cadbury_Data.[Vol Ref], Union_Combined_Cadbury_Data.[Customer Name], Union_Combined_Cadbury_Data.[GP Agent], Union_Combined_Cadbury_Data.[Admin Agent], Union_Combined_Cadbury_Data.[Lead Product Type]
FROM Union_Combined_Cadbury_Data;

how do I add Where count of [VolL Ref] >1

I know I could do this in 2 queries 1 that does a count of VOL with criteria >1 and a second that lists the details and links it by Vol Ref to the 1st query where the join looks at where the Vol Ref is exists in both queries.

I would prefer if it can be done just to use 1 query as I keep getting lost when looking at my growing number of queries which I probably could in 1 query rather than using multiple queries.

all help is much appreciated

[FishVal]

Hi, Matt.

Just simple table join like this below will return you the records where field [Vol Ref] has the same value.
[code=sql]
SELECT Table1.*, Table2.* FROM Table1 INNER JOIN Table2 ON Table1.[Vol Ref]=Table2.[Vol Ref];
[/code]

[NeoPa]

A good point from Fish.
Do you want all records with duplicates highlighted or do you just want all duplicate records (You may have answered this already but just to clarify)?

[MattFitzgerald]

I am not looking at finding the duplicates when looking at the 2 tables. I am trying to find the duplicates in the union query that combines the 2 tables.

As I have probably confused the issue with too much detail below I have what I am looking for in the simplest terms:-

I have a query which contains a [VOL Reference] and a [GP Agent] claiming they own the order . But I have duplicates of the [VOL Reference]. What I need is a report that looks at the query and gives me a list of duplicated [VOL Reference] along with the [GP Agent] so I can go to the agents to find out who the order actually belongs to so we don't pay both for the same deal.

I have only been able to do this in 3 steps

Step 1) a query that does a count of [VOL Reference] where the count of [VOL Reference] is > 1. This givies me a list of the duplicated [VOL Reference]'s but is a summary.

Step 2) a query that links the [VOL Reference] in my original union query to the the query in "Step 1" where the join type is on [VOL Reference] where both table are equal. This gives me the detail I need [VOL Reference] along with [GP Agent].

Step 3) Have a report that pull data from query in step 2

I would idealy be able to run the report directly off my union query or at least reduce the number of queries I use?

Hope this clarifies I think my methods are probably very inefficient and create lots of queries in my database.

[FishVal]

Hi, Matt.

Is this from post#1 still right?

I have a union query which combines data from 2 different databases located at different sites. Both databases are identical each of these databases does not allow duplication of field [Vol Ref] this does not stop their being a duplication as that Vol Ref could be entered once at each site and I have a duplicate when they are merged.

If so inner join of two tables by the field(s) expected to have intertable duplicates would be certainly preferable. It is more simple and runs much more faster than query including aggregate functions.

[NeoPa]

Matt,

It might be easier to start from the basic data that you hold, rather than your existing queries. Sometimes much better results can be achieved simply and directly from the data.
Here is an example of how to post table MetaData :
Table Name=tblStudent

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time