Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    2

    Unanswered: Merging tables in Access

    I have two tables in Access that I would like to merge into one table. One table contain shipment info and the other shipment costs. The structure of the two tables is different and there is only one common field. This is a rough sketch of the two tables:

    Table1:
    ShipID--Dest--Orig--Volume

    Table2
    ShipID--Revenue--FuelCharge--SecurityCharge

    Desirable result:
    ShipID--Dest--Orig--Volume--Revenue--FuelCharge--SecurityCharge

    How do I merge these two into one?

    TIA

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The easiest way would be to do a make table query - have a look in the query designer on how to create a make tabe query

    alternatively if you don't want to go down that pathway...
    create a new table which as all the desired fields (copy the table with the pargest number fo fields, then using a judicious bit of cut and paste copy the other fields into the new table). IMPORTANT define any column as an autonumber
    create a query which links the two old tables (making sure the display / column order is exactly is the same
    view the results of the query
    select all the records
    copy and paste the records into the new table
    define the required column as autonumber (if required)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello po2206!

    Try with two Queris. First Append query, and second Update query.

  4. #4
    Join Date
    Mar 2006
    Posts
    2
    Table2 contains three times as many rows as table1 and therefore the manual solution suggested (by Healdem) is not an option (there are 800K rows so manual work is not my favourite ;-))

    I'm quite new to Access, but have tried the append query but have not managed to make it work. Maybe you could be a bit more specific or provide some good links, on how to apply the append query in my case. Is it neccesary for my ShipID to be primary key?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no what i suggested you should do was a make table query
    the other alternative was if you don't fancy doing that

    failing that you could modify one of the tables with the new columns then do an update as MStef_ZG suggests
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372

    Hello po2206!

    Look at "DemoMergingTA2000.mdb".
    Look at tables. Run query1, look at table3.

    In your App do next:

    1) As Healdem suggested, make a copy of Table2 in Table3.
    2) Open Table3 in design view.
    3) Add Fields "Dest3", "Orig3", "Volume3".
    4) Make a query same as Query1.
    5) Run query.
    6) Look at Table3.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •