Access Query with Calculated Fields Not seen by Excel

Access 2007
Excel 2010

So I have a query where I am combining product level data with group level data. My intent was to compare the products back to their subgroups in order to determine if they are better or worse than the products in their most similiar product group.

With just the query I can see the table in excel without issue and use it to run pivot tables.

However when I try to use a calculated field the ability for excel to see the query goes away. I will see all the tables and queries in access other than the one with the calculated field. I found by researching earlier that having nulls and using them in calculations can cause this issue. The data I am relying on has nulls instead of 0's so I used the Null Zero function to take care of these nulls to no avail. Below is the equation that I am trying to use to calculate the Index. Basically this just calculates rates for the sku and normalizes it to the subclass average.

RTV Index: (Nz([SKU RTV Units],0)/(Nz([SKU Sales Units],0)+0.000000001))/(Nz([Subclass RTV Units],0)/(Nz([Subclass Sales Units],0)+0.0000000001))

When I began using the NZ function it correctly calculated an index for each cell but I still cannot see the query from excel. Any ideas?

Please advise!

For your first solution nothing in that trail of responses worked. I in fact had the exact same problem as the 2nd to last guy who posted. I was able to backdoor the table with a work around but it wasn't helpful because excel threw out the data as soon was it was tapped. "[Microsoft][ODBC Microsoft Access Driver] Undefined function 'nz' in expression." The other suggested i just rebuild the table which i have done to no avail. I have also isolated the issue to the calculated fields as the moment I add in another calculation Excel stops seeing the query.

Any other ideas?

Did you try using the concept of taking a query that does work and making that a shell and then appending it as necessary.
The query works. It is the calculated fields that break it. I have remade the query from scratch and broken others by adding the queries.

Can you run the query without the calculation and then do the calculation in Excel? Just trying to think outside the box for a solution that will work for you.
This would A) kill excel and B) mandate a hugely more complex data structure as it would make a mess of the aggregation level and require multiple tables for the same dataset.

Alternatively, would it be feasible to export the query directly from Access to Excel. This could be automated through a VBA solution if necessary.
Not sure as i have never done this kind of work. I am a master at basic excel functionality but havent played with VBA too much.

I somewhat developed a work around however. By making my query create a table it changes the data from calculations to actual data. This table is able to be accessed by excel. It is just going to be inefficient memory usage.

I tried one way and got "This query cannot be used as a row source". I tried it another way and it told me it was row limited to 65k rows (about 200k in the dataset).

These are error messages that i received when i tried to expor the query to excel. I was able to eventually do this by playing with the settings however it does not really meet my needs either. Basically I want to keep all the raw data inside of access to limit the size of the excel file. I was able to accomplish this by passing the data from the query through to a table and I just refresh that data. Even with all the data residing inside of access my excel dashboard is sitting at around 250 mb.

The IIF(isnull(BLAH, Blah, Blah)) worked like a charm! Thanks so much this allows me to compact my data some and keep the structure cleaner.