Re: Noob question on Access table merging

Bubb wrote:
I have an Access database with one table that I use for stuff I sell
online. Each record has the following fields: Unique Id, Cost, and
Item Description. I just obtained some more stuff with its own
database, some of the items I already have in my database. The new
table has the same three fields. What I need to do is merge the two
database tables together so I have one table. I need to make the new
merged table have 7 fields: Unique Id 1 (from my original table),
unique Id 2 (from the new table), Cost 1 (original table) Cost 2 (new
table), Item Description 1 (original table) Item Description 2 (new
table), and then a new field that has the difference in the costs
fields IF the Unique Id fields match up. Any advice as to what would
be the best way to do this with would be appreciated, you have my
thanks in advance!


Yers,

Bubb

What you could do is link the second database/table to your database. Then write an append query.

A unique ID is really a UniqueID. If I'm reading you correctly, you might have the same IDs in both tables.

I would create a new table with your fields above and another field called ID or RecID or something similar that is an autonumber.

Now create a query with Table1 and Table2. Drag a relationship line between the two tables. Select your fields from both tables to be added to the new table. Make this an append query and append into the new table.

Now change the relationship line. Dbl-Click on it and select the option to select All records from Table1 and only those that match in Table2. Change the values that will be appended from Table2 to Null. In the Criteria row for Unique Id in Table2, enter Is Null (See UnmatchedQuery wizard when creating a new query). Run the query. This will now append all records from Table1 that don't have Table2 records.

Ex: For table2
Description2 : Null

Now reverse the process. Change the relationship line (via dbl-click) to select All record in Table 2 and only those that match in Table1. Change all Table1 values to be appended to null, and put back the values from Table2. Set the UniqueID criteria row for table1 to Null. Now run. This will append all records from Table2 that don't exist in table1.

You now have a merged table. You can now run an update query to change the values between those that have record data from both tables.
.

Relevant Pages

  • Re: Re-Seed in SQL Server
    ... Table1 and then you need to change the foreign key in Table2 to match the ... Make a backup of your database file in case anything goes wrong. ... Create a query based on Table1 and only include the fields RecordID ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Move record from one table to another
    ... Building a database isn't like building ... Set a "pushFlag" column in the record in table1 to a specific ... Sets the "status" field in the new record in table2 to a specific ... If a duplicate is not found, ...
    (comp.databases.ms-access)
  • Re: Incredibly slow query when asking for specific row, but not when asking f?r *
    ... table1.*, table2.*) is very quick, querying for any specific fields is ... What are the query plans? ... Probably the second query gives poor performance because a not so ...
    (comp.databases.sybase)
  • Re: Re-Seed in SQL Server
    ... Table1 and then you need to change the foreign key in Table2 to match ... Make a backup of your database file in case anything goes wrong. ... Change the query to an Update query. ... Under OldRecordID where it says Update To, ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Re-Seed in SQL Server
    ... Table1 and then you need to change the foreign key in Table2 to match ... Make a backup of your database file in case anything goes wrong. ... Change the query to an Update query. ... Under OldRecordID where it says Update To, ...
    (microsoft.public.access.tablesdbdesign)