Microsoft Access - Count Multiple Cells (Fields)

Manny74

Board Regular
Joined
May 6, 2016
Messages
124
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>
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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)
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
Try sgn([APPT 1]) + sgn([APPT 2])....etc

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

Would this work?

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


Excel 2010
ABCDEFGHIJ
142948
24346543206.5
3
4
56/26/20188/27/201712/6/201812/31/20182/4/201810/10/20185/12/20181/26/20188.008
66/21/20184/14/20188/31/20185/14/20187/13/20185.005
74/14/20186/18/201810/2/20184/14/201811/10/20173/2/20189/23/20177.007
810/30/201712/13/201812/12/20178/5/20189/9/20187/23/20186.006
96/26/201811/11/201812/14/201710/23/20174/16/20189/14/20188/24/20177.007
102/13/20183/20/20188/7/20171/20/20184.004
111/14/20189/23/201712/26/20183.003
Sheet2
Cell Formulas
RangeFormula
A1=DATEVALUE("8/1/2017")
A2=DATEVALUE("12/31/2018")
B2=AVERAGE(A1:A2)
I5=ROUND(SUM(A5:H5)/$B$2,0)
J5=COUNT(A5:H5)

Access's round function works differently from Excel's, however, and can return unwanted numbers.
 
Last edited:
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top