Counting Issue

[Mike]

I have an issue and I'll try to simplify it with this example. I have a
table (tblSafety) that has 3 Columns - Record ID, Swings, Slides. The Swings
and Slides fields are combo boxes with the option to select Yes, No, At Risk,
N/A. I want to write a query that gives me the total number of times "No" or
"At Risk" occurs in the table and the total combined count of Swings + Slides
fields.

So I would end up with the results of 6 times either "No" or "At Risk"
occurred out of a total of 20 occurrences.

I'm lost as to how to write this query. I'm pretty new to SQL and VBA, so
please speak slowly.

 

[Duane Hookom]

I would change the table structure so Swings and Slides were values entered
into a field rather than used as field names. A swing record would contain
"Swings" and the rating value. You could then count the number of equipment
rating by type.

I kinda expect this isn't about swings and slides so I can't go much further
in suggesting a schema.

 

[KARL DEWEY]

Try this --
SELECT Sum(IIF([Swings] = "No" OR [Swings] = "At Risk", 1, 0) + IIF([Slides]
= "No" OR [Slides] = "At Risk", 1, 0)) AS [No or At Risk], Sum(IIF([Swings] =
"N/A", 0, 1) + IIF([Slides] = "N/A", 0, 1)) AS [Total Swings and Slides)
FROM tblSafety

 

[Mike]

This worked perfectly, thanks so much!

Try this --
SELECT Sum(IIF([Swings] = "No" OR [Swings] = "At Risk", 1, 0) + IIF([Slides]
= "No" OR [Slides] = "At Risk", 1, 0)) AS [No or At Risk], Sum(IIF([Swings] =
"N/A", 0, 1) + IIF([Slides] = "N/A", 0, 1)) AS [Total Swings and Slides)
FROM tblSafety

I have an issue and I'll try to simplify it with this example. I have a
table (tblSafety) that has 3 Columns - Record ID, Swings, Slides. The Swings
and Slides fields are combo boxes with the option to select Yes, No, At Risk,
N/A. I want to write a query that gives me the total number of times "No" or
"At Risk" occurs in the table and the total combined count of Swings + Slides
fields.

So I would end up with the results of 6 times either "No" or "At Risk"
occurred out of a total of 20 occurrences.

I'm lost as to how to write this query. I'm pretty new to SQL and VBA, so
please speak slowly.