crosstab query question 1

[longhair]

morning all,
have the following sql from a crosstab query:

TRANSFORM Sum([PCS]/Right$(tblMasterCartons![um-code],2)) AS Expr1
SELECT tbl2009Shipping1.SKU
FROM tbl2009Shipping1 INNER JOIN tblMasterCartons ON tbl2009Shipping1.SKU = tblMasterCartons.[product]
GROUP BY tbl2009Shipping1.SKU
PIVOT Mid([ConfirmedDate],6,2);

where ConfirmedDate is in the format yyyy/mm/dd-hh:mm:ss.
this works just fine when you wish to pivot on the month only. however, i was just wondering if it can be modified to pivot based on the day also.
so rather than pivot based on january (month 01) i could pivot on month and day - say 2008/12/28 thru 2009/01/27.
the beginning would always be the 28th of the previous month and the end would always be the 27th of the 'current' month. the query will usually be run in ranges - a whole year or a quarter. any thoughts or suggestions on how i should approach? can this be done in one query? or am i better off breaking out the date into seperate fields and assigning a numeric value to a field (where 2008/12/28 - 2009/01/27 would be 1, 2009/02/28 - 2009/03/27 would be 2, etc.) and then do the crosstab on that field.
thanks.
regards,
longhair

 

[dhookom]

Is ConfirmedDate a real date datatype? If so, you shouldn't be using a string function against it. You also seem to be dividing a number by a string. I would use:

TRANSFORM Sum([PCS]/Val(Right(tblMasterCartons![um-code],2))) AS Expr1
SELECT tbl2009Shipping1.SKU
FROM tbl2009Shipping1 INNER JOIN tblMasterCartons ON tbl2009Shipping1.SKU = tblMasterCartons.[product]
GROUP BY tbl2009Shipping1.SKU
PIVOT  Month([ConfirmedDate]);

To pivot on Month and Day, you could use:

TRANSFORM Sum([PCS]/Val(Right(tblMasterCartons![um-code],2))) AS Expr1
SELECT tbl2009Shipping1.SKU
FROM tbl2009Shipping1 INNER JOIN tblMasterCartons ON tbl2009Shipping1.SKU = tblMasterCartons.[product]
GROUP BY tbl2009Shipping1.SKU
PIVOT  DateValue([ConfirmedDate]);

Duane
Hook'D on Access
MS Access MVP

 

[longhair]

dhookum,
think you missed part of the post...
i don't want to do the crosstab on any change of the date, but if the date is within a certain defined range ie between the 26th of the previos month and the 27th of the current month.
thanks for the reply, as your suggestions are usually spot on.
regards,
longhair

 

[dhookom]

Is the ConfirmedDate really a date? If so, you might be able to just subtract a few days and then format as the month.

Duane
Hook'D on Access
MS Access MVP

 

[longhair]

dhookum,
no the db field is a text field.
regards,
longhair

 

[dhookom]

Convert the date field to a date type so it isn't just pretty, it will actually have some functionality.

TRANSFORM Sum([PCS]/Val(Right(tblMasterCartons![um-code],2))) AS Expr1
SELECT tbl2009Shipping1.SKU
FROM tbl2009Shipping1 INNER JOIN tblMasterCartons ON tbl2009Shipping1.SKU = tblMasterCartons.[product]
GROUP BY tbl2009Shipping1.SKU
PIVOT Format(Month(CDate([ConfirmedDate])-4),"00")
IN ("01","02","03","04","05","06",.etc.."12")

Duane
Hook'D on Access
MS Access MVP

 

[longhair]

dhookum,
code had to be modified a bit, but you put me on the correct path.
a star for you.
thanks for the help.
regards,
longhair