Trouble sorting this one out

[SiE]

I need to generate a report concerning sales.

I have a table with orders in related to a table with order contents.

all orders are related to salespeople.

I want to work out what customers have spent over the last 3 months by salesperson

so i thought of a crosstab.

I used a query first of all to sum up what customers have spent and narrowed the date purchased from dd/mm/yyyy to a mm/yyyy format, criteria by last 3 months.

I then created a cross tab to take each account and show there sales for the 3 months.

Sounded easy to me but the crosstab says it cannot work out the dates even though the initial query runs fine.

 

[SiE]

SQL to see any probs.

TRANSFORM Sum(([TBL_Order_Contents]![Qty]*[TBL_Order_Contents]![Price Sold])) AS spent
SELECT TBL_Orders.EmployeeID, TBL_Orders.[Customer ID]
FROM TBL_Orders INNER JOIN TBL_Order_Contents ON TBL_Orders.[Order Number] = TBL_Order_Contents.[Order Ref]
WHERE (((DatePart("m",[TBL_Orders]![Date Order Placed]) & "/" & DatePart("yyyy",[TBL_Orders]![Date Order Placed]))=[Forms]![FRM_Reporting_Suite]![Current_Month] Or (DatePart("m",[TBL_Orders]![Date Order Placed]) & "/" & DatePart("yyyy",[TBL_Orders]![Date Order Placed]))=[Forms]![FRM_Reporting_Suite]![Previous_Month] Or (DatePart("m",[TBL_Orders]![Date Order Placed]) & "/" & DatePart("yyyy",[TBL_Orders]![Date Order Placed]))=[Forms]![FRM_Reporting_Suite]![Previous_Month2]) AND ((TBL_Orders.[Quote Or Order])="Order"))
GROUP BY TBL_Orders.EmployeeID, TBL_Orders.[Customer ID], TBL_Orders.[Quote Or Order]
PIVOT DatePart("m",[TBL_Orders]![Date Order Placed]) & "/" & DatePart("yyyy",[TBL_Orders]![Date Order Placed]);

 

[SiE]

Doesnt matter. Sorted it sort of.

I just created a temp table with sales summed up alongside accounts and reps with the date fields cut down then ran the crosstab on the temp table. bit crappy but it works. if anyone has a better suggestion im open to offers.