Combining People from Same Household

[stuartk]

Hi All

We have a database for our guests where each person is a separate record.

Sometimes we send mailshots to all males or all females.

However sometimes we need to send a mailshot to ALL where the male and female reside at the same address.

Rather than send 2 letters to the same house is there anyway to amend our query to combine both into one for address label purposes?

Help greatly appreciated.

Regards

 

[DCrake]

Create a query that groups by address, postcode. Then you have to decide who's name is going on the envelope ?

 

[Galaxiom]

I think this is right but I haven't properly checked as I have to sleep.

Left Join the table to an alias of itself on the address fields. (Drag the table into the designer twice.)
The Left Join allows inclusion of those who live alone.

Add a condition on the alias tablename_1.namefield: <>tablename.namefield
This blocks the person joined to themselves.

Add another condition to the same column: Is Null
This is also required for the people living alone.

Then in a field cell enter something like

JoinedName: tablename.namefield & " and " & tablename _1.namefield

This concatenates the names together with "and" between them.

Similarly concatenate the address fields together from the original table.

Make another query to generate just the concatenated address. Turn on Totals and Group by this one field. (Or use a SELECT DISTINCT query if you know how). This makes one copy of each address.

Base a third query on the first and second joined on the concatenated address field. Also include the concatenated name and separate address fields too. Turn on Totals and use FIRST on the concatenated name.

This avoids the reverse name combination. Voila.

You can combine this all into subqueries if you like.

It gets tricker if you have three records with the same address.

 

[stuartk]

Create a query that groups by address, postcode. Then you have to decide who's name is going on the envelope ?

Hi Buddy

Not quite clued up with access.

Any advancement on how to create "query that groups by address, postcode"?

Regards

 

[jdraw]

Further to advice/comments from others, here is syntax and example that should help with the underlying concepts.
http://www.w3schools.com/sql/sql_groupby.asp