Help joining two queries

  • Thread starter Thread starter Fjordur
  • Start date Start date
F

Fjordur

Hi,
I have two queries regarding the tools people need to perform a mission.
Query "HasTools" gives me (PersonID ToolID QtyAtHand).
Query "NeedsTools" gives me (PersonID ToolID QtyNeeded)
Considering that some of the tools may be the same, some not.

I can't seem to build a query with columns
PersonID ToolID QtyAtHand QtyNeeded DifferenceQty
Am I taking things on the wrong angle? Can anyone help?
 
Create the query using all the fields you have listed except for the
Difference field. Calculate that figure in a report which uses the stored
query as it's recordsource.
 
Susan,
Thanks for helping.
Building a new query, based on both queries below, using all fields needed,
doesn't work. If I don't join the tables, it gives all combinations of all
tools (product of the two base tables). If I join the tables, it outputs
only the tools that are both needed and at hand.
I want in the final result one line per tool and per person, some tools are
needed and at hand, some are available but not needed, and some are needed
and not available.
About the difference, I agree it can be computed at run time.
 
Are the 2 queries joined by a common field? Actually, you shouldn't even
need to join to queries - simply create a new query including the fields
from whatever tables they reside in, after verifying that the relationships
exist and are correct.

If you are still having trouble, post your table structure and we'll dig
deeper.
 
Back
Top