How to group data by date in crosstab query? Access 2000

[HowHow]

First time using crosstab query because tired of creating queries and put them together in one query (such a stupid thing to do :D). Not sure how it works still.

I have link table called dbo_Roster to access 2000, I try to group data by Service and dates, for example 01/01 to 01/31 is Jan08, 01/02 to 28/2 is Feb08 and so on. The result should be like below :

[HTML]Service Jan08 Feb08 Mar08 Apr08 May08 Jun08 Total
Service1 # # # # # # #
Service2 # # # # # # #
Service3 # # # # # # #[/HTML]


However, it shows me this only:

[HTML]Service Total Feb08 Jan08
Service1 # # #
Service2 # # #
Service3 # # #[/HTML]

My code is as below:

TRANSFORM Sum([Duration]/12) AS TimeLost
 
SELECT dbo_Roster.[Service Type], Sum([TimeLost]) AS [Total Of TimeLost]
 
FROM dbo_Roster
 
WHERE (((dbo_Roster.[Service Type]) Like "RA*" Or (dbo_Roster.[Service Type]) Like "RA-*"))
 
GROUP BY dbo_Roster.[Service Type]
 
PIVOT IIf(dbo_Roster.Date Between 2008/1/1 And 2008/1/31,"Jan08",
IIf(dbo_Roster.Date Between 2008/2/1 And 2008/2/28,"Feb08",
IIf(dbo_Roster.Date Between 2008/3/1 And 2008/3/31,"Mar08",
IIf(dbo_Roster.Date Between 2008/4/1 And 2008/4/30, "Apr08",
IIf(dbo_Roster.Date Between 2008/5/1 And 2008/05/31, "May08",
IIf (dbo_Roster.Date Between 2008/6/1 And 2008/6/30, "Jun08"))))));

I think I must have get the crosstab concept wrong. Can anyone show me how to use it correctly?

[Stewart Ross]

Hi. The date comparisons in your IIF statements are unlikely to work as you expect them to, as you are comparing date literal values that are non-standard in SQL (and not delimited by # characters either). Also, the monthly headings will be ordered alphabetically (hence Feb08 before Jan08 and so on).

I would suggest using the Format function to return the year and month as a numeric combination year-first (to order correctly on year changes such as December to January), using a Pivot statement like this in place of your IIFs:

PIVOT Format(dboRoster.Date,"yyyy-mm")

Please note that a crosstab query will leave out columns that have no matching rows in the source data, so it is possible to skip months and so on unless the column headers have been explicitly defined for the query concerned. Also, if for any row group summarised there is no data for a particular pivot column a null value is recorded in that column, not a zero. The example crosstab below demonstrates both flaws of crosstab queries, as shown in one of the attached screenshots.

TRANSFORM Count(BranchEntries.[Branch#]) AS [CountOfBranch#]
SELECT BranchEntries.[Branch#]
FROM BranchEntries
GROUP BY BranchEntries.[Branch#]
PIVOT Format([EntryDate],"yyyy-mm");

Use of Nz (and CLng to transform the result back to a number) resolves the null value issue, as shown in the other screenshot.

TRANSFORM CLng(Nz(Count([Branch#]),0)) AS Expr2

Hope this helps.

-Stewart

[HowHow]

Hi,

Thanks...I was using "#" to represent numbers without explaining in the previous post..sorry for the confusion....fe w more questions though...

1. When I use clng, the numbers behind decimal is gone. For example, 1.5 become 2...etc. If I am not using clng, some of the figures has lots of numbers behind the decimal. What should I do if I only need two decimal only?

2.
a.Total column always goes in front of the "year and month" column. Is there anyway to put them behind? I tried to move them to the back in design view in query but it didn't work.

b.If total is needed every 3 months, or 6 months, can it be done? a total in between the "month column". For example, Jan, Feb, Mar, Total (Jan to Mar), Apr, May, June, Total (Apr to June), Grand Total (Total from Jan to June)


3.How to define months explicitly in query so that the month column still show even though there is no figure in it? Maybe can replace by zero if there is nothing in it?

Many thanks for your time

[Stewart Ross]

Hi again. In answer to your questions:

1. Use CDbl in place of CLng - converts to double-precision floating point values, whereas CLng is a long integer (whole number) conversion.

2. (a) There is no way you can place row summary data (such as your totals) after the pivoted columns. Such data is always displayed before the pivoted columns.

2 (b) Can't be done using an Access crosstab query.

3. It is possible to define specific column headings for the crosstab query, but I find this a very inflexible approach. By their nature, the headings are static. But the row data is not static, and there is a conflict between defining a set of headings and the moving months of real data.

There are more complex approaches which could be taken using a combination of SQL and VBA code to provide summary totals and so on, but it is simpler in many ways to export the data (or summaries of it) to Excel, which is designed for such data manipulation (which is not what databases do best).

-Stewart

Hi,
Thanks...I was using "#" to represent numbers without explaining in the previous post..sorry for the confusion....fe w more questions though...

1. When I use clng, the numbers behind decimal is gone. For example, 1.5 become 2...etc. If I am not using clng, some of the figures has lots of numbers behind the decimal. What should I do if I only need two decimal only?

2.
a.Total column always goes in front of the "year and month" column. Is there anyway to put them behind? I tried to move them to the back in design view in query but it didn't work.

b.If total is needed every 3 months, or 6 months, can it be done? a total in between the "month column". For example, Jan, Feb, Mar, Total (Jan to Mar), Apr, May, June, Total (Apr to June), Grand Total (Total from Jan to June)

3.How to define months explicitly in query so that the month column still show even though there is no figure in it? Maybe can replace by zero if there is nothing in it?

Many thanks for your time

[Stewart Ross]

Hi again. Just to add to my previous reply, the # characters I was referring to were what should have been delimiting the dates in your IIF statements (post 1). If you include literal dates in Access SQL statements these are delimited by # characters like this:

Where Table.SomeDate = #01/31/2008#

The SQL standard requires that dates listed as literals be presented in the American convention of month/day/year. It was for these two reasons I was mentioning that your crosstab was unlikely to return the values you would expect.

There is an excellent HowTo article by our Admin contributor NeoPa on this topic linked here for reference.

-Stewart

I was using "#" to represent numbers without explaining in the previous post..sorry for the confusion....fe w more questions though...

[HowHow]

Hi Stewart,

Thank you for the answeres and tips.