Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Complicated query (Access03)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated query (Access03)

    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?

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complicated query (Access03)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated query (Access03)

    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)

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complicated query (Access03)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Complicated query (Access03)

    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.

  7. #7
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Complicated query (Access03)

    Thank you. Fay

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •