[shadragon]

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.

[Ossian]

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