Query from a previous JOIN

[mtompkins]

Hello all -

Thanks for your assistance, my query building is a bit weak.

I have:

tblRoasts with fields comp1, comp2 comp3
tblInvTrans with fields TransDesc, ProductID

My intent is to build a query that will provide the following information. Comp1 - 3 all equal a unique entry in tblInvTrans under TransDesc. Each of these TransDesc entries have a ProductID I'd like to pull.

Any help creating this query would be greatly appreciated.

For those interested it is keeping track of types of coffee blends roasted by the beans and specifically from which country they are from. So any named Roast "Morning Roast" for example might be made of Colombian, Brazil and Mexican beans. Comp1-3 refer to the specific farmer's ID of the bag used (called a mark). I need to use that mark (TransDesc) to look up the country (ProductID) stored on the inventory table.

 

[PHV]

I'm not sure to understand your issue, so just a guess:
SELECT ...
FROM tblRoasts AS R INNER JOIN tblInvTrans AS I
ON R.comp1 & R.comp2 & R.comp3 = I.TransDesc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Sashanan2]

If I understand your question correctly, each record in tblRoasts has three columns (comp1, comp2 and comp3) which all refer to a record in tblInvTrans; three different ones. In other words, you have three relationships between the tables - all between 1 of the three comp fields and the field TransDesc which has all unique values.

If the above is correct, I'd say the query you are looking for is:

SELECT IT1.ProductID, IT2.ProductID, IT3.ProductID FROM tblInvTrans IT1, tblInvTrans IT2, tblInvTrans IT3, tblRoasts R WHERE IT1.TransDesc = R.comp1 AND IT2.TransDesc = R.comp2 AND IT3.TransDesc = R.comp3

That should look up the corresponding ProductID in tblInvTrans for all three of the comp fields.




"Any fool can defend his or her mistakes; and most fools do." -- Dale Carnegie

 

[lespaul]

Additionally, this is sometimes called 'committing spreadsheet' within your table and breaks normalization rules. You should read the document below and consider changing your structure if possible.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design