Pivot with "Iif" statement in SQL

[Jim Dudley]

I have a query that worked using the "Switch" option in the Pivot Statement.
I needed to ensure that if in some instances there was no data available in a given Selection of records for one of the fields the Pivot would create, that the query not crash but return a null or "0" value in that field.

To accomplish that, I have added an "Iif" statement to the query.

Issue:

I get an error now that their is a data mismatch in the query criteria.

SQL:

TRANSFORM Count(tmp_PA_Att_Range.Type) AS CountOfType
SELECT tmp_PA_Att_Range.WCode, tmp_PA_Att_Range.Type, Count(tmp_PA_Att_Range.Type) AS [Total Of Type]
FROM tmp_PA_Att_Range
GROUP BY tmp_PA_Att_Range.WCode, tmp_PA_Att_Range.Type
PIVOT Switch(IIf([Credits]=1,"1 Credit","0"),IIf([Credits]=2,"2 Credits","0"),IIf([Credits]=3,"3 Credits","0"),IIf([Credits]>=4,"4 or more Credits","0"));

Access 2010, Windows 7.

Skill Level - SQL beginner

Any suggestions or sample query code that works for this would be appreciated.

Thank you.

Jim

 

[RainLover]

PIVOT Switch(IIf([Credits]=1,"1 Credit","0"),IIf([Credits]=2,"2 Credits","0"),IIf([Credits]=3,"3 Credits","0"),IIf([Credits]>=4,"4 or more Credits","0"));

What is the Data Type of "Credits"

You have two possible results in your IIf Statement, both of which are Text.
But "Credits = 1" is a Number not text.

 

[Jim Dudley]

[Credits] is a number field.

Background:

Students earn credits for attending workshops/seminars. When we summarize the [Credits], we report on the number of students with one credit, two credits, 3 credits and 4 or more credits. If in a given period for reporting there are no students say with more than 3 credits, the cross tab query does not produce a column for 4 or more credits. This causes the code that runs subordiate queries and exports to excel to go into error and stop.
What I am attempting to accomplish is, if there is a null or 0 count/sum in any of the subsequent queries that it appear as null or 0 so that the subordinate queries continue to function.
I see your point about numeric value of [Credits] and I am requesting an alternate text answer.
How else would you state it?
I have attached a final spreadsheet of the results of the cross tabs and summary queries. The query results are exported to Excel and assembled into this one page for distribution. Access is not widely available but Excel is globally available to the recipients.

There are 13 Cross Tab queries used to make up the results on the Excel Sheet. These queries work on 3 Primary tables and 3 temporary tables that are created to assembled the records for the specified period.

SQL: (This works correctly providing there is data in all of the categories/columns)
Query one
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 Switch([Credits]=1,"1 Credit",[Credits]=2,"2 Credits",[Credits]=3,"3 Credits",[Credits]>=4,"4 or more Credits");

Query two
SELECT Sum(QX_PA_tmpRange_WS_Cr.[1 Credit]) AS [SumOf1 Credit], Sum(QX_PA_tmpRange_WS_Cr.[2 Credits]) AS [SumOf2 Credits], Sum(QX_PA_tmpRange_WS_Cr.[3 Credits]) AS [SumOf3 Credits], Sum(QX_PA_tmpRange_WS_Cr.[4 or more Credits]) AS [SumOf4 or more Credits], Sum(QX_PA_tmpRange_WS_Cr.[Total Of Type]) AS [SumOfTotal Of Type]
FROM QX_PA_tmpRange_WS_Cr;

Thank you for taking the time to look at this situation. Your interest is greatly appreciated.

Jim

 

[RainLover]

Very nice presentation in Excel.

You have given some very good information except for what exactly is failing.

Did you create this in the Query Grid. If you did can you go back and delete one thing at a time to isolate the error.

 

[Jim Dudley]

Actually, each section in the Excel sheet is linked to a specific Excel sheet that was exported from a form, which was created from a summary query. In Access I have a form with 12 tabs/sub-forms representing the results of the queries which supply the data. Each of these sub-forms is exported to Excel as an individual file and then the Spreadsheet I sent you is linked to these individual Excel files.

Access, upon request executes VBA code which in turn automatically exports the sub-forms to the appropriate Excel file. When the exports are finished, the code lodes the sheet I sent you and updates it with the latest data. This Excel summary is then distributed to the appropriate staff at 5 different College campuses. The reason we use Excel is that it is available to everyone and Access has very restricted availability.

We gather data from the various sources via Excel as well. The Input sheets/templates (3 types - Workshops, Students & Attendance) are filled in at the various Campuses and forwarded to a Central Office where they are imported into Access. The import function is automated with VBA.

This morning I received a response which seems to have solved the problem. I have just tested it on the one type of Cross Tab and it seems to solve the problem of missing data in some categories.

I appreciate your interest and help with this situation.

Thank you.

Regards,

Jim

 

[cpberg1]

This morning I received a response which seems to have solved the problem. I have just tested it on the one type of Cross Tab and it seems to solve the problem of missing data in some categories.

Jim

Hey Jim,

How about posting the solution to help others who in the future may experience the same type of issue? Glad you got it straightened out!

 

[Jim Dudley]

Here is the alternate solution that appears to resolve the original posted question.

I tested it one incident and it achieved the desired results.

Solution:

[FONT=&quot]Rather than in the crosstab query, I'd suggest calling the Switch function in a query on which the crosstab query is based, e.g

Switch(Nz(Credits,0) =0,"Zero credits",Credits=1,"1 Credit",Credits=2,"2 Credits",Credits=3,"3 Credits",Credits>=4,"4 or more Credits") AS CreditsString

Then for the crosstab query's PIVOT clause:

PIVOT CreditsString IN("Zero credits","1 Credit","2 Credits","3 Credits","4 or more Credits")

Hope this is helpful to others.[/FONT]
[FONT=&quot]Jim
[/FONT]

 

[RainLover]

Thanks Jim.