Noob question on Access table merging

[Bubb]

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

[tina]

to create the new table, copy the table that's currently in your db, and add
the extra fields (EXCEPT FOR THE "difference in cost" FIELD) - i'll call it
tblNew. the original table i'll call tblOne, and the table from the other
database i'll call tblTwo. create a Select query, adding tblNew and tblTwo
to it, and linking on the unique ID field with an INNER JOIN. add the "new"
fields in tblNew to the design grid. turn the query into an Update query,
and set the Update To line of each field in the grid to the corresponding
field from tblTwo, with the following syntax, as

[tblTwo].[FieldName]

recommend that you do NOT put a field in tblNew to store the difference in
cost between fields Cost1 and Cost2. that is a calculated value, and as such
should not be stored in a table. instead, calculate the difference
on-th-fly, whenever you need to display it in a query, form, or report.

hth
"Bubb" <sp************ @yahoo.com> wrote in message
news:11******** *************@g 10g2000cwb.goog legroups.com...

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

[salad]

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.