473,320 Members | 2,097 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Noob question on Access table merging

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

Apr 4 '06 #1
2 1783
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*********************@g10g2000cwb.googlegro ups.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

Apr 4 '06 #2
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.
Apr 4 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dave Williams | last post by:
First off...total noob to VB. So far have learned a lot in 1.5 days and feel fairly comfortable at throwing screens up. However, I want to start writing forms that revolve around an access...
4
by: Tom Dauria | last post by:
I have an application that will be distributed remotely. In the Access application I am opening Word documents and mail merging. The Word documents are linked to a tmpLetter table. In my code I...
2
by: freeagent | last post by:
Help!! I am a complete Access newbie and I have to concatenate about 30 tables into one large table. All these tables have the same column headings as each other. For example, I have TABLE...
6
by: Sven Pran | last post by:
Probably the answer is there just in front of me only awaiting me to discover it, but: 1: I want to build a query that returns all records in one table for which there is no successful "join"...
2
by: VMI | last post by:
How can I dump a datatable into an Access table (which contais exactly the same structure)? Somebody suggested doing this with a dataAdapter. I don't want to run an Insert query for each record (I...
4
by: Farraige | last post by:
Hi I need your help... I am implementing the method that updates given table (table is represented as list of lists of strings) according to other table (some kind of merging)... This method...
1
by: Paul H | last post by:
I have a very simple database containing one table called "tblProducts". The fields are: ProductID, Description, Price. I have a folder on my PC that contains image files. The image file names...
8
by: babyangel43 | last post by:
Hello, I have a query set up in Access. I run it monthly, changing "date of test". I would like this query to be merged with a Word document so that the cover letter is created in Word, the fields...
3
tdw
by: tdw | last post by:
Hi all, I am trying to create an Access database for use purely as a more efficient way to enter fields into a Mail Merge for a friend who is an attorney. Currently, I am using Word's mail...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.