Crosstab Columns

[Aoife]

Hi, I've successfully utilised the following from the 'FMS' website to create a crosstab query that generates sequential monthly column totals irrespective of whether a month contains data or not.

I'd like to manipulate the query so that I can eg enter a start date of #01/10/16# and obtain total number of orders on the [OrderDate] on a DAILY basis (not monthly basis) for next 7 days whilst also including days that don't have any orders

"The trick is to think of each month as a unique number starting from Year 0:
[INDENT]Year([OrderDate])*12+Format([OrderDate],"mm") - (Year([StartDate])*12+Format([StartDate],"mm")) + 1
[/INDENT]The equation defines each month as a number (Years * 12 + Month) and subtracts the StartDate month from the OrderDate month plus one. The result is a month number from 1 to 12 defining the months"

Can't get my head round this one, suspect I may be over-complicating this - any assistance would be appreciated

 

[Ranman256]

Q1, will pull data using the date range you need.
Q2 is the Crosstab query using Q1

 

[Aoife]

Thanks for your response. If I don't have an order on eg 25/9/16, there will be a missing column for that date. So if I've got orders on the dates either side the crosstab columns will be: 24/9/16.......26/9/16. Is there an easy way of generating the 25/9/16 column, and any other dates where there is no underlying data, so that it looks more like a works schedule?

Suspect I'll have to create another table with all dates from now until 2030 and pull them into the crosstab

 

[Ranman256]

As part of Q1, you need to add a 'date' table. This is a list of EVERY date, nothing else.
add this to Q1 as an outer join and join the date fields together.
Use the date from the Date tbl, not the date from your data tabl.

It will show every date, so thecrosstab will not have data to show.