Crosstab query with columns as date ranges 1

[brokengod]

I am starting to wrap my head around access crosstab queries (with limited sucess) and am trying to generate a query that has date ranges as the columns.

I have seen the examples that show the columns shown as quarters eg Q1, Q2, etc.

If I want to have something like "< 1 year", "1-2 years, ">2 years", how would i go about this?

thanks

bg

 

[dhookom]

I would just use a totals query. Since you didn't provide any field or table names, consider:
tblSales
================
ProductID
SaleDate
Qty
This should get you a couple calculated columns. You should be able to get any others.
[tt]
SELECT ProductID,
Sum(Abs(SaleDate>=DateAdd("yyyy",-1,Date())) * [Qty]) As "LT 1 Year",
Sum(Abs(SaleDate<=DateAdd("yyyy",-1,Date())
AND SaleDate>=DateAdd("yyyy",-2,Date()))
* [Qty]) As "From 1-2 Years"
FROM tblSales
GROUP BY ProductID;
[/tt]

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[brokengod]

Thanks dhookom,

That gives me a good start and some further ideas for the query!

I can almost smell that query working perfectly!

Cheers

bg