query logic for selecting records from 3 tables
I have tables A, B, and C each having the field PartNumber.
tables B and C also have the field DieSize.
I have a report that will use this query to display the record results on a label.
In English, this is the query:
Find the match in TableA.PartNumber and (TableB.PartNumber or TableC.PartNumber)
Return the DieSize.
There should be only one match.
Display PartNumber and DieSize on the report.
Thanks,
Brooks
tables B and C also have the field DieSize.
I have a report that will use this query to display the record results on a label.
In English, this is the query:
Find the match in TableA.PartNumber and (TableB.PartNumber or TableC.PartNumber)
Return the DieSize.
There should be only one match.
Display PartNumber and DieSize on the report.
Thanks,
Brooks
Last Comment
- try this:
SELECT TableA.PartNumber, TableB.DieSize, TableC.DieSize
FROM TableA, TableB, TableC
WHERE TableA.PartNumber = TableB.PartNumber
AND TableB.PartNumber = TableC.PartNumber
SELECT TableA.PartNumber, TableB.DieSize, TableC.DieSize
FROM TableA, TableB, TableC
WHERE TableA.PartNumber = TableB.PartNumber
AND TableB.PartNumber = TableC.PartNumber
ASKER
danishani:
My query looks like this;
Select TableA.PartNumber, TableB.Die_Size, TableC.DieSize
From (TableA INNER JOIN TableB ON TableA.PartNumber= TableB.PartNumber) INNER JOIN TableC ON TableC.PartNumber
WHERE TableA.PartNumber = "12345";
It returns no records. Can you paste your sql statement for your suggestion?
Thanks,
Brooks
OP_Zaharin:
It looks like your suggestion requires the partnumber to be in both TableB and TableC? The partnumber should exist in either one or the other.
Thanks,
Brooks
My query looks like this;
Select TableA.PartNumber, TableB.Die_Size, TableC.DieSize
From (TableA INNER JOIN TableB ON TableA.PartNumber= TableB.PartNumber) INNER JOIN TableC ON TableC.PartNumber
WHERE TableA.PartNumber = "12345";
It returns no records. Can you paste your sql statement for your suggestion?
Thanks,
Brooks
OP_Zaharin:
It looks like your suggestion requires the partnumber to be in both TableB and TableC? The partnumber should exist in either one or the other.
Thanks,
Brooks
- if partnumber exist either in B or C then:
SELECT TableA.PartNumber, TableB.DieSize, TableC.DieSize
FROM TableA, TableB, TableC
WHERE TableA.PartNumber = TableB.PartNumber
OR TableA.PartNumber = TableC.PartNumber
WHERE TableA.PartNumber = '12345';
OR
SELECT TableA.PartNumber, TableB.Die_Size, TableC.DieSize
FROM TableA
INNER JOIN TableB ON TableA.PartNumber = TableB.PartNumber
INNER JOIN TableC ON TableA.PartNumber = TableC.PartNumber
WHERE TableA.PartNumber = '12345';
SELECT TableA.PartNumber, TableB.DieSize, TableC.DieSize
FROM TableA, TableB, TableC
WHERE TableA.PartNumber = TableB.PartNumber
OR TableA.PartNumber = TableC.PartNumber
WHERE TableA.PartNumber = '12345';
OR
SELECT TableA.PartNumber, TableB.Die_Size, TableC.DieSize
FROM TableA
INNER JOIN TableB ON TableA.PartNumber = TableB.PartNumber
INNER JOIN TableC ON TableA.PartNumber = TableC.PartNumber
WHERE TableA.PartNumber = '12345';
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
That worked!
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
How is the relationship of your Tables?
Is table A linked to table B and C?
You can create a Query, link the tables together (A one-to-many to B and C) and show the Table B.DieSize and Table C.DieSize and see which one returns.
HTH,
Daniel