Pivot Statement - SQL - Force a column when no data

Question

Situation:

I have two Cross Tab Queries that Pivot on Text fields and return a Count for each category. Occasionally, one of more of the categories may have no data/activity during the period/date range of the query's criteria.

What I want to do is to force a column for those categories. In as much as they are text fields, I would like a missing category to return "No Data" or N/D.

If a Category is missing it causes other subsequent queries that are expecting these columns to fail and generate errors, which cause automation code to crash.

The categories for the first query are: [Type] - "CD", "OD" or "PD".

The categories for the second query are: [Campus] - "MK", "NH", 'SY" or "KG".

The Pivot Counts the number of occurances under each category based on another Text [field] [SNum] or [WCode]

Both queries run on the same tmpAtt_DateRange table. This table is created based on a user input for [StartDate] & [EndDate]. (Specific Period for Comparison)

SQL:

Q1;

TRANSFORM Count(tmp_Range_WS.Workshop) AS CountOfWorkshop

SELECT tmp_Range_WS.Wcode, tmp_Range_WS.Type, Count(tmp_Range_WS.Workshop) AS [Total Of Workshop]

FROM tmp_Range_WS

GROUP BY tmp_Range_WS.Wcode, tmp_Range_WS.Type

PIVOT tmp_Range_WS.Campus;

Q2;

TRANSFORM Count(TmpAtt_DateRange.WCode) AS CountOfWCode

SELECT TmpAtt_DateRange.SNUM, Count(TmpAtt_DateRange.Type) AS CountOfType

FROM TmpAtt_DateRange

GROUP BY TmpAtt_DateRange.SNUM

PIVOT TmpAtt_DateRange.Type;

Any suggestions in the form of Pivot statements would be appreciated. I have tried using "Switch", "Iif", "IifNull" etc. and cannot get the desired results. Switch does work in another case but the field is numeric. The same logic is not working with the text fields.

Alternatively, I am open to any other suggestions to achieve my goal.

Thank you in advance for looking at this situation.

Jim

Answer 1

Open the first query in design view.

Click in an empty part of the upper pane of the query design window.

Activate the Property Sheet.

Enter "CD","OD","PD" in the Column Headings property.

Save the query.

Do the same for the other query, with "MK","NH",'SY","KG" in the Column Headings property.

The SQL for this is:

TRANSFORM ...

...

PIVOT tmp_Range_WS.Campus In("CD","OD","PD")

and

TRANSFORM ...

...

PIVOT TmpAtt_DateRange.Type In ("MK","NH",'SY","KG")

Answer 2

PIVOT tmp_Range_WS.Campus IN("CD", "OD", "PD");

Answer 3

Use 'IN' in your pivot like this --

PIVOT TmpAtt_DateRange.Type IN("CD", "OD", "PD");

Just make sure the what is typed in the IN exactly matches what the query will produce.  The results will be in the same order as is entered in the IN statement.

Answer 4

Count will give a number as answer so having "1 Credit" in the IN will not match.   If you want the text with number then edit the Transform to this --

TRANSFORM Count(TmpAtt_DateRange.Type) & " Credits" AS CountOfType

SELECT TmpAtt_DateRange.WCode, TmpAtt_DateRange.Type, Count(TmpAtt_DateRange.Type) AS [Total Of Type]

FROM TmpAtt_DateRange

GROUP BY TmpAtt_DateRange.WCode, TmpAtt_DateRange.Type

PIVOT TmpAtt_DateRange.Credits In ("1 Credit","2 Credits","3 Credits","4 or more Credits");

In the above a 1 will give you "1 Credits" (note it is plural.    You should be able to use an IIF statement like this --

TRANSFORM IIF(Count(TmpAtt_DateRange.Type)= 1, Count(TmpAtt_DateRange.Type) & " Credit", Count(TmpAtt_DateRange.Type) & " Credits")  AS CountOfType

Answer 5

Thank you Karl,

It worked perfectly. I tried to apply the same technique to a similar query but it did not work. I get a "Data type mismatch in Criteria expression" message.

Is it that this method does not work with numeric fields.

The Pivot field is [Credits] - numeric.

Each student earns variable credits for attending workshops. We want to report on Students [SNum] who have earned 1 Credit, 2 Credits, 3 Credits and 4 or more Credits. I have used the Switch command in this query and can get the correct results. I like your method if it works here because it has one less step.

Here is the SQL I tried:

TRANSFORM Count(TmpAtt_DateRange.Type) AS CountOfType

SELECT TmpAtt_DateRange.WCode, TmpAtt_DateRange.Type, Count(TmpAtt_DateRange.Type) AS [Total Of Type]

FROM TmpAtt_DateRange

GROUP BY TmpAtt_DateRange.WCode, TmpAtt_DateRange.Type

PIVOT TmpAtt_DateRange.Credits In ("1 Credit","2 Credits","3 Credits","4 or more Credits");

I look forward to your response.

Thank you.

Jim