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

    Thanks so much for the advice. I would need more guidance, it is kind of overwelming. However, is there a SQL or other code that I can add to my macro to delete the report before running the new one.

  2. #32
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You don't delete report, you close it.

    What new one? Aren't you simply editing report design for the revised column headers?
    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. #33
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Thanks. I briefly looked at it to understand what it offers. My report has to have absolute dates, as it is for payment to social security board and has to specify the week it is paying for. I cant even use the number of the week eg 14, 15. It is a standardized SS report I am trying to have generated by my payroll database which stores the beginning and ending of the weekly payroll etc. Any thoughts on this.
    Thanks

  4. #34
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    The crosstab query runs fine.

  5. #35
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What do you mean by 'absolute' dates? As you can see from ridders example, there are dates on the report.

    How do you specify a week - by date of first day of week - starts on Sun?
    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. #36
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    PAYROLL FOR DEMO for FORUM.zip
    Please see a portion of the DB. TEST Username clerk1 password clerk1 Data in DB for month of 1-30/11/18 and 1- 31/12/18. ENter beginning and ending dates and year.
    run the crosstab query. Kindly check the report.

    Thanks

  7. #37
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Done. I entered those dates on form but the query pulls up November data, not December. There are only November records in table. The query does not have filter criteria so the form inputs are irrelevant.

    Yes, query matches report design, will have to manually modify the report design to match when the begin and end dates are different. The query needs filter criteria first.

    See no attempt to incorporate suggestions.
    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.

  8. #38
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    Thank you all for your assistance. WIll not proceed to the report. Will leave as a query. I am not familiar with cods and the examples are to complex for me.

    Regards

  9. #39
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I wrote an example of producing reports from a dynamic query (column headers change over time). It can be done but there's code involved.

    The example database I posted is here:

    https://www.accessforums.net/showthread.php?t=45032

  10. #40
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Even if you don't build report, the query needs filter parameters.
    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.

  11. #41
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    @rpeare
    I use a similar approach though with many more columns where needed Over 70 in one case.
    Two examples in links with posts 16 and 24
    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

  12. #42
    chavez_sea is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Feb 2011
    Posts
    105
    What is filter parameters? Is that criteria. My process might be a bit cumbersome. I run a make table query with criteria using the input dates on the dialog form. Begin and end date and year. It creates a new table with data for the month. I then run the cross tab query from the table . I created a macro yo run the make table query and the crosstab. If I got the report to run I would include in that macro.

  13. #43
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Where you enter the filter direct into the query designer I refer to it as filter criteria.
    Where it requires the user to enter the values at the time the query is run, those are PARAMETERS.
    That can be done by writing PARAMETERS as the start of the first line in a SQL query or by using an expression such as [Enter a value] in [] brackets in the query designer filter row
    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

  14. #44
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Following Duane Hookum's very brief guidelines, I did the following:

    1. CROSSTAB query:
    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 "Wk" & ([Begin Date]-[Forms]![Dialog]![Begin Date])/7+1 In ("Wk1","Wk2","Wk3","Wk4");

    2. Bind textboxes on report to the Wk# fields (and change their names as well)

    3. Instead of label controls, use textboxes for date headers (select label > right click > Change To > Text Box) - set ControlSource and change names
    Wk1Col =[Forms]![Dialog]![Begin Date]
    Wk2Col =[Forms]![Dialog]![Begin Date] + 7
    WK3Col =[Forms]![Dialog]![Begin Date] + 14
    WK4Col =[Forms]![Dialog]![Begin Date] + 21

    This could certainly be adjusted to eliminate the MAKE TABLE steps. Personally, I would not use MAKE TABLE. I use 'temp' tables but the tables are permanent and records are temporary. Run DELETE on table before running process and use INSERT SELECT action. Use the generic WK# field names and set up report as I describe binding to those fields.

    Dynamic parameters can reference form controls for user input. A WHERE clause in the above query can be:
    WHERE ((([Monthly Soc Security Payment Table].[Begin Date]) Between [Forms]![Dialog]![Begin Date] And [Forms]![Dialog]![Ending Pay Period]))

    Instead of the 'temp' table, use a query that pulls the data together as source for the CROSSTAB. Should even be able to do those JOINS within the CROSSTAB design so only 1 query object is involved.

    Suggest using comboboxes instead of textboxes for user input of date parameters, like:
    RowSource: SELECT DISTINCT [Begin Date] FROM [Salary Register];
    LimitToList: Yes
    DefaultValue: Date()-(Weekday(Date())-1)-28

    And if you always want 4 weeks, the end period date parameter can be calculated. Less for the user to do and less chance for error.
    Last edited by June7; 12-06-2018 at 01:19 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.

Page 3 of 3 FirstFirst 123
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