Microsoft Access - Count Multiple Cells (Fields)

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello All,

I am using MS Access....

As I run the data, I have each client's appointments listed (1st, 2nd, etc.)

I need a total count of appointments for each client (Number of Appts)...

I know how to create a count in Excel, but not sure how to attempt this in Access...

How do I add a count in Access to give me total number of appointments? (similar to what I have below)

CLIENT1ST APPT2ND APPT3RD APPT4TH APPTNUMBER OF APPTS
DOE, JOHN8/1/20172/1/2018 2
DOE, JANE9/2/201710/1/20173/2/2017 3
DOE, JAKE10/5/201712/5/20172/5/20183/3/20184
DOE, JUNE11/15/20176/18/2018 2
DOE, JOE6/22/2018 1

<tbody>
</tbody><colgroup><col><col span="3"><col><col></colgroup>
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,687
Office Version
  1. 365
Platform
  1. Windows
This is easy with an aggregate query in Access if your table is designed in a normalized fashion. Unfortunately, your table is not normalized (you should not have repeating similar fields).
The normalized structure would be Client Name, Appt Number, Appt Date
So, the first person would have two records, the next three records, etc.
The beauty in this design is if you ever had more than 4 appointments, you would not need to create more fields. You simply add another record.
Also, the structure you currently have makes it very hard to search for a particular date - you would need to search across four fields instead of one!

So, if you have any ability to change the table structure, I would recommend that.
Otherwise, to get your count, you need to do a calculated field something like this:
Code:
IIf([TableName]![Appt1]>0,1,0)+IIf([TableName]![Appt2]>0,1,0)+IIf([TableName]![Appt3]>0,1,0)+IIf([TableName]![Appt4]>0,1,0)
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
You can also use the Sgn() function which will return 1 for dates and 0 for zeroes (nulls may or may not have to be converted with Nz() first, I can't test on this Access-less computer). Joe4 is right though: the normal structure with a crosstab query is far more efficient than entering your data as a matrix followed by a quick fix formula.
 
Last edited:

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I'm not sure if I'm fully understanding....

So I went to my table...
Went to View.
Added a new field called Number of Appts...
From Data Type, I selected Calculated
Expression Builder came up....
So I added [APPT 1] + [APPT 2] + [APPT 3] + [APPT 4]

Would this work?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,687
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So I added [APPT 1] + [APPT 2] + [APPT 3] + [APPT 4]

Would this work?
No, that is going to add all the date values, not count them.
Access and Excel store dates as numbers, specifically the number of days since 1/0/1900.
So adding up all those date values will either result in some really big numbers (if all four dates are populated), or nothing/errors (for records without all four dates populated).
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119
Try sgn([APPT 1]) + sgn([APPT 2])....etc

If the nulls jam it then try sgn(Nz([APPT 1],0))
 
Last edited:

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,119

ADVERTISEMENT

[APPT 1] + [APPT 2] + [APPT 3] + [APPT 4]

Would this work?

If you divide the sum by the right average and round, it might:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;">42948</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">43465</td><td style="text-align: right;;">43206.5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">6/26/2018</td><td style="text-align: right;;">8/27/2017</td><td style="text-align: right;;">12/6/2018</td><td style="text-align: right;;">12/31/2018</td><td style="text-align: right;;">2/4/2018</td><td style="text-align: right;;">10/10/2018</td><td style="text-align: right;;">5/12/2018</td><td style="text-align: right;;">1/26/2018</td><td style="text-align: right;;">8.00</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;">6/21/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4/14/2018</td><td style="text-align: right;;">8/31/2018</td><td style="text-align: right;;">5/14/2018</td><td style="text-align: right;;">7/13/2018</td><td style="text-align: right;;">5.00</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">4/14/2018</td><td style="text-align: right;;">6/18/2018</td><td style="text-align: right;;">10/2/2018</td><td style="text-align: right;;">4/14/2018</td><td style="text-align: right;;">11/10/2017</td><td style="text-align: right;;">3/2/2018</td><td style="text-align: right;;">9/23/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;">7.00</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">10/30/2017</td><td style="text-align: right;;">12/13/2018</td><td style="text-align: right;;">12/12/2017</td><td style="text-align: right;;">8/5/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">9/9/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;">7/23/2018</td><td style="text-align: right;;">6.00</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">6/26/2018</td><td style="text-align: right;;">11/11/2018</td><td style="text-align: right;;">12/14/2017</td><td style="text-align: right;;">10/23/2017</td><td style="text-align: right;;">4/16/2018</td><td style="text-align: right;;">9/14/2018</td><td style="text-align: right;;">8/24/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;">7.00</td><td style="text-align: right;;">7</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">2/13/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3/20/2018</td><td style="text-align: right;;">8/7/2017</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1/20/2018</td><td style="text-align: right;;">4.00</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">1/14/2018</td><td style="text-align: right;;">9/23/2017</td><td style="text-align: right;;">12/26/2018</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3.00</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A1</th><td style="text-align:left">=DATEVALUE(<font color="Blue">"8/1/2017"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">=DATEVALUE(<font color="Blue">"12/31/2018"</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=AVERAGE(<font color="Blue">A1:A2</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I5</th><td style="text-align:left">=ROUND(<font color="Blue">SUM(<font color="Red">A5:H5</font>)/$B$2,0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J5</th><td style="text-align:left">=COUNT(<font color="Blue">A5:H5</font>)</td></tr></tbody></table></td></tr></table><br />
Access's round function works differently from Excel's, however, and can return unwanted numbers.
 
Last edited:

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So I entered Exr1: Sgn ([APPT 1]) + Sgn ([APPT 2])
Run the query
I get a Enter Parameter Value
Not data is returned
 
Last edited:

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
How do I change the table structure?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,801
Messages
5,626,965
Members
416,212
Latest member
Ifemiide

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top