Return certain data from Detail area

[wlr20]

Our group requires that we participate in 8 training sessions per year, of
which we are required to attend two of three particular sessions. I want to
create a report to keep up with this information.

In a table I have the 22 offered training sessions numbered 1 - 22 in a
field named: TrainNo - training sessions 2, 18, 21 are the sessions that
each member has to choose two to attend. I want to produce a report that
shows the number of training session each of our group has attended, and also
if they have attended two of the three particular sessions. I've been able
to do all but the last part.

I have a query where I have pulled my needed data from 2 tables. In my
report I am using the following fields: GroupMem, CountOFTrainSess, TrainNo.
I have the group header GroupMem Header, where I display the member's
name, number of training sessions, and I want to add 3 more fields to this
header where it will display "Yes" if the member has attended a certain
required session.

In the Detail area there is: GroupMem, CountOFTrainSess, TrainNo.

I have tried in my text box in the GroupMem header the following code:
=IIf([TrainNo]=2,"Yes","") This works only if training session 2 is the
first item in that members list in the Detail area - everyone who attended
sessions 1 and 2 show empty space because 1 is the first thing listed.

How can I get it to search through a member's detail info and return a Yes,
regardless of where TrainNo 2 falls in their detail list?

Once I get the code for first field figured out and working properly I
intend to add the other two fields that will have similar code as the first,
except I'll be looking for TrainNo 18 and TrainNo 21.

Your help will be so greatly appreciated!

 

[[MVP]]

Our group requires that we participate in 8 training sessions per year, of
which we are required to attend two of three particular sessions.  I want to
create a report to keep up with this information.  

In a table I have the 22 offered training sessions numbered 1 - 22 in a
field named:  TrainNo - training sessions 2, 18, 21 are the sessions that
each member has to choose two to attend.  I want to produce a report that
shows the number of training session each of our group has attended, and also
if they have attended two of the three particular sessions.  I've been able
to do all but the last part.

I have a query where I have pulled my needed data from 2 tables.  In my
report I am using the following fields:  GroupMem, CountOFTrainSess, TrainNo.
  I have the group header  GroupMem Header, where I display the member's
name, number of training sessions, and I want to add 3 more fields to this
header where it will display "Yes" if the member has attended a certain
required session.  

In the Detail area there is:  GroupMem, CountOFTrainSess, TrainNo.

I have tried in my text box in the GroupMem header the following code:  
=IIf([TrainNo]=2,"Yes","")    This works only if training session2 is the
first item in that members list in the Detail area - everyone who attended
sessions 1 and 2  show empty space because 1 is the first thing listed. 

How can I get it to search through a member's detail info and return a Yes,
regardless of where TrainNo 2 falls in their detail list?  

Once I get the code for first field figured out and working properly I
intend to add the other two fields that will have similar code as the first,
except I'll be looking for TrainNo 18 and TrainNo 21.

Your help will be so greatly appreciated!

Hi,

If I understood correctly, I think you will need to filter data in the
report source query, i.e. you can filter there TrainNo "=2" in Query
Criteria and change it to 18 and 21 later to get data for other 2
sessions.

To make it a bit user friendly, instead "=2" type in Criteria field
"[Enter Training Number]" (without quotes), so when you run report
enter either 2, 18 or 21 - or any other number. Hope this help.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Duane Hookom]

You can get a count of the number of times 2, 18, or 21 appear within a group
by using a text box in the group footer with a control source of:
=Abs(Sum([TrainNo] In (2,18,21)))
If you want to find out if TrainNo 2 has been taken then
=IIf(Sum([TrainNo] = 2)<>0,"Yes","No")

I hope this gets you pointed in the right direction.

 

[wlr20]

Thank you!!! That did exactly what I needed.

Have a great weekend!

You can get a count of the number of times 2, 18, or 21 appear within a group
by using a text box in the group footer with a control source of:
=Abs(Sum([TrainNo] In (2,18,21)))
If you want to find out if TrainNo 2 has been taken then
=IIf(Sum([TrainNo] = 2)<>0,"Yes","No")

I hope this gets you pointed in the right direction.

--
Duane Hookom
Microsoft Access MVP

Our group requires that we participate in 8 training sessions per year, of
which we are required to attend two of three particular sessions. I want to
create a report to keep up with this information.

In a table I have the 22 offered training sessions numbered 1 - 22 in a
field named: TrainNo - training sessions 2, 18, 21 are the sessions that
each member has to choose two to attend. I want to produce a report that
shows the number of training session each of our group has attended, and also
if they have attended two of the three particular sessions. I've been able
to do all but the last part.

I have a query where I have pulled my needed data from 2 tables. In my
report I am using the following fields: GroupMem, CountOFTrainSess, TrainNo.
I have the group header GroupMem Header, where I display the member's
name, number of training sessions, and I want to add 3 more fields to this
header where it will display "Yes" if the member has attended a certain
required session.

In the Detail area there is: GroupMem, CountOFTrainSess, TrainNo.

I have tried in my text box in the GroupMem header the following code:
=IIf([TrainNo]=2,"Yes","") This works only if training session 2 is the
first item in that members list in the Detail area - everyone who attended
sessions 1 and 2 show empty space because 1 is the first thing listed.

How can I get it to search through a member's detail info and return a Yes,
regardless of where TrainNo 2 falls in their detail list?

Once I get the code for first field figured out and working properly I
intend to add the other two fields that will have similar code as the first,
except I'll be looking for TrainNo 18 and TrainNo 21.

Your help will be so greatly appreciated!