Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105

    Report based on a cross Tab Query


    Good day,
    Is it possible to run a report from a crosstab query. I have a date criteria and it runs the first time, but when I input a new date criteria it does nt work.
    This is my cose I copied from the SQL.

    TRANSFORM Sum([Monthly Soc Security Payment Table].[Total Weekly Contribution]) AS [SumOfTotal Weekly Contribution]
    SELECT [Monthly Soc Security Payment Table].[Last Name], [Monthly Soc Security Payment Table].[First Name], [Monthly Soc Security Payment Table].[Social Security Number], Sum([Monthly Soc Security Payment Table].Gross) AS SumOfGross
    FROM [Monthly Soc Security Payment Table]
    GROUP BY [Monthly Soc Security Payment Table].[Last Name], [Monthly Soc Security Payment Table].[First Name], [Monthly Soc Security Payment Table].[Social Security Number]
    PIVOT [Monthly Soc Security Payment Table].[Begin Date];

    The report keeps locked in to the first criteria.

    Thanks

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Yes it is. In fact its a very common use of crosstab queries.
    Where are you specifying the date used in the pivot?
    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

  3. #3
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    I missed a step. I create a table from my main table with monthly payments from a query with criteria Between [Forms]![Dialog]![Begin Date] And [Forms]![Dialog]![Ending Pay Period] - specifying the month I am working on. From that table I do the crosstab. When I change the dates and run the query and create the table anew, the crosstab query works, but the report is stuck on the previous dates and will not run. It gives an error on the dates.

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    In that case your SQL is missing a WHERE or HAVING clause.
    Please post the complete SQL

    So you select the dates for your query in a form - do you run it whilst the form is still open - ideally run it from the form using a button click after selecting the dates
    Also what is your Windows date format? mm/dd/yyyy?
    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

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Quote Originally Posted by ridders52 View Post
    In that case your SQL is missing a WHERE or HAVING clause.
    Please post the complete SQL

    So you select the dates for your query in a form - do you run it whilst the form is still open - ideally run it from the form using a button click after selecting the dates
    Also what is your Windows date format? mm/dd/yyyy?
    Yes I run the query from the form. I did not do the report as it is not working. The SQL for the creation of the table with the data is
    SELECT [Employee Records].[Last Name], [Employee Records].[First Name], [Salary Register Report].Name, [Salary Register Report].[Number of Weeks] AS [Num of weeks], [Salary Register Report].Gross, [Salary Register Report].[Begin Date], [Salary Register Report].[End Date], [Salary Register Report].[Social Security], [Weekly Contribution].[Employer Contribution], [Social Security]+[Employer Contribution] AS [Total Weekly Contribution], [Salary Register Report].[Social Security Number], [Social Security]+[Employer Contribution] AS TOTAL INTO [Monthly Soc Security Payment Table]
    FROM [Employee Records] RIGHT JOIN ([Salary Register Report] LEFT JOIN [Weekly Contribution] ON [Salary Register Report].[Social Security] = [Weekly Contribution].[Employee Contribution]) ON [Employee Records].[Social Security Number] = [Salary Register Report].[Social Security Number]
    WHERE ((([Salary Register Report].[Begin Date]) Between [Forms]![Dialog]![Begin Date] And [Forms]![Dialog]![Ending Pay Period]));



    the cross tab SYQ is
    TRANSFORM Sum([Monthly Soc Security Payment Table].[Total Weekly Contribution]) AS [SumOfTotal Weekly Contribution]
    SELECT [Monthly Soc Security Payment Table].[Last Name], [Monthly Soc Security Payment Table].[First Name], [Monthly Soc Security Payment Table].[Social Security Number], Sum([Monthly Soc Security Payment Table].Gross) AS SumOfGross
    FROM [Monthly Soc Security Payment Table]
    GROUP BY [Monthly Soc Security Payment Table].[Last Name], [Monthly Soc Security Payment Table].[First Name], [Monthly Soc Security Payment Table].[Social Security Number]
    PIVOT [Monthly Soc Security Payment Table].[Begin Date];

    Not sure how to check the windows date format but in access I use mm/dd/18. I would much rather use dd/mm/18

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There should be no reason to create a temporary table.
    Select your start and end dates then create a crosstab from an aggregate query - a select version of your make table.
    This should then update automatically and you will not be adding file bloat with unnecessary temp tables
    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

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Where are you located? Why would you want to use dd/mm/yyyy? Review http://allenbrowne.com/ser-36.html
    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.

  9. #9
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    In Belize Central America. We use Englisg standards mostly, but it would create a problem since I have already created the DB the I would leave it.

  10. #10
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Thanks, seems too complex for me. I will leave as is.

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure if this is relevant, but it relates to something written in the first post "The report keeps locked in to the first criteria."
    If you alter criteria and run the query behind the report, you have to close and reopen that report, or at least requery it.
    Seems to me that's what you're saying the issue is as you also wrote "but the report is stuck on the previous dates"
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    I run my report from a macro which runs the first query then the report based on my criteria.
    the result of the query looks like this eg: The date fields become part of the report to where it looks for data that matches the last report run basically 11/4/18; 11/11/18 regardless of my new input dates of December
    Social Security Number SumOfGross 11/4/2018 11/11/2018
    000333333 $1,362.00 $25.60 $25.60
    000222222 $1,589.00 $25.60 $25.60
    000555555 $2,724.00 $25.60 $25.60
    000444444 $2,270.00 $25.60 $25.60
    000001111 $1,702.50 $25.60 $25.60

  13. #13
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Please see report attached.
    Attached Files Attached Files

  14. #14
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't really understand what you're doing. A report automatically loads a query when you open the report, so I'm not seeing the need for opening a query so that you can open a report. Also, you might have a report based on a Totals query (given the SumOfGross field) which can have it's own set of problems, but I'm not understanding why there's more fields in the report than there are in the query. That leads me to think the query might be a crosstab, not a Totals query. Either post a zipped copy of your db or pics of query & report design view with more explanation perhaps. Db would definitely help in zeroing in on the problem.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Creating a report to run perpetually on crosstab is tricky but can be done. Since the headers of a CROSSTAB often change, this means the textboxes in report will error because their ControlSource is no longer valid. This can be dealt with. Yes, there will be a bit of a learning curve to accomplish. There are a number of tutorial sources on this very topic. For a start, the CROSSTAB must use PARAMETERS. The Allen Browne link describes that. Here are some more:

    http://www.fmsinc.com/MicrosoftAcces...ort/index.html

    http://www.access.hookom.net/Dynamic...rosstabRpt.htm
    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.

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-15-2017, 08:44 AM
  2. Cross-Tab Report?
    By WCStarks in forum Reports
    Replies: 12
    Last Post: 08-14-2017, 03:14 PM
  3. Replies: 3
    Last Post: 07-26-2016, 02:12 PM
  4. Pass Parameters from Cross Tab Query to Report
    By jokeboy1 in forum Reports
    Replies: 5
    Last Post: 01-02-2015, 01:26 PM
  5. Adding rows on cross-tab query report
    By KahluaFawn in forum Reports
    Replies: 2
    Last Post: 02-18-2009, 10:09 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