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
Crosstab Column Names
-
- Administrator
- Posts: 78233
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- Administrator
- Posts: 78233
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Crosstab Column Names
It would be nice to have a sample database to work with.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Crosstab Column Names
They are on frmSwitchboard txtstartdate and txtenddate
-
- Administrator
- Posts: 78233
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Crosstab Column Names
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!
Thanks!
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78233
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Crosstab Column Names
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.
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.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Crosstab Column Names
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
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
-
- Administrator
- Posts: 78233
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Crosstab Column Names
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1484
- Joined: 05 Feb 2010, 22:25
Re: Crosstab Column Names
Perfect!!!! You're the best! Thanks so much!