how do i count instances of a word in an Access report?

[Guest]

I have a report that shows attendances. I have instances of Present, Sick
and Absent. I would like to count the instances of each one and have it
record the number on the report. Is there anyway of doing this?

 

[Duane Hookom]

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more information.

 

[Guest]

in my database I have a number of learners and I also record their attendance
for each week that they are on the course. I have run a report to show what
that learners attendance is over the time the learner was on the course and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under monday is
called Attendance_Key_Monday(selected on a drop down) and there can be many
instances depending on how long the learner is on the course. Does this help
anymore? If you need more information just tell me what you need.

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more information.

--
Duane Hookom
MS Access MVP

I have a report that shows attendances. I have instances of Present, Sick
and Absent. I would like to count the instances of each one and have it
record the number on the report. Is there anyway of doing this?

 

[Duane Hookom]

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week? If so,
is there any chance that you can change the structure to be more normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record their
attendance
for each week that they are on the course. I have run a report to show
what
that learners attendance is over the time the learner was on the course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under monday is
called Attendance_Key_Monday(selected on a drop down) and there can be
many
instances depending on how long the learner is on the course. Does this
help
anymore? If you need more information just tell me what you need.

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more information.

--
Duane Hookom
MS Access MVP

I have a report that shows attendances. I have instances of Present,
Sick
and Absent. I would like to count the instances of each one and have
it
record the number on the report. Is there anyway of doing this?

 

[Guest]

Tables: -
LEARNERS(learner_id(pk), firstname, surname, address1, address2, town,
county, postcode, contact_number, dob, gender, ethnicity, healt_problems,
learning_difficulties, PA, start_date, leave_date, outcome, accreditation,
warnings, notes)

Learners table is linked to:
ATTENDANCE(attendance_id(pk), learner_id(fk), attendance_key_monday,
attendance_key_tuesday, attendance_key_wednesday, attendance_key_thursday,
attendance_key_friday)

the attendance_key fields use a look up for:
ATTENDANCE_KEY(attendance_key_id(pk), key_description)

The data I get from my report at the minute is:

Kevin Martin
Month W/E Monday Tuesday Wednesday Thursday Friday

Nov-04
29/11/2004 N/A N/A Present Present
Present
Dec-04
06/12/2004 Present Present Present Sick N/A

13/12/2004 Sick Sick Sick Sick N/A
20/12/2004 Absence Present Absence Absence N/A
27/12/2004 Present Present Present N/A N/A

What I would like to see is a total of instances of Present, Sick, Absent
etc at the bottom of my report.

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week? If so,
is there any chance that you can change the structure to be more normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record their
attendance
for each week that they are on the course. I have run a report to show
what
that learners attendance is over the time the learner was on the course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under monday is
called Attendance_Key_Monday(selected on a drop down) and there can be
many
instances depending on how long the learner is on the course. Does this
help
anymore? If you need more information just tell me what you need.

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more information.

--
Duane Hookom
MS Access MVP


I have a report that shows attendances. I have instances of Present,
Sick
and Absent. I would like to count the instances of each one and have
it
record the number on the report. Is there anyway of doing this?

 

[Duane Hookom]

You haven't suggested if you want your totals under each column or do you
want to add across columns. Also, I assume you store the primary key from
the Attendance_Key table in each of the "day" fields. To total
Monday/Present, use and expression like:
=Sum(Abs(Attendance_Key_Monday = 1))

The number 1 above possibly relates to the value for Present. You haven't
provided the key values for any of the descriptions.

I would not have an attendance table with "day" columns. I would normalize
table.

--
Duane Hookom
MS Access MVP
--

Tables: -
LEARNERS(learner_id(pk), firstname, surname, address1, address2, town,
county, postcode, contact_number, dob, gender, ethnicity, healt_problems,
learning_difficulties, PA, start_date, leave_date, outcome, accreditation,
warnings, notes)

Learners table is linked to:
ATTENDANCE(attendance_id(pk), learner_id(fk), attendance_key_monday,
attendance_key_tuesday, attendance_key_wednesday, attendance_key_thursday,
attendance_key_friday)

the attendance_key fields use a look up for:
ATTENDANCE_KEY(attendance_key_id(pk), key_description)

The data I get from my report at the minute is:

Kevin Martin
Month W/E Monday Tuesday Wednesday Thursday Friday

Nov-04
29/11/2004 N/A N/A Present Present
Present
Dec-04
06/12/2004 Present Present Present Sick N/A

13/12/2004 Sick Sick Sick Sick N/A
20/12/2004 Absence Present Absence Absence N/A
27/12/2004 Present Present Present N/A N/A

