Crosstab Query Criteria on the PIVOT field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Haven't had much success on my own so I hope this makes sense...
The Row Heading is a Part Number and the Column Heading is a Type. The Type
column is my PIVOT column. Each Part can be Type "1" or Type "7".
To simplify, this is the current example of the query.
Part Type1 Type7 (Column Headings)
Part A 2 1 (Part A shows 2 Type 1 and 1 Type 7)
Part B 1 (Part B shows 1 Type 1 and NO Type 7)
Part C 1 2 (Part C shows 1 Type 1 and 2 Type 7)
Problem. I only want to show Parts that have records for both Type 1 and
Type 7. Part B does not have records for each Type, only Type 1. I'm trying
to compare Parts with both Types so this is a useless record. I want to
filter it out.

Since the Type is a single field that the crosstab divides into two columns,
I am not sure how to write a WHERE statement.
 
Dear DB:

Likely you need a WHERE EXISTS () AND EXISTS () with subqueries inside the
parens. These subqueries would correlate to the Part and choose the
specific Thpes 1 and 7 respectively.

SELECT *
FROM YourTable T
WHERE EXISTS (SELECT *
FROM YourTable T1
WHERE T1.Part = T.Part
AND T1.Type = 1)
AND EXISTS (SELECT *
FROM YourTable T1
WHERE T1.Part = T.Part
AND T1.Type = 7)

That's a general outline. Substitute the actual name of your table and
check the details of the column names involved.

Tom Ellison
 
Thanks for the assistance I really appreciate it!!
I gave it a try and it did run, but the Crosstab query gave me the same
results. So I thought I'd try running the subquery WHERE EXISTS first then
build the Crosstab Query off of that. Still get the single-Type records. I
know you're on the right track though....I'm just missing something.

I start with a query based on three linked tables. It gets the necessary
bulk data I need.
The query is called [qryRouting*7].
The Part column is called [Part No].
The Type column is called [Routing Alternative].

Maybe you can see something wrong? This is the Crosstab SQL.
TRANSFORM CLng(Nz(Count([qryRouting*7].[Routing Alternative]),0)) AS
[CountOfRouting Alternative]
SELECT [qryRouting*7].[Part No]
FROM [qryRouting*7]
WHERE EXISTS (SELECT [qryRouting*7].[Part No] FROM [qryRouting*7] WHERE
[qryRouting*7].[Part No] = [qryRouting*7].[Part No] AND
[qryRouting*7].[Routing Alternative] = "1")
AND EXISTS (SELECT [qryRouting*7].[Part No] FROM [qryRouting*7] WHERE
[qryRouting*7].[Part No] = [qryRouting*7].[Part No] AND
[qryRouting*7].[Routing Alternative] = "7")
GROUP BY [qryRouting*7].[Part No]
PIVOT [qryRouting*7].[Routing Alternative];

(The TRANSFORM "CLng(Nz(Count....." converts null values into zeroes so I
can calculate on them later. Don't think that is the cause?)

Interesting enough: If I remove the WHERE EXISTS () AND () lines and run it,
the data it returns is the same as above. Like the WHERE EXISTS part isn't
considered. Know if the subquery portion runs first, second??

Both statements give me 29 records, when the data should give me 10.
(There are 19 single-type records in the mix)
 
Dear DB:

You have completely omitted the aliases, which are not optional in a
correlated subquery. Before making changes, please try this exactly as I
have posted it:

SELECT *
FROM [qryRouting*7] Q
WHERE EXISTS (SELECT Q1.*
FROM [qryRouting*7] Q1
WHERE Q1.[Part No] = Q.[Part No]
AND Q1.[Routing Alternative] = "1")
AND EXISTS (SELECT Q1.*
FROM [qryRouting*7] Q1
WHERE Q1.[Part No] = Q.[Part No]
AND Q1.[Routing Alternative] = "7")

While it is not impossibl4e I have made a mistake along the way, the
alterations you made do not appear to me to have any chance of working.

You can change this into a crosstab after we have it working and confirmed
that it filters properly.

Is [Routing Alternative] actually a string? I had thought it would be
numeric, as the values you mentioned for it are 1 and 7. Not a big deal,
but it might be a faster query without converting numeric values to strings,
althouth the effect may be quite slight. Still, we learn to hate waste!

Tom Ellison


DBenedict said:
Thanks for the assistance I really appreciate it!!
I gave it a try and it did run, but the Crosstab query gave me the same
results. So I thought I'd try running the subquery WHERE EXISTS first then
build the Crosstab Query off of that. Still get the single-Type records. I
know you're on the right track though....I'm just missing something.

I start with a query based on three linked tables. It gets the necessary
bulk data I need.
The query is called [qryRouting*7].
The Part column is called [Part No].
The Type column is called [Routing Alternative].

Maybe you can see something wrong? This is the Crosstab SQL.
TRANSFORM CLng(Nz(Count([qryRouting*7].[Routing Alternative]),0)) AS
[CountOfRouting Alternative]
SELECT [qryRouting*7].[Part No]
FROM [qryRouting*7]
WHERE EXISTS (SELECT [qryRouting*7].[Part No] FROM [qryRouting*7] WHERE
[qryRouting*7].[Part No] = [qryRouting*7].[Part No] AND
[qryRouting*7].[Routing Alternative] = "1")
AND EXISTS (SELECT [qryRouting*7].[Part No] FROM [qryRouting*7] WHERE
[qryRouting*7].[Part No] = [qryRouting*7].[Part No] AND
[qryRouting*7].[Routing Alternative] = "7")
GROUP BY [qryRouting*7].[Part No]
PIVOT [qryRouting*7].[Routing Alternative];

(The TRANSFORM "CLng(Nz(Count....." converts null values into zeroes so I
can calculate on them later. Don't think that is the cause?)

Interesting enough: If I remove the WHERE EXISTS () AND () lines and run
it,
the data it returns is the same as above. Like the WHERE EXISTS part
isn't
considered. Know if the subquery portion runs first, second??

Both statements give me 29 records, when the data should give me 10.
(There are 19 single-type records in the mix)

Tom Ellison said:
Dear DB:

Likely you need a WHERE EXISTS () AND EXISTS () with subqueries inside
the
parens. These subqueries would correlate to the Part and choose the
specific Thpes 1 and 7 respectively.

SELECT *
FROM YourTable T
WHERE EXISTS (SELECT *
FROM YourTable T1
WHERE T1.Part = T.Part
AND T1.Type = 1)
AND EXISTS (SELECT *
FROM YourTable T1
WHERE T1.Part = T.Part
AND T1.Type = 7)

That's a general outline. Substitute the actual name of your table and
check the details of the column names involved.

Tom Ellison
Click to expand...
Click to expand...
 
Back
Top