Results 1 to 4 of 4
  1. #1
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24

    Report to summarise year readings by quarter.

    I would like to have a report printed like this:



    Quarter1 Quarter1 Quarter2 Quarter2 Quarter3 Quarter3 Quarter4 Quarter4 Running total Running total
    FamilyName FirstName StaffID Reading1 Reading2 Reading1 Reading2 Reading1 Reading2 Reading1 Reading2 Reading1 Reading2
    Smith Fred 123456 0.2 0.3 0.2 0.3 0.2 0.3 0.6 0.9
    Black Dave 234567 0.1 0.1 0.3 0.2 0.1 0.1 0.1 0.1 0.6 0.5
    Porter Steve 345678 0.2 0.2 0.1 0.1 0.2 0.2 0.2 0.2 0.7 0.7

    I have a table that has the data like this:

    FamilyName FirstName StaffID Reading1 Reading2 YEAR QUARTER
    Smith Fred 123456 0.2 0.3 2015 1
    Black Dave 234567 0.1 0.1 2015 1
    Porter Steve 345678 0.2 0.2 2015 1
    Smith Fred 123456 2015 2
    Black Dave 234567 0.3 0.2 2015 2
    Porter Steve 345678 0.1 0.1 2015 2
    Smith Fred 123456 0.2 0.3 2015 3
    Black Dave 234567 0.1 0.1 2015 3
    Porter Steve 345678 0.2 0.2 2015 3
    Smith Fred 123456 0.2 0.3 2015 4
    Black Dave 234567 0.1 0.1 2015 4
    Porter Steve 345678 0.2 0.2 2015 4

    There is sometimes data missing from some quarters. I want the persons readings to show up on the report even if readings are missing. Is it possible to do this with the table I have? By the way the persons names are stored in a separate table linked by the StaffID.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need two crosstab queries, one for reading 1 and one for reading 2 - ensure you complete the column headings property for the query

    then you need a third query to join both crosstabs together on staffid

  3. #3
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24
    Thank you very much for your usual fast reply. I'm not familiar with crosstab queries. What should I use as row and column headings in the query wizard?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    column heading would be quarter
    value (could be sum, max, first) would be reading1 (or 2 for second crosstab)

    Other columns to appear would be row headings

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

Similar Threads

  1. Input Quarter-of-year on a Form
    By J4X in forum Forms
    Replies: 3
    Last Post: 12-09-2015, 11:41 AM
  2. Replies: 2
    Last Post: 06-21-2013, 12:28 PM
  3. Replies: 4
    Last Post: 01-09-2013, 11:16 AM
  4. Show Year with Quarter
    By Mordred in forum Access
    Replies: 9
    Last Post: 06-30-2011, 07:50 AM
  5. Replies: 23
    Last Post: 06-30-2010, 02:05 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
  •  
Other Forums: Microsoft Office Forums