What I would like to see is a total of instances of Present, Sick, Absent
etc at the bottom of my report.

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week? If
so,
is there any chance that you can change the structure to be more
normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record their
attendance
for each week that they are on the course. I have run a report to show
what
that learners attendance is over the time the learner was on the course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present
Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick
N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under monday
is
called Attendance_Key_Monday(selected on a drop down) and there can be
many
instances depending on how long the learner is on the course. Does
this
help
anymore? If you need more information just tell me what you need.


:

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more information.

--
Duane Hookom
MS Access MVP


I have a report that shows attendances. I have instances of
Present,
Sick
and Absent. I would like to count the instances of each one and
have
it
record the number on the report. Is there anyway of doing this?

 

[Guest]

I just would like a way of counting all presents across the whole report. I
suppose if I did it by column I could then add each column together to get
the total.


The key values are as follows:
1 - Present
2 - Authorised Absence
3 - Unauthorised Absence
4 - Sick
5 - Holiday
6 - Not Required
7 - Late
8 - Bank Holiday

Im afraid I dont understand what you mean by not having day columns and
making it normalised. Could you explain please?

You haven't suggested if you want your totals under each column or do you
want to add across columns. Also, I assume you store the primary key from
the Attendance_Key table in each of the "day" fields. To total
Monday/Present, use and expression like:
=Sum(Abs(Attendance_Key_Monday = 1))

The number 1 above possibly relates to the value for Present. You haven't
provided the key values for any of the descriptions.

I would not have an attendance table with "day" columns. I would normalize
table.

--
Duane Hookom
MS Access MVP
--

Tables: -
LEARNERS(learner_id(pk), firstname, surname, address1, address2, town,
county, postcode, contact_number, dob, gender, ethnicity, healt_problems,
learning_difficulties, PA, start_date, leave_date, outcome, accreditation,
warnings, notes)

Learners table is linked to:
ATTENDANCE(attendance_id(pk), learner_id(fk), attendance_key_monday,
attendance_key_tuesday, attendance_key_wednesday, attendance_key_thursday,
attendance_key_friday)

the attendance_key fields use a look up for:
ATTENDANCE_KEY(attendance_key_id(pk), key_description)

The data I get from my report at the minute is:

Kevin Martin
Month W/E Monday Tuesday Wednesday Thursday Friday

Nov-04
29/11/2004 N/A N/A Present Present
Present
Dec-04
06/12/2004 Present Present Present Sick N/A

13/12/2004 Sick Sick Sick Sick N/A
20/12/2004 Absence Present Absence Absence N/A
27/12/2004 Present Present Present N/A N/A

What I would like to see is a total of instances of Present, Sick, Absent
etc at the bottom of my report.

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week? If
so,
is there any chance that you can change the structure to be more
normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record their
attendance
for each week that they are on the course. I have run a report to show
what
that learners attendance is over the time the learner was on the course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present
Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick
N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under monday
is
called Attendance_Key_Monday(selected on a drop down) and there can be
many
instances depending on how long the learner is on the course. Does
this
help
anymore? If you need more information just tell me what you need.


:

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more information.

--
Duane Hookom
MS Access MVP


I have a report that shows attendances. I have instances of
Present,
Sick
and Absent. I would like to count the instances of each one and
have
it
record the number on the report. Is there anyway of doing this?

 

[Duane Hookom]

Did you try add the expression I suggested? It should work to total the
present for monday.

Normalized tables would not have separate columns for each day of the week.
You would have an Attendance table with a structure like:
Attendance_ID
Learner_ID
AttendDate (date of attendance)
Attendance_Key_ID
This type of structure would make reporting and summarizing much more
flexible.


--
Duane Hookom
MS Access MVP
--

I just would like a way of counting all presents across the whole report.
I
suppose if I did it by column I could then add each column together to get
the total.


The key values are as follows:
1 - Present
2 - Authorised Absence
3 - Unauthorised Absence
4 - Sick
5 - Holiday
6 - Not Required
7 - Late
8 - Bank Holiday

Im afraid I dont understand what you mean by not having day columns and
making it normalised. Could you explain please?

You haven't suggested if you want your totals under each column or do you
want to add across columns. Also, I assume you store the primary key from
the Attendance_Key table in each of the "day" fields. To total
Monday/Present, use and expression like:
=Sum(Abs(Attendance_Key_Monday = 1))

The number 1 above possibly relates to the value for Present. You haven't
provided the key values for any of the descriptions.

I would not have an attendance table with "day" columns. I would
normalize
table.

--
Duane Hookom
MS Access MVP
--

