Query for householding same address

  • Thread starter Thread starter Iris
  • Start date Start date
I

Iris

I am working with a large database (450K+ names) in
Access 2002. I need to household the addresses of the
same last name, but displayed with the two different
first names, e.g., Bob and Betty Whoever. I am a novice,
so it will need to be simple. My OS is Windows XP Prof.
 
Hi,



Assuming your table is like:

myTable
FullName, Address ' fields names


Then,

1- Create a table to get the result

DROP tempTable; ' to be sure the temporary table does not exist

SELECT Address, iif( False, " ", Null) As Concat INTO tempTable FROM
myTable GROUP BY Address;



2- Push the data into the temp table

UPDATE tempTable INNER JOIN myTable
ON tempTable.Address=myTable.Address
SET tempTable.Concat=( tempTable.Concat + " and ") & myTable.FullName ;


3- Your data is now in table tempTable.


Where do you type that? in fact, make 3 queries ( the DROP, the SELECT INTO
and the UPDATE query) and run them one after the other, as required.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top