Total group / sum bug? (1 Viewer)

[Tskutnik]

All,
Anyone ever see a query Sum not work? Seems when there is only one record to Group by the related field being Sum(ed) is coming up 0 instead of the value in that one record. Same if Max or Min is selected. When there is more than one record bring grouped the related Sum is correct.
I've been on access for years and know the Total group/sum/max/count function well so it's not something totally obvious.

 

[plog]

Can you post a database that demonstrate s this?

 

[theDBguy]

Hi. I second the request for a demo file. It would be nice to see what you're saying in action. Thanks.

 

[andyhoneybadger]

Hello. Demo file would help understand the issue, thanks

 

[isladogs]

Its always worked for me as in the simple example below

Or did you mean something else?

 

[Tskutnik]

All - thanks for your notes back. I'll post the DNA Friday night. Isla - always worked for me too. Add a new record with field = b and n = 4. Query that as it is with no total and exclude ID, then query the query and add the totals as you have it. In your example my issue produces a= 10 and b =0. It has worked in other cases and I'm still testing, but I'll post something so you can see.

 

[isladogs]

I followed your instructions but got what I'd expect
Query1 is the original aggregate query based on the table
Query2 is the table minus the ID field
Query3 is the aggregate query based on Query2

Unable to replicate your issue

 

[Tskutnik]

I figured. Same with my tests. Sometimes it works. I'll post the db.

 

[theDBguy]

I figured. Same with my tests. Sometimes it works. I'll post the db.

Just FYI, I get the same result as Colin in my test.

 

[Tskutnik]

Yes I understand it's strange. I'll post the example.

 

[isladogs]

You might want to try my test dB and see what you get with it.

 

[Tskutnik]

All, Attached is the DB. To find the issue with Totals....

Run the CommissionOwed_1 Query (Totals are turned off).
Filter EarnedBy = 287 and you will see 3 values, 1 for each month

EarnedBy YYYYMM CommissionEarnedAfterTrigger
287 2019.08 8880
287 2019.09 6450
287 2019.07 3570

Now in Design View, Click the Totals option and you will see the 2 Groups and one Sum added. Run the query with those Totals and you get.

EarnedBy YYYYMM SumOfCommissionEarnedAfterTrigger
287 2019.07 0.00
287 2019.08 0.00
287 2019.09 0.00

Since there is only one value in each EarnedBy / YYYYMM there should be the same 3 values as before they were totaled, but instead all are 0.
Earned by #227 has the same problem.

