trying to Inner Join 3 database tables

[gregu710]

Hi, I need to do an INNER JOIN (I think) so that I can pull test data from 3 tables, and output them to another table. Two of the tables are test results, based on a unit Serial Number, and the 3rd table holds the Serial Number which is scanned in from a Part (so basically, I scan in a Serial Number from a Barcode, and check the Test Data to make sure the unit was tested successfully, and if so, that Serial Number is spit out to another table with the relevant test data). I've done this successfully with only ONE Table of Test Data, using INNER JOIN, but have been asked to include another set of Test data, and everything I've read seems to indicate that I should just modify my INNER JOIN, but keep getting syntax errors. Here is the original code where I have the Barcode Data and the Test Data (common element in all of these tables is the HousingID field).

INSERT INTO PassedMotorEOLTest ( Unit, HousingID, [TimeStamp], SerialNumber, TestNr, EOLPassed )


SELECT tempHousingIDScan.Unit, tempHousingIDScan.HousingID, MotorEOLData.TimeStamp, MotorEOLData.SerialNumber, MotorEOLData.TestNumber, MotorEOLData.Passed
FROM tempHousingIDScan INNER JOIN MotorEOLData ON (tempHousingIDScan.SerialNumber = MotorEOLData.SerialNumber) AND (tempHousingIDScan.HousingID = MotorEOLData.HousingID)
WHERE (((MotorEOLData.Passed)=True))
ORDER BY [MotorEOLData].TimeStamp DESC , tempHousingIDScan.Unit DESC;

AND HERE IS THE OTHER QUERY that needs to be combined with the one above:


INSERT INTO PassedMotorLeakTest ( Unit, HousingID, [TimeStamp], SerialNumber, TestNr, LeakPassed )
SELECT tempHousingIDScan.Unit, tempHousingIDScan.HousingID, MotorLeakTester.TimeStamp, MotorLeakTester.SerialNumber, MotorLeakTester.TestNumber, MotorLeakTester.Passed
FROM tempHousingIDScan INNER JOIN MotorLeakTester ON (tempHousingIDScan.SerialNumber = MotorLeakTester.SerialNumber) AND (tempHousingIDScan.HousingID = MotorLeakTester.HousingID)
WHERE (((MotorLeakTester.Passed)=True))
ORDER BY [MotorLeakTester].TimeStamp DESC , tempHousingIDScan.Unit DESC;

So, basically I want to "marry" these two queries and output the results to a single results table.

[June7]

Why are you duplicating data? Basic principle of relational database is to not duplicate data. If it is possible to relate these tables in an SQL in order to copy to another table, could just work with the query as a dataset for forms and reports.

Those queries are inserting into two different tables. What is the single results table you want to output to?

I think a UNION of MotorLeakTester and MotorEOLData tables will be required. Join that query to tempHousingIDScan.

Provide data and I will test idea. Can attach project to post.

[gregu710]

Why are you duplicating data? Basic principle of relational database is to not duplicate data. If it is possible to relate these tables in an SQL in order to copy to another table, could just work with the query as a dataset for forms and reports.

Those queries are inserting into two different tables. What is the single results table you want to output to?

I think a UNION of MotorLeakTester and MotorEOLData tables will be required. Join that query to tempHousingIDScan.

Provide data and I will test idea. Can attach project to post.

Hi June, I think I got it. Not trying to duplicate data, we have a sequel server that stores test data by a unique serial number, and each piece of test equipment creates it's own table on the SQL Server. But, I got lucky and was able to do an Inner Join using Query Design and finally got the syntax right. Here is what I came up with....


INSERT INTO tempMotors_to_Warehouse ( Unit, HousingID, [TimeStamp], SerialNumber, EOLPassed, LeakPassed )
SELECT DISTINCT tempHousingIDScan.Unit, tempHousingIDScan.HousingID, MotorEOLData.TimeStamp, tempHousingIDScan.SerialNumber, MotorEOLData.Passed, MotorLeakData.Passed
FROM tempHousingIDScan INNER JOIN (MotorEOLData INNER JOIN MotorLeakData ON MotorEOLData.HousingID = MotorLeakData.HousingID) ON (tempHousingIDScan.HousingID = MotorLeakData.HousingID) AND (tempHousingIDScan.HousingID = MotorEOLData.HousingID)
WHERE (((MotorEOLData.Passed)=True) AND ((MotorLeakData.Passed)=True))
ORDER BY tempHousingIDScan.Unit DESC , MotorEOLData.TimeStamp DESC;