Query to look up tables

[nifty]

I have a simple database with 3 tables.
Table 1 has fields "Phantom" and "mA",
Table 2 has fields "mA" and "focal spot" where "mA" is the primary key.
Table 3 has fields "Phantom", "focal Spot" and "detector factor" where both "Phantom and Focal Spot are set to primary keys.
What I am looking for is a query in which both "phantom" and "mA" values are entered as input data and the query returns the detector Factor as output data. To do this I have attempted to use the "ma" from Table one to lookup the "focal spot" in table 2 and then to use this focal spot and and the "phantom value" to lookup Table 3 for the "detector factor" by linking the tables as shown in the attachment. Is there a simple solution(without writing SQL) by linking the tables and writing a query into which the "phantom" and "ma" data can be entered to output the "Detector factor". Many thanks for trying!

 

[sfreeman@co.mer]

I think you may have a basic design issue with your tables, as I see the same fields in more than one table. Normal design would link via a foreign key field.

Assuming blindly that your design is OK, simple input parameters work something like:

SELECT
TableThree.[Detector Factor]

FROM
TableThree

INNER JOIN
(TableOne INNER JOIN TableTwo ON TableOne.mA = TableTwo.mA)

ON
(TableThree.Phantom = TableOne.Phantom)

AND
(TableThree.[Focal Spot] = TableTwo.FocalSpot)

WHERE
(((TableOne.Phantom)=[Please enter Phanton value:])

AND
((TableOne.mA)=[Please enter mA value:]));


HTH

 

[nifty]

Tried solution

Thank you for trouble you took to write the suggested solution. I copied and pasted the SQL into the query and ran it: An unexpected Input box for TableTwo.Focal spot came up so I Put Square brackets on the Focal Spot in the SQL script.

The Script is now:
SELECT TableThree.[Detector Factor]
FROM TableThree INNER JOIN (TableOne INNER JOIN TableTwo ON TableOne.mA = TableTwo.mA) ON (TableThree.Phantom = TableOne.Phantom) AND (TableThree.[Focal Spot] = TableTwo.[Focal Spot])
WHERE (((TableOne.Phantom)=[Please enter Phanton value:]) AND ((TableOne.mA)=[Please enter mA value:]));

This gives me a parameter query. I was very much hoping to run an ordinary Query where the query table comes up and one enters the phantom and mA value and the query works out the focal spot (by looking up table 2) and then uses this focal spot and the entered Phantom value to look up the Detector Factor. I have not been able to structure the tables or the table joins to solve this problem.The answer seems so close but not quite there yet.
Thank you very much again for your contribution.
Declan.