COMBINE TABLES IN ACCESS

[CandiC]

I have two tables of customer information in Access which contain data that I
would like to merge into 1 new table labeled (2009 master mailer). The parent
table (2008) has historical data and includes some information similar to
table(2009), like customers information as well as new customer information.
the only common columns on the two lists are,
basic customer information such as name, address, city, state, postal,
homephone. However table(2009) includes two other columns of information,
"notes" "project" that I must be able to keep. Please advise the best way to
approach this situation.

 

[Dorian]

Is there a unique key in each table that can be compared to match records for
the same person?
Is there only one row in each table for each person?
Is there any case where there is a record for a person in one table but not
the other table?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[CandiC]

The unique key could potentially be the "address", the street address is the
only field I can think of that would match the customer information in all of
the tables.

Yes there is only one row in each table for each person.

Yes, both tables could contain the same customer or additional customers.
table(2008) is the historical data, table(2009) is new customer sales
information that contains some new and some historical customer data.

 

[John W. Vinson]

The unique key could potentially be the "address", the street address is the
only field I can think of that would match the customer information in all of
the tables.

Yes there is only one row in each table for each person.

Yes, both tables could contain the same customer or additional customers.
table(2008) is the historical data, table(2009) is new customer sales
information that contains some new and some historical customer data.

Sounds like you need to do some normalization! Customers are not Sales
Transactions, and Sales Transactions aren't Customers; they're different kinds
of entities, and each type of entity should have its own table. You're moving
in the right direction to consolidate 2008 and 2009 data but I would suggest
that you go a bit further.

Note that a street address is a poor choice of primary key: are "312 Main",
"312 Main St." and "312 Main Street" the same customer? Probably, but Access
sure won't see them as being distinct!

You'll want to use APPEND queries to consolidate the data... but the task may
be a bit bigger than it seems you're currently thinking!