Results 1 to 8 of 8
  1. #1
    pbuecken is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    6

    Report based on crosstab query

    I am not sure if what i am trying to do is possible.


    I have a crosstab query which depending on the filter has more or less columns. How do i create a report based on this crosstab query? If i create a report based on the query when it has maximum columns the report will have an error when there are less columns in the query.

    Anyone has done something like that or similar.

    Cheers

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816

    Left to Right Records

    That is the issue with basing reports on crosstab query. They can be so dynamic. It is possible to structure data manipulation to stabilize the crosstab. Would have to know more about your data. Other methods involve IIf expressions, domain aggregate functions, VBA code procedure.

    Review this thread
    http://forums.aspfree.com/microsoft-...ry-322123.html
    Last edited by June7; 01-17-2012 at 04:18 PM.
    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
    pbuecken is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    6
    Thanks June 7 have been trying with union and so on but with no success i have attached my current status. As you will see when running the crosstab query i only get 3 out of possible 4 columns.

    Thanks for your help.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Change the TSR Query to RIGHT and LEFT joins instead of INNER.

    SELECT TSR.ID, TSRDescription.TSRActivity, User.FullName
    FROM [User] RIGHT JOIN (TSRDescription LEFT JOIN TSR ON TSRDescription.[ID] = TSR.[TSRDescription]) ON User.[ID] = TSR.[UserName];
    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
    pbuecken is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    6
    Thanks, this does the job when i a displaying all the users but as soon as i filter it for a specified user the query only displays the cell for this user.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Are these 4 the only TSR activities? Can do this:
    SELECT TSR.UserName, User.FullName, Sum(IIf([TSRDescription]=1,1,0)) AS Service, Sum(IIf([TSRDescription]=2,1,0)) AS Meeting, Sum(IIf([TSRDescription]=3,1,0)) AS Trial, Sum(IIf([TSRDescription]=4,1,0)) AS Training
    FROM [User] RIGHT JOIN (TSRDescription RIGHT JOIN TSR ON TSRDescription.ID = TSR.TSRDescription) ON User.ID = TSR.UserName
    GROUP BY TSR.UserName, User.FullName;

    Are you the only user of this db? You are using Lookup on UserName and TSRDescription fields in the TSR table. This makes it look the values are the text, but is really the number IDs. I never use Lookup on tables. I build forms and reports to work with the data and use Lookup. I want to see the true value when I view tables.
    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
    pbuecken is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2012
    Posts
    6
    Yes, this manual fix works great, Thanks for you help. Being rather new to access i saw some tutorial where thy have used lookups in tables, but i understand where you coming from. I might do a redesign of this.

    Thanks for your help.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Yes, I know a lot of tutorials use the lookups. They are trying to simplify creating forms and reports, especially for novices. As users should not be working directly with tables and queries, the only purpose (with rare exception) of setting properties in table is when forms/reports are created by wizard or drag/drop of fields from field list, those properties will be adopted by the control. However, changing property in table will not carry over to controls already created.
    Last edited by June7; 01-17-2012 at 08:33 PM.
    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. Replies: 2
    Last Post: 08-17-2011, 03:02 AM
  2. Replies: 0
    Last Post: 05-09-2011, 01:51 PM
  3. Replies: 1
    Last Post: 09-05-2010, 11:28 AM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. Replies: 1
    Last Post: 02-02-2009, 05:52 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