MS Access 2003
I have an easy task, but not sure how to do it. I have three tables:
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
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.
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
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
** Remember to give credit where credit is due and leave reputation points where appropriate **
|Thread Tools||Search this Thread|
|Thread||Thread Starter||Forum||Replies||Last Post|
|SBS 2003 - Configuring RemoteApps? remoteWeb Access on SBS 2003?||malhotra||SBS 2000 / 2003||0||20th August 2011 07:08|
|access OWA to ex 2003||zilcali||Exchange 2000 / 2003||0||21st December 2009 21:18|
|Securing Windows server 2003 Remote Desktop access for access through the internet||Albertwt||Windows Server 2000 / 2003||7||19th August 2009 11:43|
|granting a user access on a server (2003 sbs) without granting access to server apps||akaljian||Active Directory||2||30th June 2007 12:47|
|Cannot access exchange 2003 using Outlook Web Access||Imran||Exchange 2000 / 2003||0||28th May 2007 14:48|