Need help with cross-tab query 2

[robojeff]

I used the cross tab wizard to create a cross-tab query but it is not quite what I need...

The SQL for this is:

TRANSFORM Count([MY_tbl].ID) AS [Total Of ID]
SELECT [MY_tbl].PCode1
FROM [MY_tbl]
WHERE ((([MY_tbl].[Fail])=1) AND (([MY_tbl].Date) Between [forms]![reports]![StartDate] And [forms]![reports]![EndDate]))
GROUP BY [MY_tbl].PCode1, [MY_tbl].Late, [MY_tbl].[Fail], [MY_tbl].Date
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

This lists the Pcodes from the My_tbl in each row, and the months in each column but it displays multiple entries of each Pcode for each month.

What I would like for this query to do is display the total number of pcodes (rows) for each month (columns) but I would also like to have the columns filtered into each month of the previous year to create a rolling average cross tab query.

For example, the cross-tab query will display something like the following:

Pcode Jan Feb Mar
------- ----- ----- -----
1 1
1 1
1 1
1 1
1 1
1 1
2 5

and I would prefer:

Pcode Jan08 Feb08 Mar08
----- ----- ----- -----
1 3 2 1
2 5

In standard select queries, I have been able to accomplish this type of ordering with a simple SQL statements as "ORDER BY (Year([Date])*12+Month([Date])-1);" but I am not sure how to do this in the cross-tab query.

Can any one set me straight on this?

thank you

 

[lameid]

First your multiple records... It looks like you have extra grouping. Try, taking out [MY_tbl].Late, [MY_tbl].[Fail] out of the group by clause...

TRANSFORM Count([MY_tbl].ID) AS [Total Of ID]
SELECT [MY_tbl].PCode1
FROM [MY_tbl]
WHERE ((([MY_tbl].[Fail])=1) AND (([MY_tbl].Date) Between [forms]![reports]![StartDate] And [forms]![reports]![EndDate]))
GROUP BY [MY_tbl].PCode1, [MY_tbl].Date
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[lameid]

Secondly, I don't follow. Can you post some example data from your table and the desired result?

 

[robojeff]

Thank you Lameid-

I changed the Grouping to:
GROUP BY [My_tbl].PCode and this gave me the grouping that I need but I still need to somehow add the year to the months to display them into mmmyy format

 

[lameid]

I wouldn't include the year in the column heading but would add a column for it, Year([Date]). This will prevent problems of months or years not being there. But if you want to do it...

Change

PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

To

PIVOT Format([Date],"mmmyy");

Note the IN statement in the original says these are all and only columns to disply. When you add year in the mix, you want it to change over time and this doesn't work. But if you don't have any values for February 2008, it will skip that column altogether.

 

[robojeff]

Thank you Lameid-

When I try that date format I get an error stating, : The Microsoft Jet database does not recognize 'forms![report}!StartDate]' as a valid field name or expression

The Start Date and End date on the form which specifies this range is set to the Short Date format...

What do I need to change to accomodate the "PIVOT Format([Date],"mmmyy");" format?

 

[lameid]

Something has changed from your original post, likely in the where clause. Or you have parameters defined, which should be visible in the SQL as well.
In either case, this is the entire correct statement.

TRANSFORM Count([MY_tbl].ID) AS [Total Of ID]
SELECT [MY_tbl].PCode1
FROM [MY_tbl]
WHERE ((([MY_tbl].[Fail])=1) AND (([MY_tbl].Date) Between [forms]![reports]![StartDate] And [forms]![reports]![EndDate]))
GROUP BY [MY_tbl].PCode1
PIVOT Format([Date],"mmmyy")

 

[robojeff]

This is a bit confusing to me...

If I set the SQL code to:

TRANSFORM Count([MY_tbl].ID) AS [Total Of ID]
SELECT [MY_tbl].PCode1
FROM [MY_tbl]
WHERE ((([MY_tbl].[Fail])=1) AND (([MY_tbl].Date) Between [forms]![reports]![StartDate] And [forms]![reports]![EndDate]))
GROUP BY [MY_tbl].PCode1
PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

I get all of the data per each month but there is no year shown as in mmmyy as I need to set this up as...

If I set the SQL code to:

TRANSFORM Count([MY_tbl].ID) AS [Total Of ID]
SELECT [MY_tbl].PCode1
FROM [MY_tbl]
WHERE ((([MY_tbl].[Fail])=1) AND (([MY_tbl].Date) Between [forms]![reports]![StartDate] And [forms]![reports]![EndDate]))
GROUP BY [MY_tbl].PCode1
PIVOT Format([Date],"mmmyy") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

then I get all of the month names displayed in the header.
but no data in the columns under the month names...

If I set the SQL code to:

TRANSFORM Count([MY_tbl].ID) AS [Total Of ID]
SELECT [MY_tbl].PCode1
FROM [MY_tbl]
WHERE ((([MY_tbl].[Fail])=1) AND (([MY_tbl].Date) Between [forms]![reports]![StartDate] And [forms]![reports]![EndDate]))
GROUP BY [MY_tbl].PCode1
PIVOT Format([Date],"mmmyy");

then I get I get an error stating, : The Microsoft Jet database does not recognize 'forms![report]!StartDate]' as a valid field name or expression


I am so confused ...

 

[dhookom]

You must explicitly identify the data types of parameters in crosstab queries. Select Query->Parameters and enter:
[forms]![reports]![StartDate] Date/Time
[forms]![reports]![EndDate] Date/Time

Duane
Hook'D on Access
MS Access MVP

 

[robojeff]

Thanks Duane-

identifying the data types displays the month and the year with the data but they are now sorted in Alpha order (APR08, AUG08, DEC08, FEB08)instead of calendar order (Jan08, Feb08, Mar08)

How do I get these to display in the calendar order?

 

[dhookom]

You have a few options.
[li]Change the column headings to Format([Date],"yymm")[/li]
[li]Enter "Jan08", "Feb08", "Mar08",... into the Column Headings property of the crosstab query[/li]
[li]If you are using this query as the Record Source for a report, use the solution at faq703-5466[/li]




Duane
Hook'D on Access
MS Access MVP