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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>