How do I (Access03)

Topic

New Reply

I have a list of classes some of which are marked as required. I have a combo box that I use to assign requried classes to a specific person. I also have a list of classes that the person has attended. Then I needed it to show when the person attended the class on their selected list of required classes. That is working. Now what I need it to show a class that has no attendence record. In other words I need the following

ACLS 2/10/09
BLS 1/25/09
CCC
EKG 10/25/07

This way I know the requirement hasn’t been met.

The second issue I have is the combination of 2 classes to be reported as one. In other words there are two classes ACLS and ACLS Recert. The one is a two day class the other is a one day recertification. I want to be able to require ACLS and then if the person took the ACLS Recert class it would be reported as ACLS. Is there a way to do that?

Thank you.

Fay

Reply | Quote

Replies

I can’t answer your first question without knowing more about the structure of the tables involved.

For the second issue, I think I’d add a new field to the classes table, say RelatedID, that will be blank for most records, but that can hold the ID of a related class, e.g. for the ACLS Recertification class, it would contain the ID of the ACLS class.
You can then create a query based on two copies of the classes table with a left join on RelatedID vs ID, and add a calculated column that returns the name of the related class if available, and that of the class itself otherwise.

Reply | Quote

Thanks Hans. I will review your answer on question two. I have attached a stripped down version of the material. The qryRequiredResults is the query that needs to show the class without a date.

Reply | Quote

Open qryRequiredResults in design view.
Double click each of the join lines in turn and select the option to return ALL records from tblRequiredLearners and only related records from qryCombRequired3. It is important to do this for both join lines.

Reply | Quote

Thanks Hans that did what was needed for the first question. I will turn my attention to the second question. I appreciate your help. Fay

Reply | Quote