Barb,
Create a new table (tbl_Mileage_Ranges) with the following fields and
values. The first two should be numeric (long), the last should be text
[Range_Start], [Range_End], [Col_Header]
0 50000 0 - 50,000
50001 100000 50,001 - 100,000
100001 150000 100001 - 150,000
150001 999999 > 150,000
etc.
Add this table to your query. Select the [License Plate] field as your Row
Heading, the [Col_Header] field as the column header, Sum the Expense column
as your Value, and add another field [Mileage] to the query where the
Crosstab value is "WHERE" and the criteria is "Between [Range_Start] and
[Range_End]". I've add the IN clause at the back of the PIVOT statement to
get the columns to appear in the correct order. When you do this, you have
to make sure the values are represented exactly the way they appear in the
[Col_Header] column.
Your final SQL will look something like:
TRANSFORM Sum(tbl_Expenses.Expense) AS SumOfExpense
SELECT tbl_Expenses.[License Plate]
FROM tbl_Expenses, tbl_Mileage_Ranges
WHERE (((tbl_Expenses.Mileage) Between [Range_Start] And [Range_End]))
GROUP BY tbl_Expenses.[License Plate]
PIVOT tbl_Mileage_Ranges.Col_Header In ("0 - 50,000","50,001 -
100,000","100,001 - 150,000","> 150,000");
HTH
Dale
Barb said:
Hi Everyone
I have following columns avaiable:[LicensePlate],[Expense],[Milage]
I'm trying to build a crosstab query with the expenses as value, Milage as
column headings and LicencePlate as row headings. Now the tricky part.
Instead of the actual milage as column heading I'd like to have something
like [0-50000], [50001-100000],[100001-150000] ect.
Could you please help?
Thanks
Barb
