Count Records In Multiple Columns (Based on a Criteria)

[nick404]

Hi all,



I have been searching around for a while now and have not been able to find any information on this.

I have a query which pulls data out of a table from the previous month. The data basically consists of 20 different "tasks" and the values are either "Good" or "Not Good". I want my query to count how many "Not Good" entries there are for each task, so that I may display the top 3 later on. I have been unsuccessful so far.

So for example let's say this is the data that is returned by my query that pulls all data from the previous month:

Date	Task1	Task2	Task3
07/03/16	Good	Not Good	Good
07/12/16	Not Good	Good	Good
07/15/16	Not Good	Not Good	Good
07/19/16	Good	Good	Good
07/25/16	Not Good	Good	Good
07/26/16	Good	Good	Good
07/30/16	Not Good	Good	Not Good

Then I want the count query to return:

Count_T1	Count_T2	Count_T3
4	2	1

Currently my query just counts all the records in each column, not just the ones that are valued "Not Good".

If this is any use here is the SQL behind my current non-working query:

[CODE]
SELECT Count(*) AS Count_T1, Count(*) AS Count_T2, Count(*) AS Count_T3, Count(*) AS Count_T4, ... through all tasks
FROM displayDataLastMonth
WHERE (((displayDataLastMonth.[Task1])="Not Good")) OR (((displayDataLastMonth.[Task2])="Not Good")) OR (((displayDataLastMonth.[Task3])="Not Good")) OR (((displayDataLastMonth.[Task4])="Not Good")) ... through all tasks ;
[\CODE]

Edit: Code tags are not formatting properly for me. Not sure if my computer or not, apologies if messy SQL.

[ssanfu]

Is the table design

ID (autonumber)
Date (Date/Time) ' <-- Date is a reserved word and shouldn't be used as an object (field) name. Plus, it is not very descriptive: "Date" of what??
Task1 (Text)
Task2 (Text)
Task3 (Text)
.
.
.
Task20 (Text)


If yes, then you have "Committed spreadsheet". That is to say, your table design is like a spreadsheet - short and wide. Access tables usually are tall and narrow. Your table is not normalized.

You might try (untested)

SELECT Count(Task1) AS Count_T1, Count(Task2) AS Count_T2, Count(Task3) AS Count_T3, Count(Task4) AS Count_T4, ... through all tasks
FROM displayDataLastMonth
WHERE (((displayDataLastMonth.[Task1])="Not Good")) OR  (((displayDataLastMonth.[Task2])="Not Good")) OR  (((displayDataLastMonth.[Task3])="Not Good")) OR  (((displayDataLastMonth.[Task4])="Not Good")) ... through all tasks  ;

Also, with this kind of structure, if you add another task (because we all know the number of tasks will always change) you will have to change the table(s), forms, queries, reports and possible the VBA code!



In a normalized table structure, the table might look like:
ID (autonumber)
Date (Date/Time) ' <-- Date is a reserved word and shouldn't be used as an object (field) name
TaskName (Text) ' example "Task1", "Task2"
TaskStatus ' example "Good", "Not Good"

Then the count query is trivial.

[nick404]

Yes that is how the table is set up (Date field is "entryDate"). Thank you, I will give that a try. Otherwise it seems I may have to re-design the poor thing. Unfortunately, I inherited the database as is, and there are many records already in it...
So when I went to add some reporting functionality I left the design as is, unless you know of a way I can easily or semi-easily import the existing data into the normalized format?

Two after thoughts.
First, if I were to import the old data into a new format as above, wouldn't I end up with a lot of nearly duplicate entries?
So someone goes to a work area and begins the "audit" (this is more or less an informal audit tracking). Now if we have the tbl set up as TaskName and TaskStatus, then won't I end up with 20 entries for Person XY on EntryDate MMDDYY ? Bad thing or not?
Second, one way to get this query working would be to have count queries for each task then union them together...I don't really want 20 extra queries though.

[ssanfu]

Remember, I know nothing about your dB........

unless you know of a way I can easily or semi-easily import the existing data into the normalized format?

Append queries would be my first choice..... or could use VBA to loop, changing the field (task) in the query as an alternative to writing 20 queries.

First, if I were to import the old data into a new format as above, wouldn't I end up with a lot of nearly duplicate entries?

Lets say you have 30 records in the current table design and there are 20 tasks. In the normalized table format you would have 600 records instead of 30.
You have the same amount of data (fields * records), just in a format that is easier to manipulate.

So someone goes to a work area and begins the "audit" (this is more or less an informal audit tracking). Now if we have the tbl set up as TaskName and TaskStatus, then won't I end up with 20 entries for Person XY on EntryDate MMDDYY ? Bad thing or not?

Yes.... Not
Yes - because that is the way a relational database works.
Not - because the data is easier to work with and you can massage the records/fields to have it look like the current table structure if you want.


Another idea. Again, I don't know what you want to do with the counts - it sounds like you just want to display the counts.
Using VBA (how are your VBA skills?) and the query you already have (is it "displayDataLastMonth"?), you could write a UDF to loop through the recordset and count the "Not Goods" for each task, then write the totals to unbound text box controls on a form or save to a table for later use or ???