#  Other Applications & Softwares  > Access Tables & Databases >  >  Help with MS Access 2007 Overflow error

## Pi*

I am running  a query below:
ELECT [Overall Performance 1].[Account Name], [Overall Performance 1].[CountOfVendor Name], [Overall Performance 1].[Total Sales], [Overall Performance 1].PO, [Overall Performance 1].[SumOfUnits - Total Count] AS [Total Count], [Overall Performance 1].[On Time Fill Rate %], [Overall Performance 1].[SumOfOn Time Fill Rate % - Chargeback Amount ($)] AS [Fill Rate], ([Fill Rate]/[Total Count]) AS [Fill Rate Charges per Unit Sold], [Overall Performance 1].[Cancellations %], [Overall Performance 1].[SumOfCancellations - Chargeback Amount ($)] AS Cancellation, ([Cancellation]/[Total Count]) AS [Cancellation charge per unit sold], [Overall Performance 1].[On time %], [Overall Performance 1].[SumOfLate Shipments - Chargeback Amount ($)] AS Lateship, ([Lateship]/[Total Count]) AS [Late Shipment per unit sold], [Overall Performance 1].[LateASN %] AS [Ontime ASN %], [Overall Performance 1].[SumOfLate Notification of Shipment - Chargeback Amount ($)] AS [LateASN Charges], ([LateASN Charges]/[Total Count]) AS [Late Notification charges per unit sold], [Overall Performance 1].[Up To Date Inventory %], [Overall Performance 1].[SumOfInventory Compliance - Chargeback Amount ($)] AS Inventory, ([Inventory]/[Total Count]) AS [Inventory Charges per unit sold], [Overall Performance 1].[Average Order Value ($)], ([SumOfOn Time Fill Rate % - Chargeback Amount ($)]+[SumOfCancellations - Chargeback Amount ($)]+[SumOfLate Shipments - Chargeback Amount ($)]+[SumOfLate Notification of Shipment - Chargeback Amount ($)]+[SumOfInventory Compliance - Chargeback Amount ($)]) AS [TOTAL CHARGEBACK], ([Total Chargeback]/[Total Count]) AS [Total charge per unit sold], ([TOTAL CHARGEBACK]/[Total Sales]) AS [% of Sales]
FROM [Overall Performance 1]
GROUP BY [Overall Performance 1].[Account Name], [Overall Performance 1].[CountOfVendor Name], [Overall Performance 1].[Total Sales], [Overall Performance 1].PO, [Overall Performance 1].[SumOfUnits - Total Count], [Overall Performance 1].[On Time Fill Rate %], [Overall Performance 1].[SumOfOn Time Fill Rate % - Chargeback Amount ($)], [Overall Performance 1].[Cancellations %], [Overall Performance 1].[SumOfCancellations - Chargeback Amount ($)], [Overall Performance 1].[On time %], [Overall Performance 1].[SumOfLate Shipments - Chargeback Amount ($)], [Overall Performance 1].[LateASN %], [Overall Performance 1].[SumOfLate Notification of Shipment - Chargeback Amount ($)], [Overall Performance 1].[Up To Date Inventory %], [Overall Performance 1].[SumOfInventory Compliance - Chargeback Amount ($)], [Overall Performance 1].[Average Order Value ($)];

and I Keep getting an Overflow error.

Please help.

Thank you

----------


## Pi*

I think I figures out why I keep getting the Overflow error when I run the sql above..
The Up to date Inventory % is causing the error and it's being calculated as..
Business Days which has a value of 0 - Days withiout updates whichh is also 0 / Business days.

How do I fix this error.

Regards,

----------


## Norie

What do you want to happen when those fields have 0?

----------


## Pi*

To give me an Expression. More often than not one of the 2 has a value other than 0. But in this case since both are 0, how do I still get an expression of 0.

Pardon me I am still trying to learn Access.

----------


## Pi*

Sorry Norie..If those fields have 0 I would want it to "Group by" the result.

----------


## Pi*

I tried to write an IIF function : Up To Data Inventory %: Avg(IIF([Business Days]=0,0,IIF([Days w/o updates]=0,100,[Business Days]/[Days w/o updates])*100, “Fixed”)))

to give me 0 if the field is 0 and/or a value if this field has value but hit a snag that "Subqueries cannot e used in the expression.

Any ideas of what I am missing?

----------


## Norie

Sorry, I don't quite follow.

What do you mean you want an 'expression'?

Also, you can't conditionally group by also even if you group by the calculation is still done.

Do you actually want to include records with 0 in the Business Days field?

By the way, where is Up to date Inventory % calculated? I don't see it that in the query you posted.

----------


## Pi*

Please consider below sql instead:

