Results 1 to 9 of 9
  1. #1
    dmckphx is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    2

    Category Results as columns not rows

    Still using Access 2010 Pro, I know ancient but it's what my company has.



    We are trying to format a tabular report with categories so the category results happen as columns and not as rows.

    It is a job costing report that pull hours for projects from another program. The category function is used to tabulate the total hours for department in our company on the project. The automated formatting in Access resultsClick image for larger version. 

Name:	Design View.PNG 
Views:	22 
Size:	33.8 KB 
ID:	35624Click image for larger version. 

Name:	Report View.PNG 
Views:	21 
Size:	23.8 KB 
ID:	35625 in them printing as rows which chews up a lot of space as this is our summary report for multiple projects. We would rather each department result prints on one row from left to right as columns.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How many possible category records could each project have?

    A CROSSTAB query might accomplish pivoting data. However, number of columns could be too many for page width, also, number of columns would be variable and building a stable report based on dynamic CROSSTAB is not easy.

    Post example data. If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  3. #3
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    67
    It looks like you need a subform/report with a cross-tab query.

    In the main form/report you have your header data:
    Company / Code / Area / Billed / etc. for each Project

    Then for each Project you have a subform that shows

    CategoryA / CategoryB / CategoryC
    Hours / Hours / Hours

    There are tricks to have your cross tab show two different values (Hours and Percent) but I cannot remember them off the top of my head.
    You could build the percent calculation into the report [CategoryAPerc]=[CatAHours]/[HoursTotal]

    What information is in the Detail span? It shows as blanks in the report screenshot.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sorry, duplicate.
    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
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Peter M View Post
    It looks like you need a subform/report with a cross-tab query.
    ....
    There are tricks to have your cross tab show two different values (Hours and Percent) but I cannot remember them off the top of my head.
    You could combine both hours & percent into the same field value e.g. [Hours] & "(" & [Percent] & ")" => 22.00 (46.56%)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you go the sub report route, you could place subs side by side if there are only a few categories and the field collection isn't too wide.
    Then there is the Section.NewRowOrCol property that may work if the report is split into columns (Page Setup tab in 2016 Ribbon for columns) as long as there is a group header or footer, but I have about zero experience with it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    dmckphx is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2018
    Posts
    2
    Appreciate all of the feedback. Unfortunately a Cross Tab Query won't work because this is a report based on multiple possible selections in the base query (the base query combines multiple tables). We fail two of the most important cross tab criteria in Access. I've tried sub-reports in the past and never had any luck getting them to work. I was hoping there was a formatting trick in the report function to simply force those fields to read left to right instead of vertically. It sounds like that's not possible. Looks like we end up with reports that have a lot of pages.

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Crosstab queries can be based on tables or queries (based on multiple tables).
    They can for example be based on aggregate queries

    So exactly what are the conditions that prevent you using a crosstab query?

    In addition subreports are a very valuable tool (as are subforms) and will significantly enhance what you can achieve in Access

    It is also possible to divide your report width into multiple columns but I very much doubt that will be useful for this purpose
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

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

Similar Threads

  1. Replies: 5
    Last Post: 05-10-2014, 09:24 AM
  2. Replies: 4
    Last Post: 02-25-2014, 01:09 AM
  3. Replies: 12
    Last Post: 07-31-2013, 01:29 PM
  4. Rows to columns
    By zaffar_mughal in forum Access
    Replies: 1
    Last Post: 12-03-2012, 01:18 PM
  5. Rows into Columns
    By chrisdavis in forum Programming
    Replies: 16
    Last Post: 12-22-2011, 01:58 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