Hopeless relationship - my first post..

[petercoleman]

I am working on a database to track training of staff. I have one table of all staff and their roles. I have one table of all exams and the roles required to take them. I use a join table to enter what staff have done what exams. I use portals on the staff layout to see what exams should be done and portals on the exam layout to see who should do them. All this is basic so far. What I want to do is see a list of all the exams a person is required to take and somehow see which ones they have actually taken (with something like conditional formatting) I cannot work out a relationship to make this happen. Thanks ;)

[LaRetta]

Would something like this (attached) assist you? View Staff layout with portal to all available exams for a staff person with this role. And conditional shows (in red) if s/he has taken the exam.

However, I feel we're missing a piece of what you need. It seems that a filtered portal shows, checkbox with: exams taken or exams not taken or both (based upon the role) might work better than conditional formatting? Anyway, I hope it moves you forward a bit and possibly others can chime in with ideas. 8^)

StaffTraining.zip

[petercoleman]

Thanks for the quick reply! That was the effect I was looking for but your suggestion of a portal filter maybe more ideal. The final product should allow a manager to see what exams his staff have taken. Some exams are one offs, while other are taken annually or some other time period. They need to generate a report that shows which staff are up to date on which exams. I think I can play with what you have done and get it happening, but any better ways to achieve the goal would be great. One issue you may be able to do better than me is creating a portal with completed and current ie "in date" exams. My method is a script to find staff and exam, then sort by descending date to give me the latest exam, then flag a field in the first record. A portal filter on this flag gives me the result. I prefer to use calculations for "calculations" like this. This method is a little bit clunky for me. Thanks again.

[LaRetta]

My method is a script to find staff and exam, then sort by descending date to give me the latest exam, then flag a field in the first record.

Hi Peter, I wouldn't flag a field nor use a script. If you have dates and want to filter by those dates (or those records without dates), you can do so, using a global field but please provide us with a basic, simple example of your structure with one or two sample records from which to work.

A sample file from which to start will help us climb into your solution without exchanging 50 questions.

UPDATE: "They need to generate a report..." okay, I was going in the direction of displaying possible exams when viewing a student record. I won't have time to consider this right now particularly without a sample file so hopefully others will help out. It sounds like a find with sub-summary report will work then but still ... a sample file will help quite a bit.

Edited March 31, 2010 by Guest

[petercoleman]

The report is easy because I can use sub-summaries. For the GUI, I want to be able to look at a list of all the exams a person should do, and see what is current.

I have attached your file with the problem. Bob has done an exam 3 times, but I want to see that it is not overdue. Only the first date will appear in the portal.

and thanks again..

Edit: I thought it was there but I have a problem with the file you posted. Each role is not unique, and if it enter an exam for one person, everyone in that role is also marked as having done the exam.

damm

StaffTraining.fp7.zip

Edited April 1, 2010 by Guest

[LaRetta]

Bob has done an exam 3 times, but I want to see that it is not overdue. Only the first date will appear in the portal.

I'm not sure I understand but it seems you don't want to see Exams each staff person hasn't taken that they might want to take? Proceeding on that assumption, I created the attached revised file which only shows exams a staff member has in the Training table.

I didn't know if you would want to toggle to see all their training or only the current so the first portal toggles between all their training records and only those still current (click the yellow button).

The second portal uses vs. 11 and filters the portal based upon only those training still current. Note also that the expired lines in the first portal use conditional format on straight text (this saves creating a calculation to display the words).

Are we getting closer? If not, keep explaining and we'll get there. :laugh2:

StaffTrainingREV.zip

[petercoleman]

Its late now so its hard to concentrate. Thank you again for your effort. I suppose I really need to get several bits of info from the file. The goals are:

1. To see that all staff have taken all the exams they are supposed to

2. To make sure that that any exams that need to be renewed are flagged to be taken again.

3. To be able to enter new and updated exams.

This is for a health care centre and, for example, Nurses are required to undertake various exams and tests. The manager for a section of the centre should be able to look at the card for one staff member and see what they, should do, what they have done and is it all in date. I visual a simple card layout with a 4 tabs showing a portal in each with "Required", "Not Done", "Completed" and "Overdue". Maybe my initial structure is wrong.

Reports are easy and maybe I need to generate those instead. The portals are easy and the only thing that I can't work out I show to show what is required, but they have not done. You previous file looked perfect, but did work with roles done by multiple staff.

Forgive me for my rant but I will look at the new file in the morning. Again, thanks for your time.