Hi Roy,
It sounds like you basically have the result you want, except that it
includes all of the data instead of just the last three month's worth of
data. Is this correct? If so, you need to include a date criteria.
Not knowing the names of your fields or tables, I will offer you a similar
crosstab query result that should work in the Northwind sample database. Find
a copy of this database, which is likely already on your hard drive (search
for Northwind.mdb). When you find it, make a copy of it that you can use for
testing.
First, we need to run an update query enough times to update all orders, so
that there will be orders present for the last three months. Create a new
query. Dismiss the Show Table dialog without picking any tables. In query
design view, click on View > SQL View. Replace the default keyword "SELECT;"
with the following SQL statement:
UPDATE Orders SET Orders.OrderDate = DateAdd("yyyy",1,[OrderDate]);
Save this query as: qupdAdd1YearToEachOrderDate
Create another new query and paste the following SQL statement into the SQL
View:
SELECT Min(Orders.OrderDate) AS [Earliest Order],
Max(Orders.OrderDate) AS [Latest Order]
FROM Orders;
Save this query as: qryShowMinAndMaxOrderDate
Run your new update query once. Then run the select query that displays the
minimum and maximum order dates. Keep running the update query enough times
until the select query reveals that the order dates encompass the current
month plus the last few months.
Now create a new crosstab query:
TRANSFORM
Sum(CCur((1-[Discount])*([Order Details.UnitPrice]*[Quantity]))) AS Sales
SELECT [Firstname] & " " & [LastName] AS Employee, Sum([Sales]) AS Total
FROM (Employees INNER JOIN Orders ON Employees.EmployeeID =
Orders.EmployeeID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
WHERE (((Orders.OrderDate) Between
Date() And DateAdd("m",-2,Month(Date()) & "/1/" & Year(Date()))))
GROUP BY [Firstname] & " " & [LastName]
PIVOT Right(" " & Right("0" & DatePart("m",[OrderDate]),2) & "/" &
DatePart("yyyy",[OrderDate]),8-(DatePart("yyyy",[OrderDate])-Year(Date())+1));
Save this query as: qxtbSalesByMonthForLastThreeMonths
Tom
__________________________________________
:
I have two tables and create a cross tab query on which the report is set.
This query can show all period with individual amount. But I just need to
monthly present the report for the latest three months. What can i do for
this.
__________________________________________
:
If you will clarify what data you have, and how you have it stored, someone
may well be able to assist. Just knowing what you want, without that
information, we probably cannot be of much help.
Larry Linson
Microsoft Access MVP
__________________________________________
"aggregate sum over the periods"
Hi,
I would like to have report like below:
Month M-2 Month M-1 Month M Accu M
ProductID 50 70 60 180
Accu M is the accumuated amount and Month is for current month only.
Please help me to set the formula in the report to achieve this result.
Roy
