Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2005
    Posts
    5

    Unanswered: database file transfer/merge

    hello,
    I am trying to merge two different database files that are currently in an Excel document. I do have Microsoft Access 2003.

    database number 1 has about 62,000 items in it. It has two different columns. one column has the part numbers. and the second column has the prices.

    database number 2 has about 20,000 items in it. it has two different columns. one column has the part numbers, and the second column has the full item description.

    is there a way I can send the prices and part numbers in database number 1 to their respective part number in database number two.??

    Please be aware that the two databases have a different amount of files in them.
    Most of the item part numbers in database 2 are in database 1.

    Here is an Example:

    Database 1.

    __________________________________________________
    item part number l Item Price l
    abcd3344 200.00



    Database 2.

    __________________________________________________ _
    item part number l item description l

    abcd3344 l This is the item specs......



    I would like the following result.

    Database 3.

    __________________________________________________ _
    item part number l item Price l item description

    abcd3344 l 200.00 l this is the item specs....




    I appeciate your help.

    BOB

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Im presuming then that database2 has distinct items i.e. no item repeated and all the items in db2 are in db1 if not we can adapt the query below
    what i think you need then is a simple join

    Code:
    SELECT table1.partnumber, table1.Cost, Table2.Description
    FROM table1 LEFT JOIN Table2 ON table1.partnumber = Table2.PartNumber;

    if you need all the records from both tables and they both have unique values within them then you can use something like this

    Code:
    SELECT table1.partnumber, table1.Cost, Table2.Description
    FROM table1 LEFT JOIN Table2 ON table1.partnumber = Table2.PartNumber;
    
    UNION
    
    SELECT table2.partnumber, table1.Cost, Table2.Description
    FROM table2 LEFT JOIN Table1 ON table2.partnumber = Table1.PartNumber;
    you could then run it as a make table if you really need a table but you should just use the query results

    to do this with excel functions look into the VLOOKUP Function, but with your amount of records it could take an age

    HTH
    Dave

Posting Permissions

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