MSACCESS help in counting text values

[dxsmith]

I'm new to Access and need some help if possible. I have a report that I'm trying to build from a table. The table lists students in a class. The table contains the usual fields such as name, class, address, state, etc. Becasue I need to know if the students were present or absent on certain dates, I made the dates fields in the table. I want to be able to count the number of dates each student was present and absent on a report. The value that I'm usinf in the date fields is "P" for present and "A" for absent. How do I tell Access to tell me how many "P"s and how many"A"s for a series of dates? How do I count non-numeric values from a field?
Thanks

[Scott Price]

I'm new to Access and need some help if possible. I have a report that I'm trying to build from a table. The table lists students in a class. The table contains the usual fields such as name, class, address, state, etc. Becasue I need to know if the students were present or absent on certain dates, I made the dates fields in the table. I want to be able to count the number of dates each student was present and absent on a report. The value that I'm usinf in the date fields is "P" for present and "A" for absent. How do I tell Access to tell me how many "P"s and how many"A"s for a series of dates? How do I count non-numeric values from a field?
Thanks

Hi there, and welcome to the Scripts!

You can use the DCount(expr ,domain ,[criteria] ) function to count the occurrences of any value (text included) of a field.

To give you any more specific help we'll need to know a few more things about the structure of your table. Specifically, you say that the date field contains the P and A values? In what format, then, is the date stored?

Please post your table structure. Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :

Table Name=tblStudent

          Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Scott

[ADezii]

I'm new to Access and need some help if possible. I have a report that I'm trying to build from a table. The table lists students in a class. The table contains the usual fields such as name, class, address, state, etc. Becasue I need to know if the students were present or absent on certain dates, I made the dates fields in the table. I want to be able to count the number of dates each student was present and absent on a report. The value that I'm usinf in the date fields is "P" for present and "A" for absent. How do I tell Access to tell me how many "P"s and how many"A"s for a series of dates? How do I count non-numeric values from a field?
Thanks

1. Restrict the Date Range in the Record Source for the Report.
2. Create a Text Box in the Report Footer with a Label similar to: Total Students present:
3. Copy and Paste this code to the Control Source of the Text Box:
   

   =DCount("*","<Report Record Source>","[Date]='P'")

4. Create a Text Box in the Report Footer with a Label similar to: Total Students absent:
5. Copy and Paste this code to the Control Source of the Text Box:
   

   =DCount("*","<Report Record Source>","[Date]='A'")

[dxsmith]

Hi there, and welcome to the Scripts!

You can use the DCount(expr ,domain ,[criteria] ) function to count the occurrences of any value (text included) of a field.

To give you any more specific help we'll need to know a few more things about the structure of your table. Specifically, you say that the date field contains the P and A values? In what format, then, is the date stored?

Please post your table structure. Please include the MetaData for all relevant datasets. Here is an example of how to post table MetaData :

Table Name=tblStudent

          Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Scott

Thanks for the reply, I think I probaly need to start over; my table has the standard fields except I used the dates themselves as the field names in my table as seen below. On the report I wanted to show on the same row with each student, each day, and the status of their attendance. The finished report, I had hoped would have a row that shows name, class, sept 9, 2007, sept 16, 2007, etc. and the attendance value for each. Then at the end of the row for each student I would have the count of how many "P"s and the count of how many "A"s

Table Name=tblStudent
StudentID
LastName
FirstName
sept 9, 2007
sept 16, 2007
sept 30, 2007
Oct 1, 2007
etc

[Scott Price]

Thanks for the reply, I think I probaly need to start over; my table has the standard fields except I used the dates themselves as the field names in my table as seen below. On the report I wanted to show on the same row with each student, each day, and the status of their attendance. The finished report, I had hoped would have a row that shows name, class, sept 9, 2007, sept 16, 2007, etc. and the attendance value for each. Then at the end of the row for each student I would have the count of how many "P"s and the count of how many "A"s

Table Name=tblStudent
StudentID
LastName
FirstName
sept 9, 2007
sept 16, 2007
sept 30, 2007
Oct 1, 2007
etc

Hmm... I think you can easily see now how the structure you have artificially limits your capabilities, especially down the road! For example say you decide to add class days, or next year you need to change the dates. It will be a royal pain to have to go into your table and manually change each date field to correspond to the new class days.

However, there are other options :-) Probably the best one that I can think of right now is to create another table and call it Attendance (or tblAttendance if you want to use that naming convention). It will have these fields:

tblAttendance
AttendanceID Autonumber, PK
StudentID Number FK
ClassDate Date/Time
Attendance Text w/ validation rule: P, A

You can then delete from your Students table all the fields that refer to dates, and you will create a relationship between the StudentID field in tblStudents and the StudentID field in tblAttendance.

Now you can create a query that looks and counts each student's P's and A's quite easily.

Please read this tutorial on Database Normalisation and Table Structures also.

Regards,
Scott