MS Access 2003

Home Forums Office Office XP / 2003 General Issues MS Access 2003

This topic contains 1 reply, has 2 voices, and was last updated by Avatar Ossian 7 years, 3 months ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • Avatar
    shadragon
    Member
    #159372

    I have an easy task, but not sure how to do it. I have three tables:

    C
    MS
    S

    Each has different information in them as they come from three different sources that I do not control and import daily. There is no way to relate the info between as there are no common key fields.

    I want to take selected fields from each and put them into a fourth table, lets call it Table4. BUT I need to put the same info from each table into the same field of Table4

    Example:

    C has 12 columns (Account Number, Amount, Date, Notes, Reference…etc)
    MS has 7 columns (Account Number, Cost, Date, Time, Buyer, Name…etc)
    S has 9 columns (Reference, Price, Date, Commission %, Amount…etc.)

    In Table4, I want to cherry pick fields in those above tables into one.

    In Table C, “Amount” goes into Column 5 of Table4. I also need “Cost” from Table MS to go into the same column and “Price” from Table S in the same column.

    So if I do Account Number, C and MS go into column 1 of Table4, but as Table S has no account number field, it will be blank.

    Once done I can export Table4 to an Excel sheet so one of our people can fill in the blanks from their paperwork. See example below.

    1fa4cc19c734815a3908c0e6ac03a1644a423e337f3b752c19a3fcffd2f9ae526g.jpg

    Avatar
    Ossian
    Moderator
    #187002

    Re: MS Access 2003

    You should be able to do a query against the three tables and generate keys “on the fly” – something like
    SELECT * FROM C, MS, S
    WHERE C.accountnumber = MS.accountnumber AND C.reference = S.reference

    (note this is not proper syntax, just a hint)
    If I remember, Access supports various joins so an alternative would be

    SELECT * FROM C FULL OUTER JOIN MS ON C.accountnumber = MS.accountnumber (etc….)
    The outer join will select rows even if no matching one is found in the other table (see also left and right outer joins)
    Once you have everything selected, limit it to certain fields only

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.