A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

combine tables



 
 
Thread Tools Display Modes
  #1  
Old August 27th, 2009, 06:42 PM posted to microsoft.public.access.gettingstarted
CandiC
external usenet poster
 
Posts: 42
Default combine tables

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.
  #2  
Old August 27th, 2009, 07:06 PM posted to microsoft.public.access.gettingstarted
Jerry Whittle
external usenet poster
 
Posts: 4,732
Default combine tables

The best way is to never, ever have seperate tables for years, months, days,
projects, etc. You now know why.

Is there a primary key on either table? Something like a Customer ID field
where the data is consistent between the two tables? I.E. Custome 123 is the
same in both tables? If that is the case you could do an append query and
pour the records from 2008 into the 2009 table. You will get an error for any
duplicates, but Access will let the rest in.

If you don't have a primary key field defined, then you could create a
multi-field primary key based on all the name and address fields. HOWEVER if
someone even did something as small as AVENUE in one table and AVE in the
other, it will allow in both records.

Most likely you are just going to have to put human eyeballs on the problem
and manually update the 2009 table. While doing so, consider 2010 and how to
avoid this problem in the first place.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"CandiC" wrote:

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.

  #3  
Old August 28th, 2009, 01:52 AM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default combine tables

It depends how consistent the common data in the two tables are. If you can
identify a set of columns which you can be sure will have exactly the same
values in each table and will uniquely identify the customer, e.g. name,
address, city, state, then you can do so.

1. Start by making a copy of the full 2009 table and then add to its design
any columns which are in the 2008 table but not currently in the 2009 table.
Also add an autonumber CustomerID column to automatically provide a single
unique identifier for each row.

2. In this new table define the set of columns which will uniquely identify
the customer as its primary key, which you do in table design view by Ctrl-
clicking on each field, making sure you click on the field selector (the
little grey rectangle to the left of the field name), then right-click and
select 'Primary key' from the shortcut menu (it might differ slightly in
Access 2007, I'm not sure).

3. Firstly append all rows from the 2008 table to the new table using an
append query. Any rows for customers who already exist in the new table
won't be appended because of the key violation.

4. Then update the extra columns in the new table which you added in step 1
to reflect the columns from 2008 not in 2009. You do this with an update
query which joins the new table to the 2008 table on all of the set of
columns which will uniquely identify the customer, and updates each extra
column in the new table to the value of the corresponding column in the 2008
table, e.g. for a column named Foo you'd add the Foo column from 2009 to the
query design grid and in its 'update to' row put [2008].[Foo]

5. Remove the primary key designation of the set of 'customer identifier'
columns and make the autonumber CustomerID column the primary key.

You should then end up with a consolidated table which includes all customers,
with one row per customer, and all the data from both of the original 2008
and 2009 tables. Once you are happy that the new table does include all the
correct data you can delete the original two tables, which are now redundant.
But be sure that the database is backed up before doing this, just in case!

It should be stressed that the above does depend on the set of columns which
you use as the unique key for each customer must match exactly for every
customer who is represented in both tables. Even the slightest discrepancy
will mean that they will be treated as separate customers and added twice to
the new table, so you might have to do some manual 'weeding' afterwards.

Ken Sheridan
Stafford, England

CandiC wrote:
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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/200908/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:28 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.