sum of two columns from two tables

[bfreeman]

new to sql...

trying to sum two columns from two tables.

Volunteers can earn hours in either TrailPatrol or Jobs. I'm trying to
get a total for all volunteers that have worked both TrailPatrol hours
and JobHours. So...

VolunteerID 117 has earned 1,000 hours overall.

Also, I assume that if a volunteer has no hours worked that it will
return a null value, so i need the null value to become zero...

Here is the code I've been working with:


Code:
--------------------


SELECT Volunteers.VolunteerID,
[LastName] & " ," & [FirstName] AS Expr1,
JobHours.Date, Sum(JobHours.Hours) AS SumOfHours,
Sum(TrailPatrol.Hours) AS SumOfHours1
FROM (Volunteers INNER JOIN (VolunteerJobs INNER JOIN JobHours
ON VolunteerJobs.VolunteerJobID = JobHours.VolunteerJobID)
ON Volunteers.VolunteerID = VolunteerJobs.VolunteerID)
INNER JOIN TrailPatrol ON Volunteers.VolunteerID = TrailPatrol.VolunteerID
GROUP BY Volunteers.VolunteerID, [LastName] & " ," & [FirstName], JobHours.Date
HAVING (((JobHours.Date) Between [Enter begin date:] And [Enter end date:])
AND ((Sum(JobHours.Hours)) Is Not Null));

 

[Michel Walsh]

Hi,


Maybe I would use an outer join Volonteers (an inner join only keep
records that have a match in the other table, and vise-versa).

SELECT Volunteers.VolunteerID,
LAST(LastName) & " ," & LAST(FirstName) AS Expr1,
Query1.Date,
Nz(Sum(Query1.Hours),0) AS SumOfHours,
Nz(Sum(TrailPatrol.Hours),0) AS SumOfHours1

FROM (Volonteers LEFT JOIN query1
ON Volunteers.VolunteerID = Query1.VolunteerID) )
LEFT JOIN TrailPatrol
ON Volunteers.VolunteerID = TrailPatrol.VolunteerID

GROUP BY Volunteers.VolunteerID, Query1.Date
HAVING Sum(JobHours.Hours) Is Not Null;




with Query1, from what I see is only used to get the date/time association
and some part of the worked time ( ? ) :


SELECT VolunteerID, Date, Hours
FROM VolunteerJobs INNER JOIN JobHours
ON VolunteerJobs.VolunteerJobID = JobHours.VolunteerJobID
WHERE JobHours.Date Between [Enter begin date:] And [Enter end date:]



Note that I spitted the query in two to avoid an "ambiguous join error".



Hoping it may help,
Vanderghast, Access MVP

 

[Jeff Boyce]

So far, from your description, there are only two tasks (TrailPatrol, Jobs)
for which Volunteers can earn hours. It sounds like you have two tables to
hold those hours.

What happens when you get a new category of ways Volunteers can earn hours?
A third table? Changes to all your queries, forms, reports and code that
refers to earning hours?

Consider revisiting the normalization of your data -- or consider using a
tool more suited to simple summing, like a spreadsheet.

The function you're looking for is:

Nz([YourFieldName],0)

This will convert a null value in YourFieldName to 0.

Good luck

Jeff Boyce
<Access MVP>