Counting checkmarks across colums

[mattmickle]

Hi All,
Another noob Access question:

I have a table set up:
Dept ID#, Last Name, First Name, 01/01/19 (YES/NO column)

For each person, I'm checking "YES" if they were there on that date. The dates could be a couple days apart or weeks, you never know. So, basically, this is an attendance record with unknown dates and unknown people.

I'm looking for a way to count the "YES" check marks by person.
For example:

021 Mickle, M - Yes on 01/01/19, Yes on 01/14/19, No on 01/15/19, Yes on 01/23/19 = 3 Yesses.
I don't know how many dates I have, could have the same date twice (at 2 different times), but want to keep a running total of how many "Yesses" each person has.

I could whip this off in Excel in about 3 minutes, but everything else I have is in access so I'm trying to keep it there.

Any help is always appreciated.

 

[welshgasman]

You would use an aggregate query and in this case Count(YNColumn)
Use DISTINCT if you only want one of each date.

Should take less than 3 minutes with the QBE design mode.

 

[Joe4]

So, do you have a different column for each date?
If so, then this is the problem. You have a table structure that is not normalized, which makes simply tasks more difficult than they should be.
In a well-designed database, you would not have the need to keep changing your table structure (keep adding more columns).

The table strcuture should just have these fields:
- Dept ID
- Last Name
- First Name
- Date
- Yes/No

So adding more dates does not necessitate the need to add more columns, you are just adding more records.
Then, to get a count by person, you would just do an Aggregate (Totals) Query, grouping on the name columns (and department, if you wish), put criteria to only inlcude "Yes" records, and do a Count of the Date field.
That would give you what you want.

 

[mattmickle]

So, do you have a different column for each date?
If so, then this is the problem. You have a table structure that is not normalized, which makes simply tasks more difficult than they should be.
In a well-designed database, you would not have the need to keep changing your table structure (keep adding more columns).

The table strcuture should just have these fields:
- Dept ID
- Last Name
- First Name
- Date
- Yes/No

So adding more dates does not necessitate the need to add more columns, you are just adding more records.
Then, to get a count by person, you would just do an Aggregate (Totals) Query, grouping on the name columns (and department, if you wish), put criteria to only inlcude "Yes" records, and do a Count of the Date field.
That would give you what you want.

Joe, so in this way, wouldn't I need to add the Dept ID#, Last Name, and First Name each time for each date?

For all intents and purposes, this is an attendance record of a set # of employees (could grow or shrink, but doesn't happen that often). I need to be able to count which employees showed up on which dates. So, Joe Test, showed up on 1/4, 1/6 and 1/10 = 3, but Jane Test showed up on 1/4, 1/10, 1/13, and 1/14 = 4.

I appreciate the help. I'm a whiz with Excel, but Access is something fairly new to me.

 

[Joe4]

Joe, so in this way, wouldn't I need to add the Dept ID#, Last Name, and First Name each time for each date?

Well, if you really wanted to design this in a typical relational database model, you would probably have a separate employee table that had:
- employee id
- first name
- last name
- department

Then this attendence table would use employee id instead of first name, last name, and department (you could just link back to the employee table to get that information)

In a normalized table design, you should seldom have the need to repeat fields like that for every record. That is one of the keys of a "relational" database. You segregate it into separate data tables that make sense, and "relate" the data tables to one another.

For all intents and purposes, this is an attendance record of a set # of employees (could grow or shrink, but doesn't happen that often). I need to be able to count which employees showed up on which dates. So, Joe Test, showed up on 1/4, 1/6 and 1/10 = 3, but Jane Test showed up on 1/4, 1/10, 1/13, and 1/14 = 4.

The proper, normalized table structure will still be the one I explained. Using criteria under the date field, you can return which employees should up on which dates.

Here are some good examples of simple tasks that are made harder by having a de-normalized structure:

Return each person and the number of Yes's they have
In the normalized structure I explained, this would be a simple Aggregate Query
In the other structure, you would need a calculated field and that checks and adds each field individually. It may not seem a big deal if you only had 3 dates, but what if there were 100? Or what if you needed to add more dates later on? Then you would need to modify/edit the calculated field.
A general rule of thumb is this. If adding more of the same type of data causes you to have to edit your table structure (i.e. add fields) or edit queries, that is a red flag that it is not designed efficiently.

Return all people who have missed at least one day (have one now)
In a normalized structure, you rwould once again just use a simple Aggregate Query, with the criteria of "No" in your Yes/No field.
In the original structure, you would either need to have a long crieria statement checking for a "No" in each and every date field, or you would need a long calculated field counting the number of "No's" in each record.

As you can see, a table design that is not normalized makes simple tasks much harder to complete. A good design allows you to do pretty much anything you want, usually with little trouble.

Here is a good write-up on normalization: https://support.microsoft.com/en-us/help/283878/description-of-the-database-normalization-basics

 

[mattmickle]

OK, thanks again for being patient with me. I did say i was a noob at all this.
So, if I'm following all this correctly, then I need to rebuild my tables.
Instead of seeing all of John Test's attendance across, and having all my employee in the table (even those with no attendance), I would only enter those in attendance, using only Dept id# and the date they attended.

 

[Joe4]

OK, thanks again for being patient with me. I did say i was a noob at all this.

No worries. It is good that you are asking. It may save you from a lot of the headaches many of us had to learn the hard way.

You would basically have two tables:

Employee
- employee id
- first name
- last name
- department

Attendence
- employee id
- date
- yes/no

The two tables are related by the common employee id.

The "yes/no" field is really optional, as you can go one of two ways:
1. Enter the record with a value of "no"
2. Not have a "yes/no" field, and only add records for people who are present on that day
The choice is yours. I might be more inclined to include a "no" record, as it will making searching for and counting "no's" a little easier. Otherwise, you would just need to do an unmatched query between the two tables (not that big of a deal, unless you were trying to check multiple days at once, in which case it gets a little tricky, but not too bad).

If desired, for presentation purposes, you can change the way things look by doing things like Cross Tab Queries (which are kind of like Excel's Pivot Tables).

 

[mattmickle]

Any recommendations on a good source of Access knowledge? I think I may need to back up a bit and learn some basics...

 

[Joe4]

When I was first doing this kind of stuff, I got an introductory Access book, then an Access VBA book. But I later found out that this is not quite enough, so I pick up a book on Relational Database Theory. This contains things like rules of normalization and database design.

Think of it like this, that Access is the "toolbox", but Relational Database Theory is more like the "instruction manual". The tools are great, but without the knowledge of how and when to use them, you probably won't get very far.
This is the book I used for that: https://www.springer.com/us/book/9781852334017
It is kind of old (from 2001), but the theory really doesn't change much.

Access has changed, but there are lots of good introductory books. For the least amount of frustration, try to get one that is for the same version of Access that you have.
Just be careful not to get too enamored with some of the new functionality. There are a few "advances" that can actually end up causing more problems then they solve. The two that I can think of is:
1. The ability to do calculated fields directly in a table. There are limitations to this, and it is not compatible with any other database program. Avoid using this. Do all calculations in a query instead.
2. The use of multi-valued fields. It may seem cool at first, but these can present a nightmare (they don't really follow the rules of normalization).

 

[mattmickle]

Thanks Joe!
Ok, I've got my aggregate query up and running.
Shows ID#, I pull in Last Name and First Name from another table, and I count the dates each employee participated.
Can I now take that count and multiply it by 5 for another field called "Credits"?