Outer Joins???

[esi]

Running the following query:

SELECT M.Period, D.[Department Number], REG.Total_REGHrs, OVT.Total_OVTHrs
FROM tblMonth AS M INNER JOIN ((tblDepartments AS D INNER JOIN
qryTotalOVTPayroll AS OVT ON D.[Department Number] = OVT.Dept) INNER JOIN
qryTotalREGPayroll AS REG ON D.[Department Number] = REG.Dept) ON (M.Period
= OVT.Period) AND (M.Period = REG.Period);

Both the REG and OVT queries are based on table called PAYROLL which
contains all our payroll data for each pay period(every 2 weeks). My
problem with this query above is that in a given M.[Period] for each
D.[Department Number] my data may not have any OVT.[OVTHrs]. This query
will not show me the null value for [OVTHrs], it will just not show the
record for the PERIOD and DEPARTMENT NUMBER at all. I'm thinking I may need
some outer joins? Do I need to join the REG and OVT queries before running
this query? My tables/queries are structured as follows:

qry REG:
Period
Dept
REGHrs

qry OVT:
Period
Dept
OVTHrs

tbl M:
Period

tbl D:
Department Number

 

[Kathi]

Try the NZ function. This is Null to Zero and will insert
a zero for calculation if the field is null. NZ([Field1])
is the format.