Results 1 to 6 of 6
  1. #1
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19

    Reports not matching query


    Hi,

    I am brand new to Microsoft Access having only a week's worth of experience and I'm finding an issue where my query is producing the desired results but the report is not. More specifically, I'm trying to create a report that allows a user to choose a period in order to retrieve data for that period. The data being retrieved are percentages that are calculated as a line item divided by the total of all line items. This is working when the user, for example, chooses January, but if the user hits refresh and then chooses February, the numerator in the calculations are appropriately from February but the denominator stays as January's and produces the wrong result. The frustrating part is that this works in the queries when refresh is used. Furthermore, if I run January and close the report and then reopen and run February, I get the proper results (i.e. February numerator and denominator). Doesn't a report simply pull data from the query or does it actually perform calculations itself? For some reason it seems to be remembering the January denominator when displaying February results.

    I hope this question makes sense. If not, please let me know on what I should expound. Any help would be appreciated. Thank you.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    At the very least, the process should be open the form, input the parameters, test if the report is open. If not, open the report from that form. If yes, requery the report. The other option would be to close the report then reopen it. Forms and reports will maintain old data until you either requery or refresh them. Based on what you are doing, I would not opt for refresh.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    The data entry is being done through an Excel upload and not through a form. I have the query set up to request a period in the "YYYYMM" format. So, for example, when I run the report for "201601", all is fine. When I refresh and the prompt returns, I enter "201602", I get the proper dollar values (for February or "201602") but the percentages (calculated as dollar value divided by total of all dollar values) do not work. The percentages are the February dollar values divided by the total of the January dollar values, not the total of the February dollar values. The confusion I have is that this works fine for the query but not the report. Again, I apologize if my inexperience is causing any confusion. Thanks.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're being too vague for me. You have to realize that we know nothing of what you are doing or where you are doing it, or where you're experiencing any sort of result.
    When I refresh and the prompt returns I - refresh what?
    the percentages (calculated as dollar value divided by total of all dollar values) do not work. - calculated where? Query or report?
    The confusion I have is that this works fine for the query but not the report. What 'works fine'? The query?

    Where are the calculations being done? They should be done in the report.
    Is the report being requeried (or closed then re-opened) when the date parameters change? If not, this is likely the solution and was already noted. I suspect that the major reason for the difficulty is that you are not managing the process from a form, even if it is only a button click that runs code or a macro. You haven't said how the process was being controlled either.

  5. #5
    AccessNovice16 is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    19
    Okay, sorry, I will try to be more clear.

    The calculation to take each dollar value in a list of dollar values and divide by the total of all dollar values is being done in a query. The query is set up to prompt for a date as these are calculations are done at each month-end and users will need to be able to access the historical records from inception (i.e. at any month end in the entity's history based on need).

    When I referred to "refreshing", I was referring to the use of the "refresh all" button in the "Home" tab in the "Records" menu. This causes the date prompt to return so that I can choose another date. When I use this button in the query, and then enter a new date, the results that are produced are correct. If I try to do the same in the report (i.e. use the "refresh all" to enter a new date), the results that are produced are incorrect. If I do not use the "refresh all" button when trying to run two separate months, but instead close the report, reopen and reenter a new date when prompted, the results are correct in the report. I am trying to avoid, if possible, a user having to open and close the report 12 times if someone wants to run a full year. I'm not sure if this is possible.

    You are correct, I do not have forms set up as there will be only one enterer of data and that data will be uploaded from a template with many lines and should never need to be changed by a user. So far, we have entering data through an Excel upload using the "External Data" tab instead of using a form.

    In general, it was my assumption (and likely an incorrect one since you mention that the calculations should be done in the report and not the query) that the query was responsible for manipulating data and the reports merely organized the data in a way that was useful for end users. I did not appreciate that calculations should be done in reports and not in queries.

    I hope that makes my question and situation more clear. If not, no worries, I will just open and close the report. Thanks.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Re: calculations - I expressed that in a way that may be misleading. I meant for what you are doing (i.e. not using a form). It's my belief that refreshing the report via the ribbon would work OK if the calculations were done in the report so I was addressing your situation on the assumption you were not using a form. It's fine to have calculations in a query, though it would be my preference to use a report whenever possible, especially if the calculations are that simple. The reason I can is that in all cases I have used forms to drive the process, knowing that a) the typical user knows nothing of how to control Access and b)I can control the process better while not relying on their Access level of knowledge.

    A form is not only for data input, but is also handy for controlling the process. If you were me then, the form button click to open the report would first check to see if the report was open (as previously mentioned) and the report would run the query (I would not run the query then open the report). Doing it in this order and fashion, you would not have this problem. If you want users to have multiple month results in your report, it sounds to me like the report design could provide that. Consider having the report group by month so that if you wanted to see results for Jan - Feb - Mar, you'd get a section for each month in one go as long as user can input a date range when prompted. A form is also a good way to provide options, such as date ranges, departments, sales reps etc. to pass to the query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. point matching query
    By Shales in forum Access
    Replies: 5
    Last Post: 04-20-2016, 10:25 AM
  2. Help with Pattern matching in access query
    By javajockey in forum Queries
    Replies: 3
    Last Post: 10-01-2014, 08:38 PM
  3. Matching Update Query
    By james28 in forum Queries
    Replies: 8
    Last Post: 08-03-2014, 10:10 AM
  4. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  5. Matching Query Dates
    By rgwynne in forum Queries
    Replies: 1
    Last Post: 08-13-2009, 05:23 AM

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