C
Carla Gilless
I have an employee training database and am trying to work out the best
solution for a complicated query. A typical scenario is below, with actual
field names in []:
The query must check for several conditions, then check a text field for a
specific class name and whether it is attended and then return a list of
required classes into an unbound text box on the query report
I thought of using IIF, but the conditions seem to complex for that. Would
Case Select or Do Loop work better?
Case1: If [PMLevel]=1 or 2 and [PMP Cert] is false and [PM Basic] is false,
then check [Attended] for [ClassName].
There are 3 classes in [ClassName] that must be met. If none attended, then
list the 3 classes that are required. If any of the 3 are attended, then
list only the remaining two required. PMP Cert, PM Basic and Attended are all
check boxes, PMLevel is a value list and ClassName is a text field.
The result I want is:
If [PMLevel]=1 or 2 and [PMPCert] is false and [PM Basic] is false and
[Attended] is false for "PM Basic", [Attended] is false for "Managing
Projects" and [Attended] is false for "Managing by Project Simulation", then
return all 3 courses in unbound field on report. If any of the attended
courses is true, then only return the remaining false ones.
I have 6 different scenarios to test for a return of 5 required courses.
Any help would be appreciated with clear SQL or VBA code, as I am still
learning code and SQL. And, of course, suggestions for some other solution.
Thanks.
solution for a complicated query. A typical scenario is below, with actual
field names in []:
The query must check for several conditions, then check a text field for a
specific class name and whether it is attended and then return a list of
required classes into an unbound text box on the query report
I thought of using IIF, but the conditions seem to complex for that. Would
Case Select or Do Loop work better?
Case1: If [PMLevel]=1 or 2 and [PMP Cert] is false and [PM Basic] is false,
then check [Attended] for [ClassName].
There are 3 classes in [ClassName] that must be met. If none attended, then
list the 3 classes that are required. If any of the 3 are attended, then
list only the remaining two required. PMP Cert, PM Basic and Attended are all
check boxes, PMLevel is a value list and ClassName is a text field.
The result I want is:
If [PMLevel]=1 or 2 and [PMPCert] is false and [PM Basic] is false and
[Attended] is false for "PM Basic", [Attended] is false for "Managing
Projects" and [Attended] is false for "Managing by Project Simulation", then
return all 3 courses in unbound field on report. If any of the attended
courses is true, then only return the remaining false ones.
I have 6 different scenarios to test for a return of 5 required courses.
Any help would be appreciated with clear SQL or VBA code, as I am still
learning code and SQL. And, of course, suggestions for some other solution.
Thanks.