Announcement

Collapse
No announcement yet.

MS Access 2003

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • MS Access 2003

    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.


  • #2
    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
    Tom Jones
    MCT, MCSE (2000:Security & 2003), MCSA:Security & Messaging, MCDBA, MCDST, MCITP(EA, EMA, SA, EDA, ES, CS), MCTS, MCP, Sec+
    PhD, MSc, FIAP, MIITT
    IT Trainer / Consultant
    Ossian Ltd
    Scotland

    ** Remember to give credit where credit is due and leave reputation points where appropriate **

    Comment

    Working...
    X