Householding a database

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chilepepper99 - 19 May 2008 15:59 GMT

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!!

Reply to this Message

Arvin Meyer [MVP] - 19 May 2008 16:34 GMT

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.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

>I need to know how to "household" a database using Access (or maybe merge
> purge would be a better term?)
[quoted text clipped - 21 lines]
>
> Any help would be GREATLY appreciated!!

Reply to this Message