Other ones work fine.
Example before the Totals (#25 with 2 records in March 2019)

EarnedBy YYYYMM CommissionEarnedAfterTrigger
25 2019.03 535.5
25 2019.03 374.85

After the Total is added (and it is correct)
EarnedBy YYYYMM SumOfCommissionEarnedAfterTrigger
25 2019.03 910.35

Changing the format on the number field does not work.
I'm totally lost.

Thanks for any help

 

[isladogs]

You have a whole series of Commission queries each with calculations built on calculations in earlier queries.
Many contain Nz functions with no default value which isn't a good idea.

To understand why this issue is occurring you need to trace the calculations back to the original source. However the following queries will I think help you track it down

First look at commission values for 227 & 287

SELECT Commission_6.EarnedBy, Commission_6.YYYYMM, Commission_6.CommissionEarnedAfterTrigger, Commission_6.CommissionEarnedPerJob, Commission_6.TriggerFlags
FROM Commission_6
WHERE (((Commission_6.EarnedBy)=227 Or (Commission_6.EarnedBy)=287))
ORDER BY Commission_6.EarnedBy;

There are 12 records. Notice the TriggerFlags are all 1
Now group all fields in that query

SELECT Commission_6.EarnedBy, Commission_6.YYYYMM, Commission_6.CommissionEarnedAfterTrigger, Commission_6.CommissionEarnedPerJob, Commission_6.TriggerFlags
FROM Commission_6
GROUP BY Commission_6.EarnedBy, Commission_6.YYYYMM, Commission_6.CommissionEarnedAfterTrigger, Commission_6.CommissionEarnedPerJob, Commission_6.TriggerFlags
HAVING (((Commission_6.EarnedBy)=227 Or (Commission_6.EarnedBy)=287))
ORDER BY Commission_6.EarnedBy;

There are still 12 records but the TriggerFlags are now all 0.
Identify why there is a discrepancy by tracing back to source and this should then work properly

 

[Tskutnik]

Appreciate that and I'll continue diagnosing today. But no matter any issues I can find upstream from the query, how is it possible that the values are there before the Total and 0 after? On the face of it that makes no sense to me.

 

[isladogs]

Somewhere you have managed to confuse the logic in one or more of your expressions as the discrepancy in the two queries I supplied proved clearly.

I would start by looking through each query. Check each expression field used carefully including adding a false part to each Nz function. Whilst it probably defaults at zero, its better to specify Nz(fieldname,0) than just Nz(fieldname).

Rather than build up Commission_1 to Commission_6 (etc) then running a totals query on the last query, I would recommend using the original queries or tables as a basis for the aggregate query. The fewer steps involved the less risk of error and the faster it should run.

Good luck

 

[isladogs]

Just returned to this and solved it by doing what I indicated.
Adding Nz false values and going back to earlier queries. In this case to Commission_5.
This seems to work correctly

SELECT Commission_5.EarnedBy, Commission_5.YYYYMM, Sum([CommissionEarnedPerJob]*[TriggerTemp]) AS CommissionEarnedAfterTrigger
FROM Commission_5
GROUP BY Commission_5.EarnedBy, Commission_5.YYYYMM
ORDER BY Commission_5.EarnedBy;

I've saved it as qryConmissionOwed_CR in the attached version

 

[Tskutnik]

I'm getting closer to the problem but still cannot figure out why it is happening. Any help is welcome.
In the attached database - still focusing on the [EarnedBy] = 287 problem:

First See [Commission_6]. The underlying source are 2 Queries:
1) [Commission_5] with LEFT JOINS to [Trigger_2] so all 228 records in [Commisson_5] are captured.
2) [Trigger_2], which (correctly) does not have any records for [EarnedBy] = 287

So - the [Commission_6] query should have a NULL value for the 287's for fields only pulled from [Trigger_2], which is only [TriggerFlag]. Other calculated fields referencing [TriggerFlag] should also be null.
Problem: In Commission_6 the [TriggerFlag] = 1 instead of NULL.

To Diagnose - I copied [Commission_6] to a new [Commission_6B] with:
1) The same [Commission_5] Query, with the LEFT JOINS as above
2) A new Table called [Trigger_2Table], created with a MAKE TABLE version of the original [Trigger_2]. The original [Trigger_2] and new [Trigger_2Table] have the same (correct) values, neither with [EarnedBy] = 287
Result: In [Commission_6B] the [TriggerFlag] is now (correctly) Null.

FYI - I did the same MAKE TABLE test with [Commission_5] and nothing changed, so that side is not the problem.

So here is the question - how an the same [Commission_6] query be different with the [Trigger_2] source from a Query vs. a Table when both the query and table give the same results? Even stranger, neither [Table_2] version has a 287 record, so it REALLY should not matter.

 

[Tskutnik]

Isla - just saw your note. let me check. I also did the NZ removal but has the issue just posted.

 

[Tskutnik]

Isla - thanks for your work on this. I see [qryCommissionOwed_CR] points to Commission_5 and that it works. Only issue is [qryCommissionOwed_CR] needs to point to Comission6 because that one includes the Trigger_2 query that is needed. Trigger_2 is the one giving me a problem.
Hopefully my last DB post illustrates it easily.
I really appreciate the help

 

[isladogs]

How about this instead?

SELECT qryCommissionOwed_CR.EarnedBy, qryCommissionOwed_CR.YYYYMM, qryCommissionOwed_CR.CommissionEarnedAfterTrigger, Trigger_2.TriggerFlag, Trigger_2.CommPlanID, Trigger_2.TriggerBreakPointValue
FROM qryCommissionOwed_CR LEFT JOIN Trigger_2 ON (qryCommissionOwed_CR.YYYYMM = Trigger_2.YYYYMM) AND (qryCommissionOwed_CR.EarnedBy = Trigger_2.EarnedBy);