#  Other Applications & Softwares  > Access Tables & Databases >  >  Access Query with Calculated Fields Not seen by Excel

## Xcelguy

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!

----------


## alansidman

Look at this thread where there is a discussion and workaround solution.

http://social.msdn.microsoft.com/For...orum=accessdev

and this is another scenario

http://www.mrexcel.com/forum/microso...ess-query.html

----------


## Xcelguy

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?

----------


## alansidman

Did you try using the concept of taking a query that does work and making that a shell and then appending it as necessary.

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.

Alternatively, would it be feasible to export the query directly from Access to Excel.  This could be automated through a VBA solution if necessary.

----------


## Xcelguy

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.

----------


## alansidman

> 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 concur that it is inefficient.

Setting up an export from Access to Excel is fairly routine and simple.  To get an idea of how to do this, I suggest that you do it manually first.
1.  Select your query and highlight it in the Navigation Pane.  Click on External Data on the ribbon.  Export Tab. select Excel.  Follow the wizard.  
2.  If this works for you, then we can help you to write some simple VBA and execute it as necessary to accomplish the same.

HTH--Alan

----------


## Xcelguy

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).

----------


## alansidman

> 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).



I don't understand what you are referring to here or what you were attempting to do.

----------


## Xcelguy

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.

----------


## JosephP

msquery doesn't understand nz so you have to use iif(isnull([field]),0,[field]) instead

----------


## alansidman

Josie;
A technical question.  When you use Excel to look directly into an Access table or Query, the application uses MS Query as the conduit? And naturally, if the Access functions are not part of MS Query (ie. NZ), then you have to use a workaround.  Thanks much for this lesson.  Is my understanding of this correct?

Alan

----------


## JosephP

no-in fact I misspoke (at least it does not use the .exe). it is the odbc driver (which msquery also uses) that doesn't understand what Nz is

----------


## Xcelguy

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.

----------

