Crosstab has extra field with <>?? 1

[air0jmb]

I'm hoping someone can tell me why my crosstab query has an extra column labeled "<>"?

Here's the sql, BTW how do I go about putting it in the pretty little box all you code masters use?

TRANSFORM Count(qryDelaysAndCanx.ExType) AS CountOfExType
SELECT rqryMonthsAndTails.CalMonth, rqryMonthsAndTails.Tail, Count(qryDelaysAndCanx.Fleet) AS Exceptions
FROM rqryMonthsAndTails LEFT JOIN qryDelaysAndCanx ON (rqryMonthsAndTails.CalMonth = qryDelaysAndCanx.ExMonth) AND (rqryMonthsAndTails.Tail = qryDelaysAndCanx.Tail)
GROUP BY rqryMonthsAndTails.CalMonth, rqryMonthsAndTails.Tail
PIVOT qryDelaysAndCanx.ExType;

Thanks for any assistance!!

Mike

 

[lespaul]

BTW how do I go about putting it in the pretty little box all you code masters use?

check out the TGML process link below. Basically its:

[ignore]

put your code here

[/ignore]

Don't know how/why the <> is in your query though!



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Probably for the null values of qryDelaysAndCanx.ExType due the left join.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

PH, how would the left join add a column to the query output? That's what I inferred was occurring:

CalMonth Tail Exceptions <> (field headings in query results)

 

[PHV]

Leslie, if the left join returns null values for qryDelaysAndCanx.ExType (the pivot field), you can't have a null as oolumn header and thus the <> I guess.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[air0jmb]

That sounds right, there is null values. The left side is a cartesian product that gives me all tails and all months. And, not every tail has a record for each month in qryDelaysAndCanx.

Is there a better approach or should I just run with this since, if I ignore the <> field, I get the results I need?

Thanks to both of you for your input!

BTW, now that I've learned how to insert text in the pretty little boxes, how do I enter table date that has the columns aligned? Anytime I try to type and post table data, it always shows up all screwed up.

Mike

 

[lespaul]

use the [ignore][tt][/tt][/ignore] tags. There's a whole list right below the posting area in the Process TGML link below (right above the Submit Post button).

 

[dhookom]

Is there a better approach or should I just run with this since, if I ignore the <> field, I get the results I need?"
This all depends on your requirements. If you don't want to see this column then filter it out. If you want something more descriptive than <>, try:

TRANSFORM Count(qryDelaysAndCanx.ExType) AS CountOfExType
SELECT rqryMonthsAndTails.CalMonth, rqryMonthsAndTails.Tail, Count(qryDelaysAndCanx.Fleet) AS Exceptions
FROM rqryMonthsAndTails LEFT JOIN qryDelaysAndCanx ON (rqryMonthsAndTails.CalMonth = qryDelaysAndCanx.ExMonth) AND (rqryMonthsAndTails.Tail = qryDelaysAndCanx.Tail)
GROUP BY rqryMonthsAndTails.CalMonth, rqryMonthsAndTails.Tail
PIVOT Nz(qryDelaysAndCanx.ExType,"No Ex Type");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]