combine fields from different records

Seasider

New member
Local time
Today, 08:42
Joined
Jan 5, 2003
Messages
33
I have a mailing list in which there can more than one person from the same household. I need to be able to test for more than one record with the same address and then combine the names but with only one address on labels and/or form letters.

For example,

Address 1
John Jones
Judy Jones

Address 2:
Alex Frame
Gena Blue

Address 3:
Alice Brown
Fred Brown
Joy Brown

Address 4:
Lucy Green
Nita Red
Jack Orange

I have tried various possibilities, none of which even came close to working. I suspect I may have to first do a count of the number of people in a household but I'm not even sure how to do that.

Any suggestions?
 
>
I suspect I may have to first do a count of the number of people in a household
<

In VBA, you can loop through the records, without the need of doing the count.

However, I think you can do it just with two queries (type/paste each in the SQL View of a new query, using the correct table name and field names):-

Query1:-
SELECT [Address], [Name], "Name" & DCount("*", "TableName", "Address='" & [Address] & "' and [Name]<='" & [Name] & "'") AS NameNum
FROM [TableName];

Query2:-
TRANSFORM First(Query1.[Name]) AS [The Value]
SELECT Query1.[Address]
FROM Query1
GROUP BY Query1.[Address]
PIVOT Query1.NameNum;


DCount is used in Query1 to find Name1, Name2, etc for each Address.
Query2 is a Crosstab query based on Query1.

Run Query2.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom