Complicated query (Access03)

Topic

New Reply

I am trying to create a report that shows staff by department, listing all of the names in the department. That ends the easy part. Then I need to show their required competencies. I created a form where I created and manage the difference competencies, i.e. pressure ulcers, venipuncture etc. There is a sub form where I then pick the department on each competencies. I then have a check box for each type of credentials: RN, LPN, EKG tech etc, this is a Yes/No field. This field is checked if required for that credential level.

Now I need to have the computer first check the department to make sure the competency is needed for that person based on their assigned department. If the person is a RN it should then pull and list those that are marked Yes for RN. How do I match a text field to a yes/no field that is yes (-1)?

Thank you. Fay

Reply | Quote

Replies

I’m finding it difficult to visualize it. Could you provide more information about the tables involved, and if possible attach a stripped down and zipped copy of the database?

Reply | Quote

Here is a stripped down version. It will come up showing the form where I input the different competencies and then assign to the department and to the credential levels. I started the query qryCompetenciesRequired, but it just selects the departments and names so far.

I need to show by departments (tblLearnerDepartment) assigned staff by name (tblLearner). Showing required competencies for each person based on their department and and credential level. Eventually I will then need to show their grade and date tested, but I need help on the step above.

tblCompetenciesDept holds the information from the subform.
tblRegIndepStudyLearner really addresses the last step, so I guess not needed here.

Looking at the form you see that required for a credential level are marked with a check mark. If you look at the tblLearnerDept you will see that credentials is in a text field. I have to marry up the these two a yes for an RN should show up under all RN names.

I hope this helps. Thank you for looking at this. Fay

Reply | Quote

The structure of tblCompetenciesDept is unfortunate. It is virtually impossible to do what you want with it.

Instead of having a separate Yes/No field for each credential, there should be a separate record for each required combination of Department, Competency and Credential. In other words, if a Department/Competency combination now has 3 fields with Yes, there would be 3 records in the new structure, and if a Department/Competency combination has 4 fields with Yes, there would be 4 records for this combination in the new structure. It would look as in the screenshot below.
(It would be even better to have numeric IDs for the departments and for the credentials)

Reply | Quote

I was kind of expecting some kind of answer like that. Back to the drawing board. At least I am just getting started with it. How are you envisioning the combo number for department and credentials. Create a separate table where I create the numbers for the department/credential combinations. Then link it to where I input learners. Is what I am hearing. Thanks for the information. Fay

Reply | Quote

No, you have three “basic” tables:
– Departments
– Competencies
– Credentials
each with an AutoNumber ID field.
Then there is a table that contains combinations of these three IDs, as in the screenshot in my previous reply. The primary key in this table is a composite index consisting of the three IDs.

Reply | Quote

Thank you. Fay

Reply | Quote