Access 97 Crosstab query problem?

[Venkat N]

Hi,

I am having following 3 Access 97 crosstab query

TRANSFORM Nz(Count(ASTUAD.PERMNUM),0) AS CountOfPERMNUM
SELECT ASTUAD.STATUS
FROM ASTUAD
WHERE (((ASTUAD.USERCD3)="A" Or (ASTUAD.USERCD3)="N"))
GROUP BY ASTUAD.STATUS
PIVOT IIf(([GRADE]="R1" And [ELIGADA]="C"),([GRADE] & " - LD"),[GRADE]) In
("01","02","03","04","05","06","07","08","09","10","11","12","R1","R1 -
LD","R2");
----------------------

TRANSFORM Nz(Count(ASTUAD.PERMNUM),0) AS CountOfPERMNUM
SELECT ASTUAD.STATUS
FROM ASTUAD
WHERE (((ASTUAD.USERCD3)="F") AND ((ASTUAD.USERCD1)="2007"))
GROUP BY ASTUAD.STATUS
PIVOT IIf(([GRADE]="R1" And [ELIGADA]="C"),([GRADE] & " - LD"),[GRADE]) In
("01","02","03","04","05","06","07","08","09","10","11","12","R1","R1 -
LD","R2");
----------------------

TRANSFORM Nz(Count(ASTUAD.PERMNUM),0) AS CountOfPERMNUM
SELECT ASTUAD.USERCD3
FROM ASTUAD
WHERE (((ASTUAD.USERCD3)="H") AND ((ASTUAD.USERCD1)="2007"))
GROUP BY ASTUAD.USERCD3
PIVOT IIf(([GRADE]="R1" And [ELIGADA]="C"),([GRADE] & " - LD"),[GRADE]) In
("01","02","03","04","05","06","07","08","09","10","11","12","R1","R1 -
LD","R2");


From the above 3 queries, there is another crosstab query (as below) which
prints the "Total". Since there is no record for query 2 ("F" status) &
query 3 ("H" status), this "Total is not at all appearing.

INSERT INTO EnrollmentProjs ( GR1, [GR1-LD], GR2, G01, G02, G03, G04, G05,
G06, G07, G08, G09, G10, G11, G12, Type )
SELECT Nz([EnrolProjA]![R1])+Nz([EnrolProjF]![R1])+Nz([EnrolProjH]![R1]) AS
SumR1, Nz([EnrolProjA]![R1 - LD])+Nz([EnrolProjF]![R1 -
LD])+Nz([EnrolProjH]![R1 - LD]) AS [SumR1-LD],
Nz([EnrolProjA]![R2])+Nz([EnrolProjF]![R2])+Nz([EnrolProjH]![R2]) AS SumR2,
Nz([EnrolProjA]![01])+Nz([EnrolProjF]![01])+Nz([EnrolProjH]![01]) AS Sum01,
Nz([EnrolProjA]![02])+Nz([EnrolProjF]![02])+Nz([EnrolProjH]![02]) AS Sum02,
Nz([EnrolProjA]![03])+Nz([EnrolProjF]![03])+Nz([EnrolProjH]![03]) AS Sum03,
Nz([EnrolProjA]![04])+Nz([EnrolProjF]![04])+Nz([EnrolProjH]![04]) AS Sum04,
Nz([EnrolProjA]![05])+Nz([EnrolProjF]![05])+Nz([EnrolProjH]![05]) AS Sum05,
Nz([EnrolProjA]![06])+Nz([EnrolProjF]![06])+Nz([EnrolProjH]![06]) AS Sum06,
Nz([EnrolProjA]![07])+Nz([EnrolProjF]![07])+Nz([EnrolProjH]![07]) AS Sum07,
Nz([EnrolProjA]![08])+Nz([EnrolProjF]![08])+Nz([EnrolProjH]![08]) AS Sum08,
Nz([EnrolProjA]![09])+Nz([EnrolProjF]![09])+Nz([EnrolProjH]![09]) AS Sum09,
Nz([EnrolProjA]![10])+Nz([EnrolProjF]![10])+Nz([EnrolProjH]![10]) AS Sum10,
Nz([EnrolProjA]![11])+Nz([EnrolProjF]![11])+Nz([EnrolProjH]![11]) AS Sum11,
Nz([EnrolProjA]![12])+Nz([EnrolProjF]![12])+Nz([EnrolProjH]![12]) AS Sum12,
"Total" AS Expr1
FROM EnrolProjA, EnrolProjF, EnrolProjH;

Can you please help me to solve this.

Thanks in advance for your help.

Venkat