Householding a database

  • Thread starter Thread starter Chilepepper99
  • Start date Start date
C

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!!
 
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.
 
Back
Top