REAL QUERY PROBLEM

[abeed]

HI THERE:
I have a CROSSTAB query.
Query1:
TRANSFORM Sum(tab_us_ovs.US_CAR_TOT) AS SumOfUS_CAR_TOT
SELECT tab_us_ovs.PROVCODE, tab_us_ovs.MONTH, Sum(tab_us_ovs.US_CAR_TOT) AS [Total Of US_CAR_TOT]
FROM tab_us_ovs
WHERE YEAR=1992 OR YEAR=1993
GROUP BY tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
PIVOT tab_us_ovs.YEAR;

PROVCODE---MONTH---1992---1993
1000------------1---------100----200
2090------------2---------300----400

Query 2:
Can I ref to the Last 2 fields of this CROSSTAB query in my 2nd query.
After EVERY 3 months, we have to change the year like: YEAR=2000 OR YEAR=2001
I was trying
Select Query1.column(3) from Query1;
----Not working. What is the correct query expression.
Thanks in advance.

 

[Norie]

How are you determining the years?

 

[abeed]

Hi Norie/Anyone:
Thanks a lot. I have a 1 row table(YM4TAB) that holds the year. This year changes as required by the client. Say this month, the year in YM4TAb is 2005. The data are then searched from tab_us_res table for year 2005 and 2004. Next month YM4TAB may have YEAR=2000 and client may want data for 2000 and 1999. I was using query as:
----------------------------------
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.YEAR Or (tab_us_ovs.YEAR)=YM4TAB.YEAR-1))
GROUP BY tab_us_ovs.PROVCODE, tab_us_ovs.MONTH
PIVOT tab_us_ovs.YEAR;
----------------------------------------
PROVCODE---MONTH---2004---2005
1000------------1---------100----200
2090------------2---------300----400
----------------------------
So when I am using a 2nd query to get the data under the 2 years(say 2004 & 2005), i can not do it. I can not make a generalized column name for 3rd & 4th column.
So how can I ref to these 3rd & 4th column. Like:
Select query1.[2004] from query1;--This is fine if hard coded. But as I said, year get changes. How can I dio it?
Thanks anyone in advance.