Unique records

[MarcVS]

I have a SQL SELECT statement that gives me a list of records. Some of them appear twice (or more) with the same ID. I tried the DISTINCT keyword, but then all the fields have to be the same. Here only the ID (and some other fields) are the same...

Is there another way of only seeing each ID once?

fe:
ID Name Reads
1 John Books
1 John Magazines
1 John Papers
2 Luc Books

Should give:
1 John
2 Luc

There are two tables in this example: Names and Reads, that are joined together in the SELECT statement...

Marc

 

[sko]

If all you're looking for is a list of people who read, try this:

SELECT distinct Names.ID, Names.Name
FROM Names INNER JOIN Reads ON Names.ID= Reads.ID;

 

[csbdeady]

I have exactly this problem in that If I do:

SELECT tblContact.ContactUID, tblContact.Title, tblContact.FirstName, tblContact.Surname, tblAddressC.Address1, tblAddressC.Address2,
INTO tblTempNewsletterRecipients FROM tblContact INNER JOIN tblAddressC ON tblContact.ContactUID = tblAddressC.ParentUID

I get a list of contacts with their addresses.

However if a contact has two address I get the contact listed twice, once with the first address and once with the second.

How do I change my SQL so that I only retrieve the first address + contact details and thus only have each contact listed with one address?

Thanks
-Colin

 

[scriverb]

Here you are. I couldn't find the thread.

You must perform an "aggregate" of the data rows. You want to GroupBy all datafield columns that are the same and perform a First on the field that you want the first to be selected. In the design wizard of your query click the button with the Totals mouseOver(looks like a cap. E). An additional row will appear that has GroupBy selected for all fields. Change the GroupBy selection in the tblAddressC.Address1, tblAddressC.Address2 columns to First. This should give you your requested results.

Good luck.

Bob Scriver

 

[csbdeady]

ok I'm really thick... in Access 97 I have the following wizards available to create a query:

Design view (not a wizard)
Simple Query Wizard
Crosstab Query Wizard
Find Duplicates Query Wizard
Find Unmatched Query Wizard

which one should I select? (I'm guessing the Find Duplicates?)

If I've already created the query using Design View, how do I add the First?

Thanks
-C

 

[csbdeady]

yep I am thick - by "E" for the totals you mean the Sum symbol *lol*.

Ok, I have this working now - many thanks!
-Colin

 

[scriverb]

I should have said a Greek "E". Epsilon?

Glad to have helped your problem.

Bob Scriver