Eliminating Duplicates

[rkapfer]

I've got a query that needs to make a table containing all fields from the source table for only unique phone numbers (multiple records contain the same phone number). Can I do this in one query?

SELECT DISTINCT [Student Info Table].[Parent 1 home phone], * INTO TABLE1
FROM [Student Info Table]
WHERE ((([Student Info Table].[Withdrawl Date]) Is Null) AND (([Student Info Table].[Stu Current Grade])<13) AND (([Student Info Table].[Contract Signed])=True));

This does not eliminate duplicates, I'm looking for an end result of only unique phone numbers along with the entire record.

 

[glynch]

You may not be able to get all of the fields into a set of records with unique phone numbers. Unless two records are exact duplicates, DISTINCT and DISTINCTROW won't filter out records with common phone numbers.

I'll take a guess at what you are trying to do and offer a possible solution.

Since you have data that has duplicates on some of the fields, especially a field like phone number, it means you are trying to hold multiple entities in one table. Try to figure out what data belongs in this table and what should go into another table.

Looking at the fields you have, it looks like you would be getting multiple records for siblings from the same household. Separate the household info (phone , address, parent name, etc.) from student info (grade, withdrawal date, etc.).

Hope this helps.