Query with some sort of sum?

[fat controller]

Doh!

Yes, I do need the live employees to exclude leavers (or to include returners as sometimes happens). Works perfectly now, thank you.

 

[isladogs]

Great. Job done?

 

[fat controller]

I reckon so - until another curve ball arises (which I have a feeling is in the offing with this one.....); apparently, this 'issue' has not been resolved for many, many years (20+ at least) as prior attempts to work it out have been excel based and with not an ounce of logic or finesse. I now have logical, answers and reporting for all the data going back into the sixties - BUT, as there is a financial side to this, I have a feeling that the financial side is badly skewed as a result of the poor/incorrect data, and will now need some sort of correction.

The trouble with looking good at resolving a big hot potato like this one is that you instantly put yourself in the frame to solve the next bit of the puzzle.....

 

[isladogs]

The trouble with looking good at resolving a big hot potato like this one is that you instantly put yourself in the frame to solve the next bit of the puzzle.....

I know that only too well.
If it causes you too much extra work, we could assist you by providing mediocre answers instead... just to help you out

 

[fat controller]

Trust me, mediocre is what my place is good at! I am slowly trying to drag them into this century.....

 

[Pat Hartman]

It is important to know that:

Nz(Sum(NumAccidents),0) AS TotalAccidents

is different from

Sum(Nz(NumAccidents,0)) AS TotalAccidents

As it happens, Sum() ignores null values so there is no difference as they stand.
However, change to :

Nz(Sum(NumAccidents + OtherValue),0) AS TotalAccidents

and there is a real difference with

Sum(Nz(NumAccidents,0) + Nz(OtherValue,0)) AS TotalAccidents

I'll leave it to you to figure out.

 

[isladogs]

Hi Pat

Just in case anyone reading your post was a trifle confused, I thought it might be useful to illustrate this with some real data of my own

The attached database has approx 44000 records listing the number of merit points and demerit points awarded to 1449 students

I have replaced all zero values with nulls for the sake of this exercise

There are 4 versions of the same query. All have 3 calculated columns:
a) Expr1: Sum([MeritPts])+Sum([DemeritPts])
b) Expr2: Nz(Sum([MeritPts]+[DemeritPts]),0)
c) Expr3: Sum(Nz([DemeritPts],0))+Sum(Nz([MeritPts],0))

The first query lists all 1449 records - one per student
The 2nd only lists those where Expr1<>Expr2 - 801 records
The 3rd only lists those where Expr1<>Expr3 - NO RECORDS
The 4th only lists those where Expr2<>Expr3 - 1445 records

Now obviously all 3 expressions can't be correct so which should you use?
Well I could be awkward by saying it depends what result you want .... but that's being disingenuous.

In practical terms. one of them is DEFINITELY correct, another DEFINITELY wrong
The third is ... well I'll leave you to decide
And the order has (possibly) been changed to protect the innocent.

In line with Pat's original post, I know the answer(s) but rather than tell you, its more instructive to work it out for yourself!

Its important to note that in the original data I had zeroes rather than nulls in MeritPts & DemeritPts fields.
In that situation, all 3 expressions are IDENTICAL for all students and therefore all are correct

BUT if there is any chance of a null, you've got to know which one(s) to use!

Hope that helps rather than confuses anyone even more ...

Of course, all of this is totally irrelevant to the actual example in the original post as it doesn't have a sum of 2 fields !!!

Over and out!