Microsoft Access - Count Multiple Cells (Fields)

[Manny74]

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)

[TABLE="width: 661"]
<tbody>[TR]
[TD]CLIENT[/TD]
[TD]1ST APPT[/TD]
[TD]2ND APPT[/TD]
[TD]3RD APPT[/TD]
[TD]4TH APPT[/TD]
[TD]NUMBER OF APPTS[/TD]
[/TR]
[TR]
[TD]DOE, JOHN[/TD]
[TD]8/1/2017[/TD]
[TD]2/1/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]DOE, JANE[/TD]
[TD]9/2/2017[/TD]
[TD]10/1/2017[/TD]
[TD]3/2/2017[/TD]
[TD] [/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]DOE, JAKE[/TD]
[TD]10/5/2017[/TD]
[TD]12/5/2017[/TD]
[TD]2/5/2018[/TD]
[TD]3/3/2018[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]DOE, JUNE[/TD]
[TD]11/15/2017[/TD]
[TD]6/18/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]DOE, JOE[/TD]
[TD]6/22/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]1[/TD]
[/TR]
</tbody><colgroup><col><col span="3"><col><col></colgroup>[/TABLE]

 

[Joe4]

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:

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]

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.

 

[Manny74]

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]

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]

Try sgn([APPT 1]) + sgn([APPT 2])....etc

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

 

[sheetspread]

[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
	A	B	C	D	E	F	G	H	I	J
1	42948
2	43465	43206.5
3
4
5	6/26/2018	8/27/2017	12/6/2018	12/31/2018	2/4/2018	10/10/2018	5/12/2018	1/26/2018	8.00	8
6	6/21/2018				4/14/2018	8/31/2018	5/14/2018	7/13/2018	5.00	5
7	4/14/2018	6/18/2018	10/2/2018	4/14/2018	11/10/2017	3/2/2018	9/23/2017		7.00	7
8	10/30/2017	12/13/2018	12/12/2017	8/5/2018		9/9/2018		7/23/2018	6.00	6
9	6/26/2018	11/11/2018	12/14/2017	10/23/2017	4/16/2018	9/14/2018	8/24/2017		7.00	7
10	2/13/2018			3/20/2018	8/7/2017			1/20/2018	4.00	4
11	1/14/2018	9/23/2017	12/26/2018						3.00	3
Sheet2

Cell Formulas
Range	Formula
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.

 

[Manny74]

So I entered Exr1: Sgn ([APPT 1]) + Sgn ([APPT 2])
Run the query
I get a Enter Parameter Value
Not data is returned

 

[Manny74]

How do I change the table structure?

 

[Manny74]

Where would I enter this code?