Problem with multiple selection query

[Rich_in_NZ]

(Using XP, Access 2003)

Hi,

I have a database that tracks laboratory staff members training at work.
Each individual competency training record (in this case a 'competency
training record' is a level of completion for a particular test method) is
split into 1-5 sections, depending upon preset requirements. Once a person
has obtained each of the required sections, they are deemed to have obtained
that competency training record. These sections are rated on a 1-4 scale and
have an assessment date and validity period.

Next there are a set of credits that can be obtained, which are made up of a
group of training records. These groupings have been defined in the same way
that the required sections of each competency training record has.

I want to be able to display all the staff, with a column for each credit
and indicate whether or not the staff member has obtained that credit.

The criteria for obtaining a particular credit is:
The staff member has obtained each competency record required for the given
credit.
In order to have obtained that competency record, each required section must
be completed, be current, and be to a level 4.

I’m sorry for the essay, but I’m stuck and don’t now how to attack this. Any
direction or advice on where to begin would be great.

Thanks,
Rich.

 

[Rich_in_NZ]

Hi Dale,

I actually already have the tables you suggested, with the credits/unit
standards mapped to the individual training records, I’m just unsure how to
use that to my benefit. I’m thinking that I may need to map the sections
applicable to each training record in that table also, but ideally don’t want
to do it that way in case something changes down the track. I would prefer to
have it query which sections are available for each record, not define them.

The Training Records table has quite a few fields, but the important fields
in the table are:
RecordID – text (PK)
And the required sections:
Media – Number (linked to a checkbox on a form when the training records
were defined)
Testing – Number
Confirmation – Number
ReadRecord – Number
Theory – Number
Practical – Number

Required sections for each training record = -1, 0 for not required.

The table that tracks staff members’ assessment is called AllRecords, which
is made up of:
AllRecordaID – Autonumber (PK)
EMP# - number (Employee number PK of staff table)
RecordID – from Training Record table
Section – text (one of the 6 sections from the TR table above)
Level - Number
Assessment Date - date
Re-assessment Period – Number (indicates the number of months the assessment
is valid)
As well as some additional columns that would have no bearing on the query.

I use (DateAdd("m",[Re-assessment Period],[Assessment Date])>=Date()) to
determine whether a record in AllRecords is current.

An example record from AllRecords would be:
AllRecordaID – “123â€
EMP# - “123456â€
RecordID – “202.604.103-0655â€
Section – “Practicalâ€
Level – “4â€
Assessment Date – “15-Jun-09â€
Re-assessment Period – “12â€

I hope there is enough information here for you to understand the structure,
please let me know if you require more.

Thanks for your help,
Rich.

 

[Rich_in_NZ]

Hi Dale,

Thanks for your suggestions. With your help I have now figured it out. Sorry
for the confusion between field names in tables. The 'scale' I referred to is
actually called 'Level' in the AllRecords table. Basically, regardless of
whether a staff member had a 'current' training record section, unless it was
to a level 4 then it wasn't obtained.

My solution was to have one query that checks if each section is required.If
it is, checks to see if a staff member is current and to a level 4, then
assigns 1 if it's required and current to level 4, 0 if it's required but not
current and to level 4, or null if it's not required. The last column is a
expression based on the section checks, as long as none of the sections = 0,
then the staff member has obtained that training record.

A second query checks the first query against the mapped credits to
determine if the staff member has obtained the credit.

Thanks again for your help, your direction saved me a lot of frustration!

Cheers,
Rich

Rich,

In your original post you stated "These sections are rated on a 1-4 scale and

have an assessment date and validity period".

What role does this rating have in the process, and where is that field
located?

I'm also having a bit of difficulty understanding the fields in your
"TrainingRecords" and "AllRecords" tables, as they relate to your original
post. However, I'll push through this and see what we can come up with.

The first thing I would do is develop a aggregate query based on the
"Training" and "All" records tables which tells me, for each employee, the
Sum of the sections completed (1-5) that are still current. Assuming I'm
using the right fields, it would look something like:

Query1:
SELECT AR.Emp#, TR.Testing, Sum(AR.Section) as ValidSections
FROM TrainingRecords as TR
INNER JOIN AllRecords as AR
ON TR.RecordID = AR.RecordID
WHERE (DateAdd("m",AR.[Re-assessment Period],AR.[Assessment Date])>=Date())
GROUP BY AR.Emp#, TR.Testing
HAVING Sum(AR.Section) = 15

Assuming that there are 5 sections (1-5) for each "test", as you indicated in
your original post, if you sum the Section #'s, they should add up to 15 (if
all the sections are done and still valid). If not, then that number will be
less than 15, and you don't want to include that Emp/Test combination in the
results of this query.

Next, I would join this query, with the Credit_Competancy table from my
original post to determine for each employee, what competancies they have
relating to each "credit". Assuming that the "Competancies" equate to your
"Testing" field, this might look like:

Query2:
SELECT Q.AR_Emp#, CC.CreditID, Count(CC.CompID) as CompletedComps
FROM Query1 as Q
INNER JOIN Credit_Competancies as CC
ON Q.Testing = CC.CompID
GROUP BY Q.AR_Emp, CC.CreditID

Next, I would write a query that identifies for each credit, how many
competancies are required. This query would be used to determine whether a
specific individual has all of the competancies for a particular credit.

Query3.
SELECT CreditID, Count(CompID) as RequiredComp
FROM tbl_Credit_Competancies
GROUP BY CreditID

Finally, you can join Query2 to Query3 to identify which credit each employe
has. Something like:

SELECT Q2.Emp#, Q2.CreditID
FROM Query2 as Q2
INNER JOIN Query3 as Q3
ON Q2.CreditID = Q3.CreditID
AND Q2.CompletedComps = Q3.RequiredComps

Once you get this part working, you can add the Employee and Credits tables
so that you can actually determine the employee name and the credit name as
well.

HTH
Dale

Hi Dale,

I actually already have the tables you suggested, with the credits/unit
standards mapped to the individual training records, I’m just unsure how to
use that to my benefit. I’m thinking that I may need to map the sections
applicable to each training record in that table also, but ideally don’t want
to do it that way in case something changes down the track. I would prefer to
have it query which sections are available for each record, not define them.

The Training Records table has quite a few fields, but the important fields
in the table are:
RecordID – text (PK)
And the required sections:
Media – Number (linked to a checkbox on a form when the training records
were defined)
Testing – Number
Confirmation – Number
ReadRecord – Number
Theory – Number
Practical – Number

Required sections for each training record = -1, 0 for not required.

The table that tracks staff members’ assessment is called AllRecords, which
is made up of:
AllRecordaID – Autonumber (PK)
EMP# - number (Employee number PK of staff table)
RecordID – from Training Record table
Section – text (one of the 6 sections from the TR table above)
Level - Number
Assessment Date - date
Re-assessment Period – Number (indicates the number of months the assessment
is valid)
As well as some additional columns that would have no bearing on the query.

I use (DateAdd("m",[Re-assessment Period],[Assessment Date])>=Date()) to
determine whether a record in AllRecords is current.

An example record from AllRecords would be:
AllRecordaID – “123â€
EMP# - “123456â€
RecordID – “202.604.103-0655â€
Section – “Practicalâ€
Level – “4â€
Assessment Date – “15-Jun-09â€
Re-assessment Period – “12â€

I hope there is enough information here for you to understand the structure,
please let me know if you require more.

Thanks for your help,
Rich.

Rich,

[quoted text clipped - 38 lines]

Thanks,
Rich.

--
HTH

Dale Fye

Message posted via AccessMonster.com