Re: Null Expression

Steve wrote:
Here is the entire SQL:

SELECT [Location Codes].DIVISION, [Location Codes]. [Location Name],
[Current Losses].[LOSS LINE], [Current Losses]. [FISCAL YEAR],
Sum([Current Losses].[TOTAL INCURRED]) AS [SumOfTOTAL INCURRED],
Count([Current Losses].[CLAIM NUMBER]) AS [CountOfCLAIM NUMBER]
FROM [Location Codes] INNER JOIN [Current Losses] ON [Location
Codes].[LOCATION CODE] = [Current Losses]. [LOCATION CODE]
GROUP BY [Location Codes].DIVISION, [Location Codes].[Location Name],
[Current Losses]. [LOSS LINE], [Current Losses].[FISCAL YEAR]
HAVING ((([Current Losses].[LOSS LINE])="WC") AND (([Current
Losses].[FISCAL YEAR])=2007));

Here is how the data is displayed with the exception being that the
purple region would not be displayed because their sum and count = 0.
How do I get the Division and the location of the purple region to be
displayed and their sum and count to be 0?

DIVISION Location Line FY SUM COUNT
ABC Blue Region WC 2007 1500 5
ABC Green Region WC 2007 1700 2
ABC Purple Region WC 2007 0 0
XYZ Pink Region WC 2007 2000 15

Why are you making me guess? :-)

I guess, extrapolating from what you described, that the Location Codes data
would look like this:
Location DIVISION Location
Code Name
1 ABC Blue Region
2 ABC Green Region
3 ABC Purple Region
4 XYZ Pink Region

Correct?
And the Current Losses data would look like this:

Location LOSS Fiscal TOTAL CLAIM
Code LINE Year INCURRED NUMBER
1 WC 2007 300 1
1 WC 2007 300 2
1 WC 2007 300 3
1 WC 2007 300 4
1 WC 2007 300 5
2 WC 2007 800 6
2 WC 2007 900 7
4 WC 2007 200 8
14 more records for code 4 totaling 2000 for total incurred

Do I have this correct? if so, you will need to use an outer join to a
subquery. Actually, a join to a saved query sounds like it might be a better
idea since it will make it easier to read and maintain the final result and
you may need to re-use that data aggregation. Start by creating a new query
with this sql (paste it into SQL View):
SELECT [LOCATION CODE], [LOSS LINE] As Line,
[FISCAL YEAR] As FiscYr,
Sum([TOTAL INCURRED]) AS [SumINCURRED],
Count([CLAIM NUMBER]) AS [CountCLAIMs]
FROM [Current Losses]
WHERE [LOSS LINE]="WC" And [FISCAL YEAR] = 2007
GROUP BY [LOCATION CODE], [LOSS LINE], [FISCAL YEAR]

Save the query as LossTotalsByLossLineAndFiscYr (or whatever you want to
call it).

Then, create a new query with this sql:
SELECT l.DIVISION, l.[Location Name],
Nz(c.[Line], "WC") As Loss Line,
Nz(c.[FiscYr], 2007) As Fiscal Year,
Nz(c.SumINCURRED,0) As Sum Of Total Incurred,
Nz(c.CountCLAIMs,0) As Count of Claims
FROM [Location Codes] As l LEFT OUTER JOIN
LossTotalsByLossLineAndFiscYr As c ON
l.[LOCATION CODE] = c.[LOCATION CODE]

Does that work?


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Null Expression
    ... How do I get the Division and the location of the purple region to be ... a join to a saved query sounds like it might be a better ... with this sql: ... FROM [Location Codes] As l LEFT OUTER JOIN ...
    (microsoft.public.access.queries)
  • Re: Null Expression
    ... How do I get the Division and the location of the purple region to ... Start by creating a new query with this sql: ... FROM [Location Codes] As l LEFT OUTER JOIN ... This email account is my spam trap so I ...
    (microsoft.public.access.queries)
Loading