Counting How many 'U' per row in query.

[larry72450]

I have a query I created that has 7 days per week x 16 weeks = 112 columns containing absenteeism records for students for a 16 week semester. A single letter. For example: P = Present, U = Unexcused, H = Holiday etc



How do I count the number of "U" each student has and list the result in a report or a query.

I exported a sample query in Excel.

Thanks,

Larry

[Minty]

The short answer is not in that query.
Is that really a query export?

Can you show us a sample of the source table and data please.

[larry72450]

Ive created me a nightmare with this database. I hate the structure of it.

Here is the table.
Class_Attendance.zip

[Micron]

112 columns containing absenteeism records

Sounds like a design problem. Counting across columns is never easy in Access. Maybe take a step back and review to see if you followed any of these principles:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

[Welshgasman]

If you are going to output data like that, do it in Excel ?

[June7]

Summing across is simple arithmetic.

field1 + field2 + field3

However, if field is Null, result of expression will be Null so must handle Null

Nz(field1,0) + Nz(field2,0) + Nz(field3,0)

If you want a count based on field content, use IIf

IIf(field1="U",1,0) + IIf(field2="U",1,0) + IIf(field3="U",1,0)

Expression can be in query or textbox.

[Minty]

Ive created me a nightmare with this database. I hate the structure of it.

Here is the table.
Class_Attendance.zip

I'm afraid I can confirm your diagnosis.
That isn't a database table, it's a excel spreadsheet dumped into an access table.

Your attendance records should be in at least 2 tables something like.

tbL_AttendanceRecords					tbl_Students
AttendanceRecID	StudentID_FK	AttendDate	AttendanceStatus		StudentID	StudentName	DOB	OtherStudentSpecificDataGoesHere
1	1	18/08/2021	U		1	John Doe	01/02/2005
2	2	18/08/2021	P		2	Jane Doe	02/02/2005
3	3	18/08/2021	H		3	Frank Doe	03/02/2005
4	4	18/08/2021	E		4	Susan Doe	04/02/2005
5	5	18/08/2021	P		5	Alice Doe	05/02/2005
6	6	18/08/2021	P		6	Henry Doe	06/02/2005
7	1	19/08/2021	p
8	2	19/08/2021	p
9	3	19/08/2021	P
10	4	19/08/2021	E
11	5	19/08/2021	H
12	6	19/08/2021	P
13	1	20/08/2021	P
14	2	20/08/2021	P
15	3	20/08/2021	U
16	4	20/08/2021	U
17	5	20/08/2021	U
18	6	20/08/2021	U

(I'd have a table for the attendance status as well. Ignore that as a nice to have at the moment)

But can you now see how easy it is to count up the number of records with a specific attendance type?

[larry72450]

Yes I can do the iff , but that is a lot of writing, 112 columns for U, 112 to search for A, then P, then H, etc Thats a lot. Access has its limits

I was hoping for SQL or vb code, dcount that would just hunt all columns for "U" etc

[orange]

I'm not sure of your overall application, but this model may offer some insight. It's the Roster table I'm thinking about; and it could have an associated field that is related to Status that identifies the meanings of your HPUE.

Instead of multiple iifs, you may consider Select Case --but readers don't know the details of your process(es) nor the audience for any form/reports/statistics.

[June7]

Yes I can do the iff , but that is a lot of writing, 112 columns for U, 112 to search for A, then P, then H, etc Thats a lot. Access has its limits

I was hoping for SQL or vb code, dcount that would just hunt all columns for "U" etc

Yes, that comes with poorly designed db. Normalize!

Yes, a VBA custom function could be written.

[larry72450]

I do plan on re-writing most of this db, if not all this next summer. But for now, everyone is using it and I cannot do a lot. I would like to create a report that does search for all "U" in the SQL query or table in all columns if possible.

If not I do appreciate all the comments. I did like the examples and help files that was suggested.

[Gicu]

Please have a look at the attached sample, it should give you what you want but strongly recommend a redesign of your db based on the suggestions received above.

Cheers,
Vlad

[Micron]

So simple arithmetic doesn't always translate to easy, does it?

[larry72450]

All I got to say is WOW. I know the hard work you did in doing this and quickly too. and its appreciated a lot. I will be adding this to my current (badly designed) database. Soon i will do a redesign.

[Gicu]

You're welcome Larry!

Cheers,