Group By Query Problem

[J. B. Roman]

Hello,

I am trying to create a query that will provide the first name, last name,
address, city, and state from a tblVoter table. The problem that I am
running into is that there are multiple voters living at the same address.
How would I construct a crosstab query (if that is the correct query to use)
to group the voters with the similar addresses together, additionally
displaying each of the voters first and last name that are living at the
same address. I am trying to construct this query for the a mass mailing
which has to be personalized and in addition, trying to reduce the total
number of mailouts. I am using Access 2003 on 3Ghz processor with 1 GB of
RAM. Operating System is Windows XP Pro.

Thanks in advance for any assistance,

J. B.

 

[Michel Walsh]

Hi,


If I use a generic case: Table1, two fields, (f1, f2). Desired result:
for each DISTINCT values of f2, concatenate (string) values of f1.


Step 1. Build a temporary table to hold the result.

SELECT DISTINCT iif(false, " " , null) As concat, f2 INTO temp FROM myTable


Step2. Make the update statement

UPDATE temp INNER JOIN myTable ON temp.f2=myTable.f2
SET temp.concat= (temp.concat + ", ") & myTable.f1




That's all. Sure, there is a step 0 that I dídn't mention: delete table
temp, if it already exists.

The concatenated result MUST NOT exceed 255 char., in any record.


Hoping it may help,
Vanderghast, Access MVP