At the risk of sounding like I haven't "done my homework," I'll post this question.
I'm using a crosstab query to produce a sales report that breaks out monthly revenue for the year. The query works great, but there's a hitch.
The way our sales dept "works," a sales month doesn't correspond to a calendar month. Instead, the days in one of our sales months is determined by the last sunday of the month, plus any trailing days from the previous month. As you may guess, this poses some date calculation problems. For ex. as far as sales is concerned, this October runs from Sept. 30 thru Oct. 28, even though a good majority of the rest of the world feels it runs from Oct 1st thru Oct 31st.
In my crosstab, I'm using a MONTH([Date]) function as the Column heading to break out the revenue by month. Since our sales dept. doesn't use a calendar month, my monthly totals are off.
What I need is a way to determine what SALES month the sale record should go on based on the sales date. I've tried creating a function to calculate this, but due to the high # of records in the sales table, it really bogs down my query.
I recently found a table in the database that details the sales calendar information. The records have an ID, Startdate, Enddate, a Year# and a Month#. For example, the record that contains information about the Oct 2001 sales month says:
ID#, 9/30/01, 10/28/01, 2001, 10
I should think I should be able use this query to determine what sales month my sales record belongs in, but since there isn't any relationship between the two the sales detail table and the date table, I'm at a loss. Anyone have any thoughts? I've looked into trying a Union query, but that doesn't seem to be what I need. Maybe I'm wrong.
If anyone has any suggestions, I'm all ears. If nothing else, I'll stick with the function I've already created, although I thought I could probably get better performance using the existing sales date table rather than the function.
Thanks in advance!
js
I'm using a crosstab query to produce a sales report that breaks out monthly revenue for the year. The query works great, but there's a hitch.
The way our sales dept "works," a sales month doesn't correspond to a calendar month. Instead, the days in one of our sales months is determined by the last sunday of the month, plus any trailing days from the previous month. As you may guess, this poses some date calculation problems. For ex. as far as sales is concerned, this October runs from Sept. 30 thru Oct. 28, even though a good majority of the rest of the world feels it runs from Oct 1st thru Oct 31st.
In my crosstab, I'm using a MONTH([Date]) function as the Column heading to break out the revenue by month. Since our sales dept. doesn't use a calendar month, my monthly totals are off.
What I need is a way to determine what SALES month the sale record should go on based on the sales date. I've tried creating a function to calculate this, but due to the high # of records in the sales table, it really bogs down my query.
I recently found a table in the database that details the sales calendar information. The records have an ID, Startdate, Enddate, a Year# and a Month#. For example, the record that contains information about the Oct 2001 sales month says:
ID#, 9/30/01, 10/28/01, 2001, 10
I should think I should be able use this query to determine what sales month my sales record belongs in, but since there isn't any relationship between the two the sales detail table and the date table, I'm at a loss. Anyone have any thoughts? I've looked into trying a Union query, but that doesn't seem to be what I need. Maybe I'm wrong.
If anyone has any suggestions, I'm all ears. If nothing else, I'll stick with the function I've already created, although I thought I could probably get better performance using the existing sales date table rather than the function.
Thanks in advance!
js
