Crosstab Column Names

Leesha
5StarLounger
Posts: 1064
Joined: 05 Feb 2010, 22:25

Crosstab Column Names

Post by Leesha »

Hi,
I have a user with crosstab query that uses [WKEndingDate] as the column header. [WKEndingDate] consists of the Tuesdays during a billing cycle. Billing cycles time frames from month to month. This is where the problem lies. The DB will need to use the results of the crosstab query in other queries each month. Currently the column headings are the [WKEndingDate] results. For example for May they are 4/28/20, 5/5/20/ 5/12/20, 5/19/20 and 5/26/20. For June they will be 6/2/20, 6/9/20, 6/16/20, 6/23/20 and 6/30/20. Since the column headings change from month to month it is impossible for me to use them in preset queries. I need the column header to say Week1, Week2, Week3, Week4, Week5, and Week5. Is there a way to do this? I have spent hours trying to get this to work with no luck.
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Crosstab Column Names

Post by HansV »

How do you restrict the dates in the query?
Regards,
Hans

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Crosstab Column Names

Post by HansV »

It would be nice to have a sample database to work with.
Regards,
Hans

Leesha
5StarLounger
Posts: 1064
Joined: 05 Feb 2010, 22:25

Re: Crosstab Column Names

Post by Leesha »

They are on frmSwitchboard txtstartdate and txtenddate

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Crosstab Column Names

Post by HansV »

Could you attach a sample database (zipped)?
Regards,
Hans

Leesha
5StarLounger
Posts: 1064
Joined: 05 Feb 2010, 22:25

Re: Crosstab Column Names

Post by Leesha »

I'm attaching a zipped copy of a very stripped down version. The date range for the data is 6/1/20 - 6/30/20, although the data only goes to 6/23/20. The query that I'm working with is qrySalesLogMissingDataCrosstab. My goal is that the columns would be labeled Week1, Week2, Week3, Week4, Week5 and Week6 (even if the data doesn't go out that far) vs the actual week ending date.
Thanks!
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Crosstab Column Names

Post by HansV »

Hi Leesha, thanks!

I created a new query qryFirstDate that returns the first (earliest) week ending date in the specified date range, in the field FirstDate.
I added this query to qrySalesLogMissingDataCrosstab. Since qryFirstDate returns only a single record, we don't have to join it to the other query.
We subtract FirstDate from WkEndingDate and divide by 7 to calculate the number of weeks since the first week ending date. This returns 0, 1, 2, ...
Add 1 to get 1, 2, 3, ... and concatenate with "Week" to return the result that you want:

Week: "Week" & ([WKEndingDate]-[FirstDate])/7+1

See the attached version.

WEDateCrosstab.zip
You do not have the required permissions to view the files attached to this post.
Regards,
Hans

Leesha
5StarLounger
Posts: 1064
Joined: 05 Feb 2010, 22:25

Re: Crosstab Column Names

Post by Leesha »

Hi Hans!
You don't even want to know all the different things I tried to get this to work. This is beautiful! I sooo appreciate the explanation of why it works. Here is my glitch. I need it to show week5, and week6 even though there is not data for it for this sample of for this particular month. The reason why is that this crosstab query will be used to capture weeks where there is not data for the particular time frame. For example, both stores in this sample do not have sales data for the last week of the month. The end user will need to know that, in the same way that store 62 comes up blank in week1. Some months there are 6 week ending dates. Is this possible?
Thanks,
Leesha

User avatar
HansV
Administrator
Posts: 68783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Crosstab Column Names

Post by HansV »

Fortunately, that is easy.
Open the crosstab query in design view.
Click in an empty part of the upper pane of the query window.
If necessary, activate the Property Sheet (press F4 if you don't see it).
Enter the following in the Column Headings property:

Week1,Week2,Week3,Week4,Week5,Week6

When you press Enter, Access will add quotes around the individual values.
You will now always see column headings Week1 to Week6, regardless of how many weeks the data contain.
Regards,
Hans

Leesha
5StarLounger
Posts: 1064
Joined: 05 Feb 2010, 22:25

Re: Crosstab Column Names

Post by Leesha »

Perfect!!!! You're the best! Thanks so much!