Stuck on a query

[Guest]

I have a query that joins 2 tables. The 2nd table is a "units table" that
has unit_id and unit_name columns containing entries such as "mg" or "ml".
The query looks up some general descriptive info and then the strength of a
compound in the format: numerator amount, numerator unit_id, denominator
amount, denominator unit_id via a join to the units table. The denominator
units in the resultset is always the same as the numerator. Ignoring the
join for the moment it kind of looks like select compound_name,
compound_amount, compound_unit_id, diluent_amount, diluent_unit_id from
tablename ... Both compound_unit_id and diluent_unit_id are joined on the
unit tables' unit_id.

The record that should be returned is compound_name 10 MG 1000 ML. I get
compound_name 10 MG 1000 MG. I built the query using access' design view. I
know there has to be a way to be a way to do this. Any suggestions?

 

[Dale Fye]

Rich,

You did not give your entire table structure, but I think you need to
reference two copies of your Units table. Something like:

SELECT numerator_amount, numerator_unit_id, Units1.UnitName,
denominator_amount, denominator_unit_ID, Units2.UnitName
FROM yourTable
INNER JOIN [units table] as Units1 ON numerator_unit_Id = Units1.Unit_ID
INNER JOIN [units table] as Units2 ON denominator_unit_ID = Units2.Unit_ID

HTH
Dale