Missing Query Results

[Guest]

I am trying to run a query [EMISSIONS] that calculates data from two other
queries
[GENERATOR] and [NATURAL GAS]. They are linked by a common field [MONTH]. If
data is available in both queries [GENERATOR and NATURAL GAS] it works fine.
But it data is only available in one of the queries [GENERATOR or NATURAL
GAS] then I do not get any results. I would like to get results based on the
data available even if it is null.

Any help would be great. Thanks.

Example:
[GENERATOR] data: in JAN, Generator 1 : 6 hrs
[NATURAL GAS] data: in JAN, BLDG 1 : 16 CCF
[EMISSIONS] JAN = .002354

However if:
[GENERATOR] data: JAN, Generator 1 : 6 hrs
[NATURAL GAS] data: JAN is null
[EMISSIONS] JAN = NO RESULTS

SELECT [Generator Records Query].ServiceMonth, (NGsum!Expr1*7.6)/2000 AS SP,
(([Generator Records Query]![Sum Of Hours]*1.93)+(NGsum!Expr1*7.6))/2000 AS
[PM-10], ((([Generator Records Query]![SumOfFuel
Used]*7.05)+(0.6*NGsum!Expr1))/2000) AS SOX, (([Generator Records Query]![Sum
Of Hours]*61.6)+(100*NGsum!Expr1))/2000 AS NOX, ((1.73*[Generator Records
Query]![Sum Of Hours])+(5.5*NGsum!SumOfCCF/10000))/2000 AS VOC,
((16.36*[Generator Records Query]![Sum Of Hours])+(84*NGsum!Expr1))/2000 AS CO
FROM [Generator Records Query] INNER JOIN NGsum ON [Generator Records
Query].ServiceMonth = NGsum.ServiceMonth;

 

[[MVP] S.Clark]

Read up on Outer Joins. They are what you typically use in this type of
situation.

 

[Guest]

Thanks. I will see if I can make it work.

Read up on Outer Joins. They are what you typically use in this type of
situation.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

I am trying to run a query [EMISSIONS] that calculates data from two other
queries
[GENERATOR] and [NATURAL GAS]. They are linked by a common field [MONTH].
If
data is available in both queries [GENERATOR and NATURAL GAS] it works
fine.
But it data is only available in one of the queries [GENERATOR or NATURAL
GAS] then I do not get any results. I would like to get results based on
the
data available even if it is null.

Any help would be great. Thanks.

Example:
[GENERATOR] data: in JAN, Generator 1 : 6 hrs
[NATURAL GAS] data: in JAN, BLDG 1 : 16 CCF
[EMISSIONS] JAN = .002354

However if:
[GENERATOR] data: JAN, Generator 1 : 6 hrs
[NATURAL GAS] data: JAN is null
[EMISSIONS] JAN = NO RESULTS

SELECT [Generator Records Query].ServiceMonth, (NGsum!Expr1*7.6)/2000 AS
SP,
(([Generator Records Query]![Sum Of Hours]*1.93)+(NGsum!Expr1*7.6))/2000
AS
[PM-10], ((([Generator Records Query]![SumOfFuel
Used]*7.05)+(0.6*NGsum!Expr1))/2000) AS SOX, (([Generator Records
Query]![Sum
Of Hours]*61.6)+(100*NGsum!Expr1))/2000 AS NOX, ((1.73*[Generator Records
Query]![Sum Of Hours])+(5.5*NGsum!SumOfCCF/10000))/2000 AS VOC,
((16.36*[Generator Records Query]![Sum Of Hours])+(84*NGsum!Expr1))/2000
AS CO
FROM [Generator Records Query] INNER JOIN NGsum ON [Generator Records
Query].ServiceMonth = NGsum.ServiceMonth;