Report pulls from two queries

  • Thread starter Thread starter Jacques
  • Start date Start date
J

Jacques

Can I make a report pull from two queries. I tried creating and unbound
report, and adding text boxes that I specify QUERY.FIELD in, but it just
gives errors.
 
Yes, you can base your report on a third query that includes both of the
queries. You must however be able to join the two queries on a common field
unless you want a cartesian product.
 
The problem I'm having with that, is that one query produces TotalHrs to
date, and the other produces TotalHrs for the month. Joining them by the
JobTitle or OfficeBranch (the only fields I can join with) produces less
info from the one I didn't join with.
I can't seem to make two joins and one or both say "all records from one and
only some from the other" without getting the ambiguous join error, and I
need all records even if they don't match (because I need it to show zero
hrs).
 
You need to include the TotalHrs to date query, TotalHrs for the month query
and your office branches table in a query. Join OfficeBranchID in each
TotalHrs query to OfficeBranchID in your office branches table. Change both
join types to the one that says include all OfficeBranchIDs in your office
branch table and only those on the TotalHrs queries that match.
 
I did the below, but remember, I have to do it twice. I have to do it for
the OfficeBranch and the JobTitle.
The query below will not show one of the Hrs fields correctly, since I can't
join the JobTitle fields in the same query (not while a join says all
records from one...). What happens is, whichever source query (TotalHrs or
TotalHrs for the month) I pull the JobTitle field from, that's the Hrs field
that has the correct Hrs. I figured this out because I pulled both JobTitle
fields down and they don't match in the records.
I can't make another step (another query from that) without using the
JobTitle column because it won't show if I use it as a source query.

The way you suggested the Join works well, but it gives wrong results.

Help.
 
Back
Top