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!
Bookmarks