Tables: -
LEARNERS(learner_id(pk), firstname, surname, address1, address2, town,
county, postcode, contact_number, dob, gender, ethnicity,
healt_problems,
learning_difficulties, PA, start_date, leave_date, outcome,
accreditation,
warnings, notes)

Learners table is linked to:
ATTENDANCE(attendance_id(pk), learner_id(fk), attendance_key_monday,
attendance_key_tuesday, attendance_key_wednesday,
attendance_key_thursday,
attendance_key_friday)

the attendance_key fields use a look up for:
ATTENDANCE_KEY(attendance_key_id(pk), key_description)

The data I get from my report at the minute is:

Kevin Martin
Month W/E Monday Tuesday Wednesday Thursday
Friday

Nov-04
29/11/2004 N/A N/A Present Present
Present
Dec-04
06/12/2004 Present Present Present Sick
N/A

13/12/2004 Sick Sick Sick Sick
N/A
20/12/2004 Absence Present Absence Absence
N/A
27/12/2004 Present Present Present N/A N/A

What I would like to see is a total of instances of Present, Sick,
Absent
etc at the bottom of my report.

:

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week? If
so,
is there any chance that you can change the structure to be more
normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record their
attendance
for each week that they are on the course. I have run a report to
show
what
that learners attendance is over the time the learner was on the
course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present
Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick
N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under
monday
is
called Attendance_Key_Monday(selected on a drop down) and there can
be
many
instances depending on how long the learner is on the course. Does
this
help
anymore? If you need more information just tell me what you need.


:

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more
information.

--
Duane Hookom
MS Access MVP


I have a report that shows attendances. I have instances of
Present,
Sick
and Absent. I would like to count the instances of each one and
have
it
record the number on the report. Is there anyway of doing this?

 

[Guest]

I did try the code suggested but it returned an #Error could I ask what the
"Abs" part of the query refers to?

Did you try add the expression I suggested? It should work to total the
present for monday.

Normalized tables would not have separate columns for each day of the week.
You would have an Attendance table with a structure like:
Attendance_ID
Learner_ID
AttendDate (date of attendance)
Attendance_Key_ID
This type of structure would make reporting and summarizing much more
flexible.


--
Duane Hookom
MS Access MVP
--

I just would like a way of counting all presents across the whole report.
I
suppose if I did it by column I could then add each column together to get
the total.


The key values are as follows:
1 - Present
2 - Authorised Absence
3 - Unauthorised Absence
4 - Sick
5 - Holiday
6 - Not Required
7 - Late
8 - Bank Holiday

Im afraid I dont understand what you mean by not having day columns and
making it normalised. Could you explain please?

You haven't suggested if you want your totals under each column or do you
want to add across columns. Also, I assume you store the primary key from
the Attendance_Key table in each of the "day" fields. To total
Monday/Present, use and expression like:
=Sum(Abs(Attendance_Key_Monday = 1))

The number 1 above possibly relates to the value for Present. You haven't
provided the key values for any of the descriptions.

I would not have an attendance table with "day" columns. I would
normalize
table.

--
Duane Hookom
MS Access MVP
--

Tables: -
LEARNERS(learner_id(pk), firstname, surname, address1, address2, town,
county, postcode, contact_number, dob, gender, ethnicity,
healt_problems,
learning_difficulties, PA, start_date, leave_date, outcome,
accreditation,
warnings, notes)

Learners table is linked to:
ATTENDANCE(attendance_id(pk), learner_id(fk), attendance_key_monday,
attendance_key_tuesday, attendance_key_wednesday,
attendance_key_thursday,
attendance_key_friday)

the attendance_key fields use a look up for:
ATTENDANCE_KEY(attendance_key_id(pk), key_description)

The data I get from my report at the minute is:

Kevin Martin
Month W/E Monday Tuesday Wednesday Thursday
Friday

Nov-04
29/11/2004 N/A N/A Present Present
Present
Dec-04
06/12/2004 Present Present Present Sick
N/A

13/12/2004 Sick Sick Sick Sick
N/A
20/12/2004 Absence Present Absence Absence
N/A
27/12/2004 Present Present Present N/A N/A

What I would like to see is a total of instances of Present, Sick,
Absent
etc at the bottom of my report.

:

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week? If
so,
is there any chance that you can change the structure to be more
normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record their
attendance
for each week that they are on the course. I have run a report to
show
what
that learners attendance is over the time the learner was on the
course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs Fri

Nov
29/11/04 N/A N/A Present Present
Present


Dec
06/12/04 Present Present Present Sick N/A
13/12/04 Sick Sick Sick Sick
N/A
20/12/04 Absent Present Absent Absent N/A

