Counting checkmarks across colums
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Counting checkmarks across colums
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    81
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Counting checkmarks across colums

    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.

  2. #2
    Board Regular
    Join Date
    May 2013
    Posts
    628
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting checkmarks across colums

    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.
    Office 2007
    Access novice. Sometimes trying to give something back

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,443
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Counting checkmarks across colums

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular
    Join Date
    Nov 2010
    Posts
    81
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting checkmarks across colums

    Quote Originally Posted by Joe4 View Post
    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.

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,443
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Counting checkmarks across colums

    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/...ization-basics
    Last edited by Joe4; Feb 11th, 2019 at 12:39 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Nov 2010
    Posts
    81
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting checkmarks across colums

    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.

  7. #7
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,443
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Counting checkmarks across colums

    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).
    Last edited by Joe4; Feb 11th, 2019 at 01:59 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #8
    Board Regular
    Join Date
    Nov 2010
    Posts
    81
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting checkmarks across colums

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

  9. #9
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,443
    Post Thanks / Like
    Mentioned
    54 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Counting checkmarks across colums

    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).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    Nov 2010
    Posts
    81
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting checkmarks across colums

    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"?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •