Access2k I'm trying to fill a table from 3 source tables on condition...

[DaveLock]

Hi,

I have 3 tables of data created from different sources, each with the same 2 fields & I wanted to fill the 2nd field of another table with the data on condition.

The condition is because some sources have a higher known accuracy than other sources.

So, every table has a field named "SupplierCode" & a field named "SupplierName", There are 3 source tables (SourceTable1, SourceTable2, SourceTable3), & a final output table (ResultTable) which contains the desired SupplierCode's.

Where there's a match with ResultTable.SupplierCode & each of the SourceTable?.SupplierCode's, I want the ResultTable.SupplierName to be populated with SourceTable3.SupplierName data, then to be populated with SourceTable2.SupplierName data, then with SourceTable3.SupplierName data. But only if there's data.

So what I'm trying to say is SourceTable1.SupplierName data is most desired & SourceTable3.SupplierName data is least desired, but better than nothing. But I don't want a blank SourceTable1.SupplierName record to overwrite populated SourceTable3.SupplierName data. Sort of "only overwrite it if you've got data" condition.

I guess another way is to populate ResultTable.SupplierName with SourceTable1.SupplierName, then only populate SourceTable2.SupplierName into ResultTable.SupplierName if that record is still empty, & so on...

I hope this is making sense, sorry if it's vague. I have searched previous help here but I can't seem to make it fit.

My SQL statement so far is:
SELECT ResultTable.SupplierCode, ResultTable.SupplierName, SourceTable1.SupplierName, SourceTable2.SupplierName, SourceTable3.SupplierName
FROM ((ResultTable LEFT JOIN SourceTable1 ON ResultTable.SupplierCode = SourceTable1.SupplierCode) LEFT JOIN SourceTable2 ON ResultTable.SupplierCode = SourceTable2.SupplierCode) LEFT JOIN SourceTable3 ON ResultTable.SupplierCode = SourceTable3.SupplierCode;


This doesn't put the data into 1 column though (it shows all 3 sources), & it won't apply the condition, of course. Thanks in advance.


Dave.

[DaveLock]

Note, the ResultTable.SupplierCode is already populated with the desired codes, because that was the result of a SELECT DISTINCT query on another data table.

I thought this extra bit of info may be important. Thanks.


Dave.

[MMcCarthy]

Try this ...

INSERT INTO ResultTable (SupplierCode, SupplierName)
SELECT SourceTable1.SupplierCode,  
IIf(Not IsNull(SourceTable1.SupplierName), SourceTable1.SupplierName, 
IIf(Not IsNull(SourceTable2.SupplierName), SourceTable2.SupplierName, SourceTable3.SupplierName)
FROM SourceTable1 INNER Join SourceTable2
On SourceTable1.SupplierCode = SourceTable2.SupplierCode
INNER JOIN SourceTable3
ON SourceTable2.SupplierCode = SourceTable3.SupplierCode
 

This will populate ResultTable with new records corresponding to your requirements as I understand them.

[MMcCarthy]

However, if as you say the ResultTable is already populated with SupplierCode then you need to use an update query as follows ...

UPDATE ResultTable Set SupplierName=
SELECT SourceTable1.SupplierCode,  
IIf(Not IsNull(SourceTable1.SupplierName), SourceTable1.SupplierName, 
IIf(Not IsNull(SourceTable2.SupplierName), SourceTable2.SupplierName, SourceTable3.SupplierName)
FROM SourceTable1 INNER Join SourceTable2
On SourceTable1.SupplierCode = SourceTable2.SupplierCode
INNER JOIN SourceTable3
ON SourceTable2.SupplierCode = SourceTable3.SupplierCode
WHERE ResultTable.SupplierCode = SourceTable1.SupplierCode
 

[DaveLock]

Thanks so much for your help mmccarthy, it is much appreciated.

Unfortunately the code for the update query I tried (your 2nd post) gives the error:
Syntax error in query expression 'SELECT SourceTable1.SupplierCode

I tried to help myself rectify this error by:
- including a bracket before the SELECT at the beginning of line 2
- including a 2nd bracket at the end of line 4
- including a bracket after the FROM near the beginning of line 5
- including a bracket at the end of line 6
- including a bracket at the end of line 8

The code now runs, & looks like:

UPDATE ResultTable SET SupplierName=
(SELECT SourceTable1.SupplierCode,  
IIf(NOT IsNull(SourceTable1.SupplierName), SourceTable1.SupplierName, 
IIf(NOT IsNull(SourceTable2.SupplierName), SourceTable2.SupplierName, SourceTable3.SupplierName))
FROM (SourceTable1 INNER JOIN SourceTable2
ON SourceTable1.SupplierCode = SourceTable2.SupplierCode)
INNER JOIN SourceTable3
ON SourceTable2.SupplierCode = SourceTable3.SupplierCode)
WHERE ResultTable.SupplierCode = SourceTable1.SupplierCode

But when it runs, an input box appears with the title "Input Parameter Value", with the description "SourceTable1.SupplierCode".

To be able to advise which part of the SQL code is making the input box appear, I did some testing & found it to be the very last text after the equals on line 9. No matter what value I enter into the input box it only tries to look for one record.

As I said, thanks for your help on this. Could it be that line 9 should have nested IIF() statements as well?

Also, I'd like the number of source tables to increase to 5, How does that look for the code? I'm guessing deeper nesting on the IIF()'s & more on the INNER JOIN's.

Also, for simpler SQL code I did in Access2k for this, it uses a LEFT JOIN. Should the JOIN be a LEFT JOIN, an INNER JOIN, or doesn't it matter?


Dave.

[MMcCarthy]

Sorry this was my fault. Thats what I get for copying and pasting ...

Try this now

UPDATE ResultTable SET SupplierName=
(SELECT IIf(NOT IsNull(SourceTable1.SupplierName), SourceTable1.SupplierName, 
IIf(NOT IsNull(SourceTable2.SupplierName), SourceTable2.SupplierName, SourceTable3.SupplierName))
FROM (SourceTable1 INNER JOIN SourceTable2
ON SourceTable1.SupplierCode = SourceTable2.SupplierCode)
INNER JOIN SourceTable3
ON SourceTable2.SupplierCode = SourceTable3.SupplierCode)
WHERE ResultTable.SupplierCode = SourceTable1.SupplierCode
 

Also, I'd like the number of source tables to increase to 5, How does that look for the code? I'm guessing deeper nesting on the IIF()'s & more on the INNER JOIN's.

Basicly yes. If the above works try it yourself and let me know if you run into any problems.

LEFT JOIN would be appropriate if all tables don't contain the full supplier code list. However, it would only work in that case if one of the tables did. It's a different problem.

Have a look at this tutorial on SQL JOINS