Crosstab query - odd field

Hi all,

I have 2 stored Crosstab queries which are basically the same in structure
but which pull data from different sources:

qryXtab_tblTemp:

TRANSFORM Last(tblTemp.Freq) AS LastOfFreq
SELECT tblTemp.Equipment AS Expr1, tblTemp.HSC AS Expr2, tblTemp.Match AS
Expr3
FROM tblTemp
WHERE ((([tblTemp].[OpStat])="FOS"))
GROUP BY tblTemp.Equipment, tblTemp.HSC, tblTemp.Match
ORDER BY tblTemp.HSC
PIVOT tblTemp.MCode;



qryXtab_Prop:

TRANSFORM Last(tblPTemp.FREQ) AS LastOfFREQ
SELECT tblPTemp.Equipment, tblPTemp.HSC, tblPTemp.Match,
tblPTemp.CalculatedLevelCode, tblPTemp.MaintenancePlanId
FROM tblPTemp
GROUP BY tblPTemp.Equipment, tblPTemp.HSC, tblPTemp.Match,
tblPTemp.CalculatedLevelCode, tblPTemp.MaintenancePlanId
ORDER BY tblPTemp.HSC
PIVOT tblPTemp.MCODE;

The second query, qryXtab_Prop outputs an additional field labeled (which
never has any data, all records are null for this field). The thing is, I
don't understand where it's coming from. I've rebuilt the query several
times, and also rebuilt the first query using the exact same methods, and
the first query never outputs this field, but the second always does.

It's not important functionally, as I can omit this weird field when
working further with the data, but I'm very curious as to why it's there and
what significance it may have?

Thanks,

Susan

You have a record in tblPTemp with no value in the MCODE field. Delete the
record or add a where clause to filter out records
WHERE [tblTemp].[OpStat]="FOS" And MCODE Is Not Null

--
Duane Hookom
MS Access MVP
--

"SusanV" wrote in message
...
Hi all,

I have 2 stored Crosstab queries which are basically the same in structure
but which pull data from different sources:

qryXtab_tblTemp:

TRANSFORM Last(tblTemp.Freq) AS LastOfFreq
SELECT tblTemp.Equipment AS Expr1, tblTemp.HSC AS Expr2, tblTemp.Match AS
Expr3
FROM tblTemp
WHERE ((([tblTemp].[OpStat])="FOS"))
GROUP BY tblTemp.Equipment, tblTemp.HSC, tblTemp.Match
ORDER BY tblTemp.HSC
PIVOT tblTemp.MCode;



qryXtab_Prop:

TRANSFORM Last(tblPTemp.FREQ) AS LastOfFREQ
SELECT tblPTemp.Equipment, tblPTemp.HSC, tblPTemp.Match,
tblPTemp.CalculatedLevelCode, tblPTemp.MaintenancePlanId
FROM tblPTemp
GROUP BY tblPTemp.Equipment, tblPTemp.HSC, tblPTemp.Match,
tblPTemp.CalculatedLevelCode, tblPTemp.MaintenancePlanId
ORDER BY tblPTemp.HSC
PIVOT tblPTemp.MCODE;

The second query, qryXtab_Prop outputs an additional field labeled
(which never has any data, all records are null for this field). The thing
is, I don't understand where it's coming from. I've rebuilt the query
several times, and also rebuilt the first query using the exact same
methods, and the first query never outputs this field, but the second
always does.

It's not important functionally, as I can omit this weird field when
working further with the data, but I'm very curious as to why it's there
and what significance it may have?

Thanks,

Susan

Good morning Duane - and thanks for responding so quickly!

Crosstab queries are still a novelty here, and your explanation makes
PERFECT sense! Yes, I have several records with no MCode values (expected to
be so, as well) - and I can't drop those records - I need them to populate
the RowHeading fields. So this is working perfectly - and I can simply
delete the field later via DAO...

Beautiful - thank you SO much!!! ;-D

Susan


"Duane Hookom" wrote in message
...
You have a record in tblPTemp with no value in the MCODE field. Delete the
record or add a where clause to filter out records
WHERE [tblTemp].[OpStat]="FOS" And MCODE Is Not Null

--
Duane Hookom
MS Access MVP
--

"SusanV" wrote in message
...
Hi all,

I have 2 stored Crosstab queries which are basically the same in
structure but which pull data from different sources:

qryXtab_tblTemp:

TRANSFORM Last(tblTemp.Freq) AS LastOfFreq
SELECT tblTemp.Equipment AS Expr1, tblTemp.HSC AS Expr2, tblTemp.Match AS
Expr3
FROM tblTemp
WHERE ((([tblTemp].[OpStat])="FOS"))
GROUP BY tblTemp.Equipment, tblTemp.HSC, tblTemp.Match
ORDER BY tblTemp.HSC
PIVOT tblTemp.MCode;



qryXtab_Prop:

TRANSFORM Last(tblPTemp.FREQ) AS LastOfFREQ
SELECT tblPTemp.Equipment, tblPTemp.HSC, tblPTemp.Match,
tblPTemp.CalculatedLevelCode, tblPTemp.MaintenancePlanId
FROM tblPTemp
GROUP BY tblPTemp.Equipment, tblPTemp.HSC, tblPTemp.Match,
tblPTemp.CalculatedLevelCode, tblPTemp.MaintenancePlanId
ORDER BY tblPTemp.HSC
PIVOT tblPTemp.MCODE;

The second query, qryXtab_Prop outputs an additional field labeled
(which never has any data, all records are null for this field). The
thing is, I don't understand where it's coming from. I've rebuilt the
query several times, and also rebuilt the first query using the exact
same methods, and the first query never outputs this field, but the
second always does.

It's not important functionally, as I can omit this weird field when
working further with the data, but I'm very curious as to why it's there
and what significance it may have?

Thanks,

Susan