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.