+ Reply to Thread
Results 1 to 13 of 13

Access Query with Calculated Fields Not seen by Excel

  1. #1
    Registered User
    Join Date
    05-23-2013
    Location
    atlanta, georgia
    MS-Off Ver
    Excel 2003
    Posts
    26

    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!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Access Query with Calculated Fields Not seen by Excel

    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
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-23-2013
    Location
    atlanta, georgia
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Access Query with Calculated Fields Not seen by Excel

    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?

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Access Query with Calculated Fields Not seen by Excel

    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.

  5. #5
    Registered User
    Join Date
    05-23-2013
    Location
    atlanta, georgia
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Access Query with Calculated Fields Not seen by Excel

    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.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Access Query with Calculated Fields Not seen by Excel

    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

  7. #7
    Registered User
    Join Date
    05-23-2013
    Location
    atlanta, georgia
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Access Query with Calculated Fields Not seen by Excel

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

  8. #8
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Access Query with Calculated Fields Not seen by Excel

    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.

  9. #9
    Registered User
    Join Date
    05-23-2013
    Location
    atlanta, georgia
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Access Query with Calculated Fields Not seen by Excel

    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.

  10. #10
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Access Query with Calculated Fields Not seen by Excel

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

    if at first you don't succeed try doing it the way your wife told you to

  11. #11
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,744

    Re: Access Query with Calculated Fields Not seen by Excel

    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

  12. #12
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Access Query with Calculated Fields Not seen by Excel

    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
    Last edited by JosephP; 11-06-2013 at 10:56 AM.

  13. #13
    Registered User
    Join Date
    05-23-2013
    Location
    atlanta, georgia
    MS-Off Ver
    Excel 2003
    Posts
    26

    Re: Access Query with Calculated Fields Not seen by Excel

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Export Access Query Record into decicated fields in a Excel Template File
    By JRayhan in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-16-2013, 04:38 PM
  2. Replies: 1
    Last Post: 10-19-2012, 02:08 PM
  3. MS Query calculated fields
    By mkvassh in forum Excel General
    Replies: 1
    Last Post: 01-26-2009, 06:50 AM
  4. [SOLVED] Calculated Fields Based on Running Total Fields?
    By Kruncher in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-12-2006, 01:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1