Results 1 to 12 of 12
  1. #1
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    69

    Crosstab Query Report by Quarter problem?

    I have a report based on a Crosstab query that works fine as long as their is data in each of the quarters BUT if any of the quarters have no data because they haven't happened yet the report errors and says that MSAccess doesn't recognize Q? as a report field - this is because there is no data for Q2 or 3 or 4 as they haven't happened yet (it's still Q1)



    The crosstab query that the report is based on expands or shrinks depending on whether or not there is data in other quarters and does not error regardless of data or not.

    So I guess my question is how can I design the report so that it doesn't error should there be no data in a quarter
    Click image for larger version. 

Name:	Screen Shot 2020-01-19 at 9.34.27 AM.jpg 
Views:	12 
Size:	158.5 KB 
ID:	40717
    Above is a screenshot of the design view of the report - it is very simple - yet if there is no data in any of the detail fields (quarters) the report errors

    Any ideas as to how to stop this from happening - currently I am using 0 value place holder records for the quarters that haven't happened yet

    Thanks for any assistance!
    If it's not your job to make things better than whose job is it? - rcrobman

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    7,898
    I have a 'report' table that I dump the data into for the report to show the crosstab.
    1. I dump the regular data in the range
    2. I dump dummy data that has EVERY quarter, but with zeros. Thus ensuring all fields get filled.

    then when the crosstab runs, all catagories are represented.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,754
    This is a very common topic. Suggest you Google it because there is more than one approach and it's a bit too much in depth if you need explicit instructions. One page you might find is here https://access-programmers.co.uk/for...d.php?t=242545.
    with a working db somewhere around post 7.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    69
    That is essentially what I have done to get it to work but since this is a quarterly report that will be run year after year I don't want to have to put dummy records that serve as placeholders for real data each year.
    Thanks for the help
    If it's not your job to make things better than whose job is it? - rcrobman

  5. #5
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    69
    Thanks - I read the postings - quite complex as you said. I really find it hard to believe that you can't somehow put the place holders in the report in design mode so that if there is no data for the quarter it just displays the place holder - sort of like N/A or something. Unfortunately my coding knowledge is lacking at best so writing a bunch of in-depth code to make it work is a bit beyond me - my coding knowledge is at best rudimentary.
    Thanks for the help - appreciate it!
    If it's not your job to make things better than whose job is it? - rcrobman

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,754
    What you're complaining about is unfortunately, due to the nature of cross tab queries. Since unique row values become field (column) headers, there is no way to know how many fields you'll end up with as that is the dynamic part of the query. If you build a report from a query that has 6 field values today and run it tomorrow and there's 7, you have a problem.

    IF your problem is that there is NO data for a quarter (I just noticed you said that earlier) then that is a different problem. A report has an OnNoData event that is meant for this situation. In it, you just cancel the opening of the report. If the calling code balks and reports that the Open event was canceled (2501 maybe) you just trap that error in the calling sub. Maybe that's what you meant from the beginning and we didn't catch on.

    If you remain stuck, maybe post a copy of your db for us to fiddle with. Unfortunately I have nothing to work with for this. But I think that this
    how can I design the report so that it doesn't error should there be no data in a quarter
    may be your real problem, solvable via OnNoData.

    If this really is about the usual crosstab query type of report and missing fields, then there are ways around it. Trick is to find one that makes sense to you or at least fits your situation. If for example, this were about months, you ensure that the sql includes them as fields. Sorry, I can't recall if that's just by using the IN clause (one value for every time period) or if the query needs defined parameters.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  7. #7
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    69
    Thanks again for you help.

    Just to clarify the problem is that there won't be any data for the upcoming quarters as they haven't happened yet and hence the report fails to open.

    The solution you suggest would work great if I could apply it to each field rather than the whole report which I don't think is possible but will check!
    I came up with a potential workaround - sort of a sledgehammer effect. What I was thinking is to run the "create" the report 4 different times - once for each possibility - then use a If script on the triggering button that says
    if this is Qtr 1 then run this version else if this is Qtr2 run this etc.
    Kind of rudimentary but would take care of the problem!
    Will check out the OnNoData for a field and get back to everyone.
    Really appreciate the help!
    If it's not your job to make things better than whose job is it? - rcrobman

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,754
    I guess I'm not understanding what you have. If you're opening a report based on one quarter and there is no data, then the event (which only applies to the report itself) should provide a solution. If you're not opening one report based on one quarter, then I don't understand what's going on and it would appear that you need something else.

    EDIT - OK if I go by the picture and not what you're writing, then I get it and you need to make the quarters 'fixed'. I think I mentioned that the solutions at the links I've reviewed in the past involve using IN clause. You could also effect a solution by modifying the query definition of a query that the report is based on. I suppose this query mod would also need to set the non-existent quarters to null.

    In essence then, your cross tab query sql is formed on the fly so to speak.

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,754
    I had been following this thread and a code solution was just posted. You might want to review it from the standpoint of quarters rather than grades, but the principle is what I was referring to about modifying the source on the fly.

  10. #10
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    69
    Thank you all very much for your suggestions!
    The good news is that the report starts a year ago so there is always data (didn't know that when I was developing it)
    Didn't have to sledgehammer it just had to come up with the query parameters that would always go back 1 year from the date of being run - even I could figure that out!
    Always appreciate the helpful people on this forum - kudos to all!
    If it's not your job to make things better than whose job is it? - rcrobman

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,754
    Glad you got it solved!
    Thanks for the rep pts.

  12. #12
    rcrobman is offline Not Expert Yet!
    Windows 10 Access 2016
    Join Date
    Apr 2011
    Location
    Toronto
    Posts
    69
    You are welcome - though truth be told it sort of solved itself by having the report go back a year - good thing cause I was getting ready to hit it with the sledgehammer!
    Thanks for your help
    If it's not your job to make things better than whose job is it? - rcrobman

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

Similar Threads

  1. Unique counts problem in a Crosstab query
    By dgmdvm in forum Queries
    Replies: 5
    Last Post: 06-24-2018, 07:20 PM
  2. Problem with a Crosstab query
    By jose_armando in forum Queries
    Replies: 5
    Last Post: 10-06-2016, 03:29 AM
  3. Particular Crosstab Query Problem (w/ parameters)
    By McArthurGDM in forum Access
    Replies: 14
    Last Post: 12-29-2014, 02:46 PM
  4. Replies: 4
    Last Post: 07-27-2012, 07:04 AM
  5. Display previous quarter data when quarter is selected
    By rlsublime in forum Programming
    Replies: 1
    Last Post: 07-03-2012, 03:12 PM

Posting Permissions

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