I tried the code but it didnt work. Every attendance type under
monday
is
called Attendance_Key_Monday(selected on a drop down) and there can
be
many
instances depending on how long the learner is on the course. Does
this
help
anymore? If you need more information just tell me what you need.


:

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more
information.

--
Duane Hookom
MS Access MVP


I have a report that shows attendances. I have instances of
Present,
Sick
and Absent. I would like to count the instances of each one and
have
it
record the number on the report. Is there anyway of doing this?

 

[Duane Hookom]

Make sure the name of the text control is not the name of a field. Abs() is
a arithmetic function for Absolute Value. The expression
"Attendance_Key_Monday = 1" will evaluate to either true/-1 or false/0. The
Absolute Value of this expression is either 1 or 0. This allows you to
"Count" the true values.

--
Duane Hookom
MS Access MVP
--

I did try the code suggested but it returned an #Error could I ask what the
"Abs" part of the query refers to?

Did you try add the expression I suggested? It should work to total the
present for monday.

Normalized tables would not have separate columns for each day of the
week.
You would have an Attendance table with a structure like:
Attendance_ID
Learner_ID
AttendDate (date of attendance)
Attendance_Key_ID
This type of structure would make reporting and summarizing much more
flexible.


--
Duane Hookom
MS Access MVP
--

I just would like a way of counting all presents across the whole
report.
I
suppose if I did it by column I could then add each column together to
get
the total.


The key values are as follows:
1 - Present
2 - Authorised Absence
3 - Unauthorised Absence
4 - Sick
5 - Holiday
6 - Not Required
7 - Late
8 - Bank Holiday

Im afraid I dont understand what you mean by not having day columns and
making it normalised. Could you explain please?


:

You haven't suggested if you want your totals under each column or do
you
want to add across columns. Also, I assume you store the primary key
from
the Attendance_Key table in each of the "day" fields. To total
Monday/Present, use and expression like:
=Sum(Abs(Attendance_Key_Monday = 1))

The number 1 above possibly relates to the value for Present. You
haven't
provided the key values for any of the descriptions.

I would not have an attendance table with "day" columns. I would
normalize
table.

--
Duane Hookom
MS Access MVP
--

Tables: -
LEARNERS(learner_id(pk), firstname, surname, address1, address2,
town,
county, postcode, contact_number, dob, gender, ethnicity,
healt_problems,
learning_difficulties, PA, start_date, leave_date, outcome,
accreditation,
warnings, notes)

Learners table is linked to:
ATTENDANCE(attendance_id(pk), learner_id(fk), attendance_key_monday,
attendance_key_tuesday, attendance_key_wednesday,
attendance_key_thursday,
attendance_key_friday)

the attendance_key fields use a look up for:
ATTENDANCE_KEY(attendance_key_id(pk), key_description)

The data I get from my report at the minute is:

Kevin Martin
Month W/E Monday Tuesday Wednesday Thursday
Friday

Nov-04
29/11/2004 N/A N/A Present
Present
Present
Dec-04
06/12/2004 Present Present Present Sick
N/A

13/12/2004 Sick Sick Sick Sick
N/A
20/12/2004 Absence Present Absence Absence
N/A
27/12/2004 Present Present Present N/A
N/A

What I would like to see is a total of instances of Present, Sick,
Absent
etc at the bottom of my report.

:

I would like to see
- actual table and field names
- some sample data from these table(s)
- expect results from this sample data

Do you really have fields with names that include days of the week?
If
so,
is there any chance that you can change the structure to be more
normalized?

--
Duane Hookom
MS Access MVP
--

in my database I have a number of learners and I also record
their
attendance
for each week that they are on the course. I have run a report
to
show
what
that learners attendance is over the time the learner was on the
course
and
broken it down into monthly periods. The report looks like this:

Month W/E Mon Tue Wed Thurs
Fri

Nov
29/11/04 N/A N/A Present Present
Present


Dec
06/12/04 Present Present Present Sick
N/A
13/12/04 Sick Sick Sick Sick
N/A
20/12/04 Absent Present Absent Absent
N/A

I tried the code but it didnt work. Every attendance type under
monday
is
called Attendance_Key_Monday(selected on a drop down) and there
can
be
many
instances depending on how long the learner is on the course.
Does
this
help
anymore? If you need more information just tell me what you
need.


:

Is this value by itself in field? If so,
=Sum(Abs([YourField]="Present"))
If this isn't your situation then you should provide more
information.

--
Duane Hookom
MS Access MVP


I have a report that shows attendances. I have instances of
Present,
Sick
and Absent. I would like to count the instances of each one
and
have
it
record the number on the report. Is there anyway of doing
this?