Merging tables and making the sum of text fields the key field

[ejh]

I am pretty new to access and I need some help. Right now I have a bunch of tables filled with clients. Each table is the list of clients from a year, so I have a table for 2002, 2003, ect. Right now there is a autonumber field in each of the tables that acts as the key field. There are also three fields for the client's address, street, city, state and zip. I need to merge all the tables into one and have no duplicate address entries.

How should I go about doing this?

[rpeare]

Before you do this there are some things you have to consider

1. How are these tables related to other tables in your database, and will merging them on to one table affect the parent/child link between each table?
2. Can a customer have multiple address? (non-identical)
3. How restrictive was your data entry, for instance if you have 1313 Mockingbird Lane on one address for a customer and 1313 Mockingbird Ln. for the same customer on a second table although the address is the same you are doing a text comparison so they will look different to Access

item 1 is probably the biggest thing you need to consider. Changing the primary key of a table is no small thing if there are dependent tables.

In reality all you have to do is create a fourth table and append the contents of all three tables to it (using an append query). If you set up key fields (not a unique identifier) that prevents duplicates you can eliminate a lot of the work assuming the address is typed the same from year to year.

[ejh]

The tables are completely independent, they were just imported from excel recently.
2.No, a customer should not have more than one address, if they do it should count as two records anyway.

3. Since the tables were just imported from excel, the entries for the addresses were not limited in any way. I planned to search for duplicates after merging the tables.

Should I use a auto number for the key field for the merged table or should I use the address? Also, how do I concatenate the address fields to make a calculated field with the full address?

[rpeare]

yes, use an autonumber field for the primary key, it's just easier. Just create a new table with an extra field (the autonumber) and import all your tables using append queries.

Never, never, never use fields with addresses or person names or anything that could possibly change, a completely independent numbering system accomplishes this.

In a query you can concantenate fields using the & symbol

for instance

lastname & ", " & firstname

will produce a result like

Mouse, Mickey

when you view it. You'd do this in a query or in a text box on a form or report.

[ejh]

ok thanks. If I have any problems ill be back


Just one more noob question, how do I ensure there are no duplicate addresses? as in, if the user inputs an address in a form and that address is already in the table it should throw an error.

[rpeare]

you can do a couple of easy things. When you create your table that's going to hold your combined data set a primary key that covers your the fields you don't want to duplicate (address1, address2, city, state, ziip for example) when you do the import any duplicates will automatically be dropped from the table.

If you don't do this, you can import all your data as previously discuss and run a 'find duplicates query' which you can build through the query wizard so you can browse through the duplicates yourself to determine if they actually are dups.