IIf over all records in group
I have a report that is based on a query that generates multiple records for each individual, one for each training they have participated in. The data is normalized...there is only one record for each individual in the "name list" table, and one record for each training in the "activities" table, and the 2 are linked by SSN. These 2 tables are brought together to create my query.
I am trying to create a report that lists the Names of the individuals, and for each, lists the trainings I am interested in and the dates that they were completed, so that at a glance, I can see which ones are missing dates. Since not every person has every training (or I would not need this report), I have created a list of labels with the names of the trainings, and a control for each with the following to display the dates:
=IIf([Activity] Like "*Training Name*" And [txtSSN]=[SSN],[Date],""
[txtSSN] is a field in the report in the SSN group, (so that all the training records for a given SSN can be displayed)
[SSN] is a field in the record documenting the training in the query
[Date] is a field in the record documenting the training in the query
I'm sure I am missing something very simple, but I cannot get this list to work. If I put it in the header, only the 1st training in the database for that individual has a date. If I put in in the detail section, the whole list prints once for every training, with only the date for that training. If I only wanted the trainings that have been completed, I could make this work. However, I also want it to be very clear which trainings are MISSING also by using the blank date field.
Can anyone offer a suggestion? Thanks.
I have a report that is based on a query that generates multiple records for each individual, one for each training they have participated in. The data is normalized...there is only one record for each individual in the "name list" table, and one record for each training in the "activities" table, and the 2 are linked by SSN. These 2 tables are brought together to create my query.
I am trying to create a report that lists the Names of the individuals, and for each, lists the trainings I am interested in and the dates that they were completed, so that at a glance, I can see which ones are missing dates. Since not every person has every training (or I would not need this report), I have created a list of labels with the names of the trainings, and a control for each with the following to display the dates:
=IIf([Activity] Like "*Training Name*" And [txtSSN]=[SSN],[Date],""
[txtSSN] is a field in the report in the SSN group, (so that all the training records for a given SSN can be displayed)
[SSN] is a field in the record documenting the training in the query
[Date] is a field in the record documenting the training in the query
I'm sure I am missing something very simple, but I cannot get this list to work. If I put it in the header, only the 1st training in the database for that individual has a date. If I put in in the detail section, the whole list prints once for every training, with only the date for that training. If I only wanted the trainings that have been completed, I could make this work. However, I also want it to be very clear which trainings are MISSING also by using the blank date field.
Can anyone offer a suggestion? Thanks.
Last edited:
