Solved Overflow error in a Max Query (1 Viewer)

[raziel3]

Hi guys,
Can I get a second pair of eyes on the query AZMAXCOST.
I'm trying to get the max cost by month for each variation of FGMID and RMSUBID. I'm using the same method in the query BRCMAXCOST and it works but AZMAXCOST is giving me an overflow error.

 

[arnelgp]

i don't have problem running AZMAXCOST.

 

[raziel3]

i don't have problem running AZMAXCOST.
View attachment 105256

It's ungrouped.

When you total it, it throws the error. Also the base query has only 5068 records but AZPROMAXCOSTLV2 has 66045 records. Are the joins wrong?

 

[raziel3]

Ok, I checked back the joins in AZPROMAXCOSTLV2 and I forgot to join AZPROMAXCOSTLV1 to CCQTYByMTH on CCID and MTHEND.

That took care of the duplications but still unable to Aggregate the final query AZMAXCOST.

Edit: Also removed SALESUOM from CCQTYByMTH

 

[raziel3]

Got it to work.
I put a primary key in the WEIGHTS Table, removed SALESUOM and fixed the joins in the CCQTYByMTH query.

 

[The_Doc_Man]

Good work, raziel3, and thanks for explaining how you fixed it.

 

[raziel3]

Just wanted to provide additional information on the Overflow Error.

In addition to making sure your joins are properly done, whenever Aggregating Queries ALWAYS remember to filter out Nulls and Errors from the field being Aggregated.

In your table design, if you are certain that a field will be used for aggregation you can set up the default values for that field. For example Numeric field can be defaulted to 0 and I usually default date fields to Date() so during testing it won't be such a pain to debug.