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

RIGHT JOIN is my crostab query requirement.

But I'm 100% sure, there are no blank records in either tables.

If i take a LEFT or INNER join, there are no blank records.

But as this thread says : "Force columns when no data available". Therefore i need a RIGHT JOIN.

May u help me in finding out, y this balnk record appears.

Answer 5

The row fields of your query are from the right hand side of a LEFT JOIN, so they can be blank if a record from TblDates doesn't have a matching record in 01DBase.

Can you change LEFT JOIN to INNER JOIN?