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.
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.
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.
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
The crosstab query runs fine.
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.
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
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.
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
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
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.
@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
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.
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
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.