Query using two separate query totals

[sesmock]

I'm trying to create a query using a table of user information and two
separate queries with totaled hours over a period of time. Each separate
query has a total number of hours used between a start date and end date. It
is possible that a user can use hours from either query. I would like to be
able to have the totals information from each hours query sorted by the
contact's last name. I so far have the contact's Name, the total hours from
one query, and the total hours from another query. When I run the query, it
shows the information from the first query and combines the totals from the
other query and doesn't show all of the users. Hopefully, I have explained
this correctly. Is there a way to do this?

 

[Daryl S]

sesmock -

You need to use your user table and both queries, since either query may not
contain all the users. You didn't provide any specs, but your query will
look something like this:

SELECT UserTbl.ContactID, UserTbl.ContactName, Query1.SumOfHours,
Query2.SumOfHours
FROM (UserTbl Left Join Query1 ON UserTbl.ContactID = Query1.ContactID) LEFT
JOIN Query2 ON UserTbl.ContactID = Query2.ContactID
ORDER BY UserTbl.ContactName;

 

[sesmock]

OK, so that worked great. One more question: Is there a way to remove those
users who did not have any hours at all? Right now, it shows the entire user
list.

 

[Daryl S]

You can add in a WHERE clause to include only those with some hours:

SELECT UserTbl.ContactID, UserTbl.ContactName, Query1.SumOfHours,
Query2.SumOfHours
FROM (UserTbl Left Join Query1 ON UserTbl.ContactID = Query1.ContactID) LEFT
JOIN Query2 ON UserTbl.ContactID = Query2.ContactID
WHERE nz(Query1.SumOfHours,0) + nz(Query2.SumOfHours,0) > 0
ORDER BY UserTbl.ContactName;