Householding a database

[Chilepepper99]

I need to know how to "household" a database using Access (or maybe merge
purge would be a better term?)

Basically what I've got is a list of people with their names, addresses and
other info. I want to merge any enteries with a duplicate addresses and save
all of the info from all of the duplicates into one single row, thus
"householding" the database.

Example:
If I have this somewhere in the database,
Jon Smith 123 Oak St. Anywhere, ST 55555 40 M
Jane Smith 123 Oak St. Anywhere, ST 55555 38 F

I want it to look like this:
Jon Jane Smith 123 Oak St. Anywhere, ST 55555 40 38 M F

Maybe not exactly like that, but I want to save all of the merged info from
all duplicate enteries into one row.

I'm fairly new to Access, but I know this kind of thing can be done.

Any help would be GREATLY appreciated!!

 

[Arvin Meyer [MVP]]

There is no built in mechanism to do this in Access (or any other system
I've encountered).

You can run a duplicates query on the addresses to find the matches:

SELECT Address, City, Zip, FirstName, LastName, Age
FROM tblMyData
WHERE (((Address) In (SELECT [Address] FROM [tblMyData] As Tmp GROUP BY
[Address],[City],[Zip] HAVING Count(*)>1 And [City] = [tblMyData].[City]
And [Zip] = [tblMyData].[Zip])))
ORDER BY Address, City, Zip;

Then you would need to write some custom VBA code to act on the query
results and either append the values into a new table, or to a text file.