SELECT [Overall Partner Compliance].[Account Name], Count([Overall Partner Compliance].[Vendor Name]) AS [CountOfVendor Name], Sum([Overall Partner Compliance].[Total Sales in Unit Cost ($) - Total ($)]) AS [Total Sales], Sum([Overall Partner Compliance].[Purchase Orders - Total Count]) AS PO, Sum([Overall Partner Compliance].[Units - Total Count]) AS [SumOfUnits - Total Count], Sum([Overall Partner Compliance].[Units - Total Count]) AS [Total Units], Sum([Overall Partner Compliance].[Cancellations - Total Units Cancelled]) AS [units cancelled], [units cancelled]/[total units] AS [Cancellations %], Sum([Overall Partner Compliance].[Late Shipments - Total Count]) AS [Late Ship], ([total units]-[late ship])/[total units] AS [On time %], Sum([Overall Partner Compliance].[Late Notification of Shipment - Total Notifications]) AS LateASN, Sum([Overall Partner Compliance].[Late Notification of Shipment - Total On-Time]) AS [LateASN Total Ontime], [LateASN Total Ontime]/[LateASN] AS [LateASN %], Sum([Overall Partner Compliance].[Inventory Compliance - Business Days in Period]) AS [Business Days], Sum([Overall Partner Compliance].[Inventory Compliance - Business Days Without Updates]) AS [Days w/o updates], (([Business Days]-[Days w/o updates])/[Business Days]) AS [Up To Date Inventory %], Sum([Overall Partner Compliance].[Purchase Orders - Total Count]) AS [PO total], Sum([Overall Partner Compliance].[Cancellations - Total Count]) AS [Cancellations total], Sum([Overall Partner Compliance].[Late Shipments - Total Orders with Late Shipments]) AS Lateship, Sum([Overall Partner Compliance].[Cancellations - Merchant Cancels (orders)]) AS Merchants, ([PO total]-[Cancellations total]-[Lateship])/([PO total]-[Merchants]) AS [On Time Fill Rate %], Sum([Overall Partner Compliance].[On Time Fill Rate % - Chargeback Amount ($)]) AS [SumOfOn Time Fill Rate % - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Cancellations - Chargeback Amount ($)]) AS [SumOfCancellations - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Late Shipments - Chargeback Amount ($)]) AS [SumOfLate Shipments - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Late Notification of Shipment - Chargeback Amount ($)]) AS [SumOfLate Notification of Shipment - Chargeback Amount ($)], Sum([Overall Partner Compliance].[Inventory Compliance - Chargeback Amount ($)]) AS [SumOfInventory Compliance - Chargeback Amount ($)], ([Total Sales]/[PO]) AS [Average Order Value ($)]
FROM [Overall Partner Compliance]
GROUP BY [Overall Partner Compliance].[Account Name], [Overall Partner Compliance].[Import Date]
HAVING ((([Overall Partner Compliance].[Import Date])=Date()));

By expression I mean the calculation Total in this field should be an "expression" instead of "Group by".

Basically if "Business Days"(numerator) in the Up To Date % calculation is 0 I want my calculation bt be 0.

If "Business Days" (numerator) in the Up To Date % calculation isn't zero then I want my calculation to equal an amount ie Business Days-Days w/o updates/ Business Days.

So in the sql I am trying to change the Up to Date % formular into an IIF formula like: Up To Data Inventory %: Avg(IIF([Business Days]=0,0,Format(CDbl([Days w/o updates])/CDbl([Business Days])*100, “Fixed”)))

It works (returns a blank field since both Business Days and Days w/o updates are 0). 

Question: Would it still work if there are values instead of 0?

I hope I haven't lost you again.

I truly apprecaite your time and assistance.

Regards,

----------


## Norie

What do you mean by 'calcuation Total'?

Also, the query is only grouped on [Account Name] and [Import Date].

----------


## Pi*

Thank you Norie for your response.

Could you please assist me in writing an IIF formula for the Up To Date % calcalation?

In the sql this is calculated as Business Days - Days w/o update / Business Days. Since Business Days (the numerator) is 0 and the Days w/o update is also 0 it result in an overflow error.

Perharps an IIF formula that will result in 0 if Business Days is 0.
And also the same formula resulting in a desired result if there are values in both the Business Days or Days w/o update.

Regards

----------


## Norie

I don't think an Iif will work.

The reason for that is both the true and false expressions are calculated regardless.

So if one of the expressions could result in a division by zero you would probably end up with an error.

----------


## Pi*

Please provide a workaround for this issue ( when the field is > 0 or 0 ) and please demonstrate with examples. If you have any other suggestions on this I will also appreciate them.

Regards.

----------

