Solved Crosstab - Current week dates in column headings (1 Viewer)

MarioUY

New member
Local time
Today, 10:13
Joined
Mar 16, 2020
Messages
10
In an application (related to the Covid-19 pandemic in the north of Brazil) I've to generate a series of crosstabs queries, a few based on current week's collected data (Sunday being the first day of the week).

To limit the query to the current week is simple with a WHERE DatePart("ww",[RecDate]) = DatePart("ww",Date()) clause.

However, my problem is to find a way to automatically fill the Column Headings (in Query Properties) with the proper (seven) dates (dates, not names), one column for each day of the current week (starting Sunday) and also in the related form where the results are displayed, otherwise I'm forced to update several crosstabs queries and related forms every week.

To place the dates as rows is not an option because some of the tables would have an unknown number of columns and headings.

Any ideas how to accomplish the task?
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:13
Joined
Aug 11, 2003
Messages
11,696
given the way the pivots work, your only solution is to build the crosstabs in vba but.... that means adjusting the forms too.... which is not "desirable"

Best way around this is to fix the columns in the crosstab by using Sunday, monday etc... as column names.
Then in the form use those columns as fixed column names and use code to fudge the labels of the columns to the corresponding dates.

Regards from a Health proffesional in The Netherlands facing simular problems :)
 

plog

Banishment Pending
Local time
Today, 10:13
Joined
May 11, 2011
Messages
11,613
Do this in Excel with a pivot table.


No cross-tab query, instead a regular query with the Date value in a field. You build your query and it generates the data you need (although not in the format). Paste it into excel on a tab called 'data'. On another tab called 'report1' build your report by making a pivot table from the data on 'data'. Make it look like you want, with the date values as column headers (and any other headers if necessary). Sort it, order it, format numbers, color it, etc. Then save it.

Next week, make a copy of this file, delete all the data on the 'data' tab (leave the column headers). Run your query and paste the data into the 'data' tab under the appropriate data columns. On the 'report' tab change the data source of the pivot table to use the data on 'data'. Done.
 

isladogs

MVP / VIP
Local time
Today, 15:13
Joined
Jan 14, 2017
Messages
18,186
You can do this with a crosstab query in Access with dynamic column headers. These are updated automatically to fit the available data.
An example report using this idea and based on a crosstab query can be found here:

The same approach can be used with forms
 

MarioUY

New member
Local time
Today, 10:13
Joined
Mar 16, 2020
Messages
10
I followed namliam's suggestions and it works beautifully well, and it's quite easy to implement. Recommended!
So thank you namliam and all of you my friends for the useful suggestions.
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:13
Joined
Aug 11, 2003
Messages
11,696
Glad you got it working, happy to help :)
 

Users who are viewing this thread

Top Bottom