Calcuated field in Access quey contributes to crashing Excel table upon import !

[countingguru]

I am going nuts on this issue!



I have a simple calculated field (As "Ratio") that is readable in Access 2010 query, but crashes in Excel upon importing/running the query in an .xlsx table.
ERROR MESSAGE in EXCEL: "The query did not run, or the database table could not be opened...check database server..contact your database administrator.."

I am simply taking the cost amounts of each project record (shown as "Record Cost") and dividing it by the "Total Cost" of that project to render "Ratio".
The Total Cost is retrieved from another table.

Testing scenarios:
If we exclude Ratio, the whole query import just fine.
If we filter the query to one project (i.e. "Project A") the ratio field imports fine.
However, the whole unfiltered query does not with the ratio!

Other testing scenarios:
If we use a constant for the denominator AS "1" in Ratio (i.e. recordcost/1) the whole unfiltered query and ratio field imports fine.
If we use a constant for the numerator AS "1" in Ratio (i.e. 1/totalcost) the whole unfiltered query upon import crashes in Excel.

This is a mock example of the dataset, the actual query has 50K+ records:

Project Service Date Record Cost Total Cost Ratio
A Welding 1/1/2012 $100 $120 83%
A Plumbing 2/1/2012 $20 $120 17%
B Welding 1/1/2012 $50 $75 67%
B Plumbing 2/1/2012 $25 $75 33%
C Welding 1/1/2012 $40 $61 66%
C Plumbing 2/1/2012 $21 $61 34%


Thanks in advance. Al

[pbaldy]

Is it possible there are records with either Null or 0 in the "Total Cost" field? That would cause a division by zero error that you might not see in Access until you navigated to/beyond that record in the query.

[June7]

How are you retrieving TotalCost from the other table - DLookup or subquery?

Post query statement and sample of source data, not just the desired output.

If you want to provide db for analysis, follow instructions at bottom of my post.

[rpeare]

Are any of your denominators (total cost) 0? (I've never tried to link a query to an excel file as the data source but this is my guess for what's going on, some problem with the numbers involved that you can see in the access query but excel can't interpret)

Have you tried exporting the query to excel and seeing if you get the correct value as opposed to directly linking to an access query?

I'd also check all your numerators to see if they're 0 either or in your ratio column instead of having a simple division have something like

iif(isnull([record cost]) or [record cost] = 0, 0, iif(isnull([total cost]) or [total cost] = 0, 0, [record cost]/[total cost]))

[countingguru]

Yes some projects have total cost "0" values

[countingguru]

rpeare <<<<<< is a genius !!!! THANK YOU IT WORK !!!