Eliminating duplicate unique data

[mkleino]

This is my dilemma: I'm working on an Access 97 dabase of medical residents, with one table for identification linked in a one-to-many relationship to another with multiple records for each year of residency. I want a query that generates a list of everyone who ever served a residency at a certain hospital prior to 1991, but with only one record per unique ID. I've already accomplished this in two ways; the problem is that the results vary and I don't know why.

Method 1:
I added the ID field, date and hospital code fields. I had one possible code for the hospital criteria, and used <=#12/31/90# for the date criteria. I made a table with the query, copied the structure to a new table, and set the ID field as the primary key in the new table. Then I appended the same information to the new table, resulting in an error that leaves out all duplicates in the ID field. This is actually a suggested method in MS Access help.

Method 2:
This method is suggested in the MS knowledge base article Q90809. In the design view, I set the total row to "First" for the date and hospital code fields, and "Group By" for the unique ID. While this did return results without duplication, it came up almost 300 people short of when I used method 1 (both methods returned over 4,000 records).

I double checked by changing everything to Group By and using method 1 again, and the 300 people had returned. I can't figure out why this is, although I did find that if I changed only the date field to "First", I could get most but not all of the people included, without duplicates.

The reason I don't just use method 1 is that I was hoping to use the query in a form, and I want to be able to utilize the current database information rather than relying on a table, which is only a "snapshot."

Any insight as to the causes of the discrepancy and how to solve it would be greatly appreciated.

 

[Pat Hartman]

You don't actually need to include the date in the recordset produced by the query so uncheck it. You only need it for selection criteria. The SQL will end up looking like:

Select ResidentId, HospitalId
From YourTable
Where ResidenceDt <=#12/31/90#
Group by ResidentId, HospitalId;

This query will return a distinct row for each combination of ResidentId and HospitalId for all records with a date <=#12/31/90#. The query can be used as the recordsource for a form but the recordset it produces is not updateable.

 

[mkleino]

Thanks very much, that did work!

I really have no experience using SQL and little even using the Total row, so I didn't realize that Where was what to use. I also found that Min would work, which makes sense in this case. It does irk me, however, that Microsoft would post such a solution without taking into account situations where it wouldn't work properly.