Pulling Multiple Identifiers for one Field where Date is the Same 2

[laina222]

I have a query I need to run where I have three fields for criteria. Field1 is an identifying ID#, Field2 is another ID# (so ID2) and Field3 is a Date. Field1 – ID can have one or many ID2, and the Date field is the Date that ID2 was created.
What I need to know is how I can pull all records for ID that have different ID2 with the same Date.
Example:
ID ID2 Date
10000000 501 9/9/04
10000000 567 9/9/04
10000000 456 9/9/04

10000001 658 9/1/04
10000001 528 9/1/04

Thanks for any help in advance!

 

[rac2]

This will show you the IDs and Dates that have multiple ID2s.

SELECT ID, Date
FROM myTable
GROUP BY ID, Date
HAVING COUNT(*) > 1

Assuming of course that you never have two rows with the same ID2. Which more or less implies that ID2 is the primary key for this table. Is it?


Then JOIN that query to the basic table. This acts like a filter, the first query identifies the IDs and Dates of interest; only rows that match those are of interest.

SELECT a.ID, a.ID2, a.Date
FROM myTable a
JOIN (
      SELECT ID, Date
      FROM myTable
      GROUP BY ID, Date
      HAVING COUNT(*) > 1
     ) b ON b.ID = a.ID
        AND b.Date = a.Date

 

[RickSpr]

If these are Jet tables, this syntax doesn't work, because subqueries can't be used in the FROM clause. You would have to store the first query as a query object, then use it in the second query with this syntax:

SELECT a.ID, a.ID2, a.Date
FROM myTable AS a
INNER JOIN <stored query name> AS b
    ON b.ID = a.ID AND b.Date = a.Date

This query then shows all the information for all ID2s, but only where there is more than one ID2 on a given date. Is that what you wanted?

Note: The Find Duplicates query wizard will generate a query that will do this for you, but it has a flaw which may cause additional records to be selected when you search multiple fields for duplicate data.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[laina222]

Actually ID2 is not the primary key. ID can have one or many ID2, and those can be the same ID2, provided the Date is different.

 

[RickSpr]

ID2 does not have to be unique for this to work, but there must be no completely duplicate rows (ID, ID2, and Date all duplicated).

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[laina222]

How cool! Thanks for all the help