Adding another Totals Column in Crosstab query

[Jomark]

I have a crosstab query that gives me information for a specific annual
period using a QBF parameter and has a total column summing the row data.

What I would like to know is it possible to add another total column that
shows a previous annual period

Name Total Of Opened APR MAY JUN JUL
BM 1
GK 3
JL 14
JM 55 2 3 3 1
JS 6
MC 2
MF 38 1
TN 6

 

[Allen Browne]

So you want columns like this:
This Apr | Last Apr | This May | Last May | This Jun...

Let's assume:
- you are using a calendar year
- you have a date column namd 'd',
- the query requests the year number as a parameter named WotYear (integer)

The Criteria under your date field will be something like this:

= DateSerial([WotYear]-1,1,1) And < DateSerial([WotYear]+1,1,1)

You could now type an expression like this into the Field row:
IIf(Year([d])=[WotYear], "This ", "Last ") & Format([d], "mmm")
Use that expression as the Column Heading.

If your year starts with April, instead of just [d] you will need to
subtract 3 months to get the year right:
DateAdd("m", -3 [d])

There are other ways to get multiple sets of values into the crosstab, but
they would be more convoluted. Details:
http://allenbrowne.com/ser-67.html#MultipleValues

 

[Jomark]

No.
what I am looking for is a Total of Opened column only for say the previous
year

e.g. Total last year|Total This Year| Apr|May|Jun| etc where the months are
the This Year months.
I am comfortable with the criteria although your suggestion is something to
think about using the Iff statement.

So you want columns like this:
This Apr | Last Apr | This May | Last May | This Jun...

Let's assume:
- you are using a calendar year
- you have a date column namd 'd',
- the query requests the year number as a parameter named WotYear (integer)

The Criteria under your date field will be something like this:

= DateSerial([WotYear]-1,1,1) And < DateSerial([WotYear]+1,1,1)

You could now type an expression like this into the Field row:
IIf(Year([d])=[WotYear], "This ", "Last ") & Format([d], "mmm")
Use that expression as the Column Heading.

If your year starts with April, instead of just [d] you will need to
subtract 3 months to get the year right:
DateAdd("m", -3 [d])

There are other ways to get multiple sets of values into the crosstab, but
they would be more convoluted. Details:
http://allenbrowne.com/ser-67.html#MultipleValues

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a crosstab query that gives me information for a specific annual
period using a QBF parameter and has a total column summing the row data.

What I would like to know is it possible to add another total column that
shows a previous annual period

Name Total Of Opened APR MAY JUN JUL
BM 1
GK 3
JL 14
JM 55 2 3 3 1
JS 6
MC 2
MF 38 1
TN 6

 

[John Spencer]

You should be able to use the DSUM function to get the desired result.

TRANSFORM Sum(Amount)
SELECT [Name]
, DSum("Amount","YourTable","[Name]=""" & [Name] & " AND TimePeriod Between
#2007/1/1# AND #2007/12/31#") as PriorYear
,Sum(Amount) as Total
FROM YourTable
GROUP BY [Name]
,, DSum("Amount","YourTable","[Name]=""" & [Name] & " AND TimePeriod Between
#2007/1/1# AND #2007/12/31#")
Pivot Month(TimePeriod)

This will be slow.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County