Values in crosstab represent change -- how to get query to return"current value" for each month?

[Rachel Garrett]

So I've got this nifty crosstab query that shows me my data broken
down over time. The rows are [Assessment Type] and [Question]; the
columns are from the [Assessment Date] field; the values are from
[Score]. So it looks like this:

Question 12/1/07 1/1/08 2/1/08
1.1 3 4
1.2 2 3
1.3 4 5 4

I think you can see where this is heading. I want to be able to "fill
in the blanks" so that for any month, I can use another query to pull
what the *current* score was for each question. For example, I want to
say that in January, Question 1.1 was scoring at a 3.

On a related note...how do I group column headings on a crosstab, so
that 3/1/08, 3/23/08, 3/18/08 would all appear in "3/08"? I can do
this in PivotTables, but what is the syntax in a crosstab query? (Is
it better etiquette here to post that as a separate question?)

Thanks,
Rachel Garrett

 

[Michel Walsh]

For the second part, PIVOT on an expression which will subtract the day part
from the date:


PIVOT DateAdd("d", 1-DatePart("d", yourDate), yourDate)


which, basically, collapses any date (of a given month and year) to the
first day of that month and year.

I assumed your dates don't have a time part (ie, they always have, but I
mean the time part is always 00:00:00 )




Vanderghast, Access MVP