Crosstab query - odd field

  • Thread starter Thread starter SusanV
  • Start date Start date
S

SusanV

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
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Back
Top