Results 1 to 8 of 8
  1. #1
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18

    Adding Calculated Field to Query Stops Previously Working Excel Import

    I have a query that extracts actual and forecast income for stores from a table. I import the data from the query to my Excel dashboard and all works well.

    I wanted to add a performance factor into the Access Query so added the calculation Perfomance: [Income]/[Forecast]. This works in the query and gives the correct values, but now, when I try to refresh my Excel dashboard, it will no longer refresh.

    I checked it by trying to import the same query to a blank Excel sheet and got the same problem. Am I doing something wrong, or expecting something to work that simply won't? Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    nothing to do, if you have a live connection to the access query, then importing the data should update the results.
    Now, the access query doesnt have any parameters right?

  3. #3
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Quote Originally Posted by ranman256 View Post
    nothing to do, if you have a live connection to the access query, then importing the data should update the results.
    Now, the access query doesnt have any parameters right?
    It is definitely something to do with the query - I have another spreadsheet that imports from the same database but does not look at that particular query and I can update it as normal.

    But when I try to update the spreadsheet which reads the query I amended, I get the response 'Data could not be retrieved from the Database. Check the database server or contact your database administrator. Make sure the external database is available and then try the operation again.'

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Maybe delete and recreate the connection in Excel.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Thank you both for your support - I have managed a work-around, although I haven't the faintest idea why the workaround should work.

    Basically, I kept the query with the calculation but I then converted it back to a table using Make Table. For some reason I can import the table to my dashboard, just not the query that it comes from.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Interesting, I could not replicate the issue.

    Instead of MAKE TABLE which modifies the db structure, recommend the table be permanent and then DELETE and INSERT records.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Quote Originally Posted by June7 View Post
    Interesting, I could not replicate the issue.

    Instead of MAKE TABLE which modifies the db structure, recommend the table be permanent and then DELETE and INSERT records.
    Can you explain a little more about your suggestion please? I am not sure what you mean by 'DELETE and INSERT records'.
    Thanks

    I should add that this database is the most complicated one I have made, but has grown organically over time to fulfil a number of functions that I had not anticipated when I first created it. I am sure, therefore, that the problem is related to how I built it. I am tempted to start afresh using the little knowledge of database structure and design that I have subsequently gained, but I use it for so many different purposes that it scares me to think I need to start again.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Run SQL DELETE action to clear table then INSERT action to populate table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Working query stops working after importing
    By Abacus1234 in forum Import/Export Data
    Replies: 3
    Last Post: 10-25-2015, 09:12 PM
  2. Calculated field in Query not working!!! :( I made a video
    By SebastianColombia in forum Queries
    Replies: 3
    Last Post: 07-28-2015, 07:02 PM
  3. Adding a Calculated Date Field to a Query
    By MFriend in forum Access
    Replies: 8
    Last Post: 07-24-2015, 04:00 PM
  4. Replies: 6
    Last Post: 02-12-2014, 05:16 PM
  5. Replies: 2
    Last Post: 12-03-2010, 09:33 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums