Query Ref Problem

[ripon via AccessMonster.com]

Hi: I have a query1 that has 2 PIVOT columns.
SELECT sum(totalmon) AS TOT1 from tab1
Where year=2004 or year=2005
pivot year
----result
2004-------2005
1000-------200
3080-------500

In my 2nd query2, how can I ref to these 2 fields. As year changes (2005 or
2006), how can I ref to these 2 fields.
Query2: Select col1, col2 from query1;
----Query Code not working.
Thanks in advance

 

[Duane Hookom]

Consider using relative years. Your column heading expression would be
something like:
ColHead: "Y" & DateDiff("yyyy",[DateField] , Date() )
Then set the Column Headings property to:
Column Headings: "Y0","Y1"
This will display columns for this year and last year.

 

[ripon via AccessMonster.com]

Hi Duane:
Thanks a lot. I tried--
TRANSFORM Sum(tab_us_ovs.US_CAR_SD) AS SumOfUS_CAR_SD
SELECT tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
FROM tab_us_ovs, YM4TAB
WHERE (((tab_us_ovs.YEAR)=[YM4TAB].[YEAR2] Or (tab_us_ovs.YEAR)=[YM4TAB].
[YEAR2]-1))
GROUP BY tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
PIVOT "Y" & DateDiff("yyyy",YEAR2,YEAR2-1);
---
But I am having only one year column as "Y0". I am not getting "Y01"

-------------------------------------------------

Consider using relative years. Your column heading expression would be
something like:
ColHead: "Y" & DateDiff("yyyy",[DateField] , Date() )
Then set the Column Headings property to:
Column Headings: "Y0","Y1"
This will display columns for this year and last year.

Hi: I have a query1 that has 2 PIVOT columns.
SELECT sum(totalmon) AS TOT1 from tab1

[quoted text clipped - 11 lines]

----Query Code not working.
Thanks in advance

 

[Duane Hookom]

Your answer is what I would expect since you have Year2 twice in the
DateDiff() function. I'm not sure why you didn't use Date() for one of those
like suggested. I have no idea what kind of values are in the Year2 field.

Try:

TRANSFORM Sum(tab_us_ovs.US_CAR_SD) AS SumOfUS_CAR_SD
SELECT tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
FROM tab_us_ovs, YM4TAB
WHERE (((tab_us_ovs.YEAR)=[YM4TAB].[YEAR2] Or (tab_us_ovs.YEAR)=[YM4TAB].
[YEAR2]-1))
GROUP BY tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
PIVOT "Y" & DateDiff("yyyy",YEAR2,Date) IN ("Y0","Y1");


--
Duane Hookom
MS Access MVP
--

Hi Duane:
Thanks a lot. I tried--
TRANSFORM Sum(tab_us_ovs.US_CAR_SD) AS SumOfUS_CAR_SD
SELECT tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
FROM tab_us_ovs, YM4TAB
WHERE (((tab_us_ovs.YEAR)=[YM4TAB].[YEAR2] Or (tab_us_ovs.YEAR)=[YM4TAB].
[YEAR2]-1))
GROUP BY tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
PIVOT "Y" & DateDiff("yyyy",YEAR2,YEAR2-1);
---
But I am having only one year column as "Y0". I am not getting "Y01"

-------------------------------------------------

Consider using relative years. Your column heading expression would be
something like:
ColHead: "Y" & DateDiff("yyyy",[DateField] , Date() )
Then set the Column Headings property to:
Column Headings: "Y0","Y1"
This will display columns for this year and last year.

Hi: I have a query1 that has 2 PIVOT columns.
SELECT sum(totalmon) AS TOT1 from tab1

[quoted text clipped - 11 lines]

----Query Code not working.
Thanks in advance