Join table to itself?

[BascoBill]

Hi All,
I have a problem that I haven't ben able to figure out (or find the answer to.)
I am working with a single table that has town census data in it. My client wants to send out a mailer to a certain demographic. I have made an extract table (make-table query) that has all the records we need in it.
My problem is that each record is for one person. My client would like to send one to each household (people over 60). The table might look like this:

First Last Address
James Madison 141 Main St
Dolly Madison 141 Main St
Henry Madison 12 Bell Circle
Harry Potter 10 Circuit Court
Maude Frickert 1010 Wanderers Way

She would want to make one mailing label for the first two record, showing the two names:
James Madison
Dolly Madison
141 Main St
Etc, Ect.

I have to make the query so it has both names in it (if they are at the same address). I tried it and I get a lot of duplicate data:

Output:
First_1 First_2 Last
James Dolly Madison
Dolly James Madison

Does anyone have an idea how I can get this? I realize that when it is a single person (like Henry Madison, above) that the First_2 field will be empty, resulting in a blank line on the label - this is ok.

Any guidance would be much appreciated.
Thanks!
Bill

 

[JonFer]

If it doesn't matter which two people, do a group by on the address fields and then add two name fields:

Name1: Min([First] & " " & [Last])
Name2: Max([First] & " " & [Last])

Only print Name2 on the label if it is different than Name1.

 

[BascoBill]

Thanks JonFer!
It is close.. I am getting the combined records I was looking for now, but I am missing some records too. When I did the GROUP BY, I had to identify all the fields in the output as aggregates - I used Min for the First_1 and Max for the First_2 and Max for the Last. I have joined the two instances of the table on "Record_Sequence_Number" (unique).
Do you know why these records are missing from the resulting recordset?

Thanks again!!
Bill

 

[JonFer]

You don't need to join the table to itself. Just use a single instance of the table. The Group By on the address will produce what you want if you use Min() and Max() on the names.

 

[BascoBill]

Hey JonFer!
Got it!!! Thank you very much for the help.. I was lost.

Take care and have a great weekend!
Bill