Difficult SQL Question Regarding Matrices after Filtration

[JohnEMcCarthy]

I've hit a big problem and I'm not certain whether or not there is a solution.

I am building an application that allows the user to filter on various criteria, and then build a matrix around a field, containing only the records existing after filtration.

The vertical and horizontal field names, however, must remain unchanged after filtration -- only the data within can be altered. Ugh.

For example, here is my base SQL code:

TRANSFORM (IIf(Avg([Times])=0,Null,Avg([Times]))) AS AvgOfTimes
SELECT Step2.Department AS Expr1
FROM Step2
GROUP BY Step2.Department
PIVOT Step2.Matched;

When I run this, I get a square matrix with 45 fields appearing in both the vertical and horizontal.
The fields in the vertical commensurate with those in the horizontal:

X AA BB CC
A
B
C

I am then using a VBA click event to dynamically populate a WHERE clause in to my original SQL statement.

For example, if one clicks the value "7" from a combo box, the SQL will be modified as:

TRANSFORM (IIf(Avg([Times])=0,Null,Avg([Times]))) AS AvgOfTimes
SELECT Step2.Department AS Expr1
FROM Step2
WHERE [Times] = 7
GROUP BY Step2.Department
PIVOT Step2.Matched;

Expectedly, after I do this, the fields in my vertical and horizontal no longer sync, as it only displays those fields that contain a value. For example,

X AA BB DD
A
C

I need to retain that original structure, however, while using some filtration mechanism (a WHERE clause?) to only include chosen records.

Basically, I need the matrix to remain a square with the same # of fields in the vertical that exist in the horizontal -- these two things must not change. Within that fixed "structure," however, the data that populates changes depending on inputted criteria.

This is awfully difficult to articulate in under 10,000 words. I hope someone can understand me.

I've hit a serious impasse; any help would be appreciated beyond words. Thank you all.

 

[PHV]

Hopefully Step2 has a primary key, say PK:
TRANSFORM Avg(B.[Times]) AS AvgOfTimes
SELECT A.Department
FROM Step2 A LEFT JOIN (
SELECT * FROM Step2 WHERE [Times] = 7
) B ON A.PK = B.PK
GROUP BY A.Department
PIVOT A.Matched

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886