Syntax error in FROM clause

[cogivina]

Hi,
I have two test tables. One is, SALE, contained State, Yr, Month, Paid. Some data might be missing or null.

SALE:
State Yr Mos Paid
AK 2000 1 150
AK 2002 3 250
CT 2008 1 444
...

The other table, Table0, contains all the states, Yrs, Months and 0.
Table0:
State Yr Mos Paid
AK 2000 1 0
AK 2001 1 0
AK 2002 1 0
CT 2000 1 0
CT 2001 1 0
CT 2002 1 0


My first SALE crosstab query, Sale_CrossTab is:

TRANSFORM Sum(Paid) AS SumOfPaid
SELECT State, Yr
FROM SALE
WHERE State ="AK" AND Mos =1
GROUP BY State, Yr
PIVOT Mos;

It returns:
State Yr 1
AK 2000 150

I joined this crosstab query with the Table0 so the result will show all the values:

State Yr Mos 1_CD
AK 2000 1 150
AK 2001 1 0
AK 2002 1 0

SELECT Table0.State, Table0.Yr, Table0.Mos, IIf([1] Is Null,0,[1]) AS 1_CD
FROM Sale_Crosstab RIGHT JOIN Table0 ON (Sale_Crosstab.Yr = Table0.Yr) AND (Sale_Crosstab.State = Table0.State)
WHERE Table0.State ="ak"
---------------------

I try to combine these two queries into only query and I get the syntax error in FROM clause.

SELECT B.State, B.Yr, B.Mos, IIf([1] Is Null,0,[1]) AS 1_CD
FROM (TRANSFORM Sum(SALE.Paid) AS SumOfPaid
SELECT SALE.State, SALE.Yr
FROM SALE
WHERE SALE.State ="AK" AND SALE.Mos =1
GROUP BY SALE.State, SALE.Yr
PIVOT SALE.Mos) A
RIGHT JOIN Table0 B ON (A.State = B.State) AND (A.Yr = B.Yr)
WHERE B.State="AK"

Please help to point out the error.
Thanks.

 

[SkipVought]

Hi,

What application? What Database?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cogivina]

It is in access database. I try to use this code in Excel VBA.

 

[SkipVought]

You had better be using ADO, since MS Query only allows ONE outer join...

TRANSFORM Sum(Paid)
SELECT State, Yr
FROM (SELECT  A.State, A.Yr, A.Mos, B.Paid
FROM Table0 A LEFT JOIN SALE B   ON (A.Mos = B.Mos)  AND (A.Yr = B.Yr)  AND (A.State = B.State)
) 
Group By State, Yr
Pivot Mos

My Results (slightly changed source data)...
[tt]
State Yr 1 2 3
AK 2000 150
AK 2001
AK 2002 250
CT 2000
CT 2001
CT 2002 444
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}