RE: VBA or SQL

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Pendragon,
Thanks for taking a look, but actually I do want a blank as my else part.
If the statement is true (or the required class has not been attended) then I
want the string to appear listing the required class name, else enter
nothing. That part works fine. I've tried this several different ways,
creating first a query that selects the population to be active PM's, then a
subquery that lists classes attended and entering the IIF's into the
subquery. I've tried changing the IIFs to search for a required classname.
The results are the same. The part that has me stumped is how to program to
process one IIF until the condition is met, then move on to the next IIF
without having to run through the entire database each time. The result I get
from the IIF's now shows student A as needing req 2, 3 and 4 even though they
have been attended. (edited datasheet result below)

Student A:
ClassName
Facilitation - Getting Results Fast
Req1
Req2 Managing Projects at BSC
Req3 Managing by Project Simulation
Req4
Req5 Leading High Performing Teams Simulation

ClassName
Managing Projects at BSC
Req1
Req2
Req3 Managing by Project Simulation
Req4 Facilitation - Getting Results Fast
Req5 Leading High Performing Teams Simulation

ClassName
Managing by Project Simulation
Req1
Req2 Managing Projects at BSC
Req3
Req4 Facilitation - Getting Results Fast
Req5 Leading High Performing Teams Simulation

ClassName
Process Execution Capability Overview
Req1
Req2 Managing Projects at BSC
Req3 Managing by Project Simulation
Req4 Facilitation - Getting Results Fast
Req5 Leading High Performing Teams Simulation

--
Carla


"Pendragon" wrote:

Carla,

Not an MVP, but I work with a database that tracks this kind of "required
classes" process for an industry certification. My set up uses a few
different tables with established relationships, and quite a bit of VBA to
sort through the tree structure of the certification requirements (we have
two, sometimes three levels - like Cert A has three parts, each part is pick
two of these four, etc., etc.).

What I see in your SQL statement is that your IIFs do not have the ELSE part
of the statement. For example, in the first IIF statement (as Req1), if this
is true

Students.PMLevel In ("1","2") And Students.[PM Basics
Completed]=False And Students.[PMP Certificate]=False

then your value is going to be "PM Basics". But what value do you want in
the field if the Condition is not true? Right now, because you haven't
defined the ELSE statement, your field is blank.

Revised: IIf(Students.PMLevel In ("1","2") And Students.[PM Basics
Completed]=False And Students.[PMP Certificate]=False,"PM Basics", [WHAT DO YOU WANT IF THE CONDITION IS FALSE])

Truthfully, I think you need to break up your query into parts. Have a query
that gets your students and classes, have a query that lists the classes and
requirements, and then an outer join query (B not in A) that gives you
students and the classes needed (or not attended).

Your report could be a list of students and the classes attended, with a
group heading on the Student ID (or whatever unique ID you are using), and
have a subreport for the classes needed in the group footer with a Student ID
Parent-Child relationship.

Just some ideas.

"Carla Gilless" wrote:

In an employee training database, I am trying to produce a report that
searches for 5 required courses and if the course hasn't been attended, list
the course under a separate heading as required (the report also shows all
attended courses whether they are required or not). I've created a select
query that essentially does what I want, with 5 separate IIF statements, (SQL
below) except that it doesn't stop when one of the required courses has been
attended, so every course attended either lists the required course or
produces a blank in the Req. section of the datasheet. In the report, I then
get courses listed as required even though they have been attended. I'm sure
there is a more elegant solution in either SQL or VBA but I need help with
the coding and deciding whether to use if, then else statements or Select
Case, or something else entirely....
Basically the report needs to list all the courses attended, then check for
the required courses, and return a list of the required courses that have not
been attended. Any suggestions, or better yet coding examples, would be
greatly appreciated.

PM Required Query:
SELECT DISTINCT ClassName.ClassName, Classes.Date, [Students And
Classes].Attended, Students.LastName, Students.FirstName, Students.[PM Basics
Completed], Students.BU, Students.PMLevel, Students.ActivePM, Students.[PMP
Certificate], IIf(Students.PMLevel In ("1","2") And Students.[PM Basics
Completed]=False And Students.[PMP Certificate]=False,"PM Basics") AS Req1,
IIf(Students.PMLevel In ("1","2","3") And [PMP Certificate]=False And
ClassName.ClassName Not In ("Managing Projects at BSC"),"Managing Projects at
BSC") AS Req2, IIf(Students.PMLevel In ("1","2","3") And [PMP
Certificate]=False And ClassName.ClassName Not In ("Managing by Project
Simulation"),"Managing by Project Simulation") AS Req3, IIf([PMLevel]="3" And
ClassName.ClassName Not In ("Facilitation - Getting Results
Fast"),"Facilitation - Getting Results Fast ") AS Req4, IIf([PMLevel] In
("4","5") And ClassName.ClassName Not In ("Leading High Performing Teams
Simulation"),"Leading High Performing Teams Simulation") AS Req5,
Students.ActivePM
FROM (ClassName INNER JOIN Classes ON ClassName.ClassNameID =
Classes.ClassName) INNER JOIN (Students INNER JOIN [Students And Classes] ON
Students.EmplID = [Students And Classes].EmplID) ON Classes.ClassID =
[Students And Classes].ClassID
WHERE ((([Students And Classes].Attended)=True) AND
((Students.ActivePM)=True))
ORDER BY Students.LastName;

Tables are: ClassName, Classes, Students and Classes, Students
--
Carla
.

Relevant Pages

  • RE: VBA or SQL
    ... I think you need to break up your query into parts. ... that gets your students and classes, have a query that lists the classes and ... students and the classes needed. ... attended courses whether they are required or not). ...
    (microsoft.public.access.modulesdaovba)
  • Re: Excel Challenge (prize - she keeps her job!)
    ... Column A lists Courses ... B3:G9 lists # students in daytime groups ... > in each group for the C courses. ...
    (microsoft.public.excel.misc)
  • Re: Prolems in Mathematics Education
    ... mathematics courses, and claim to have taken a calculus course in high ... Most local students taking AP Calculus do not bother to actually ... they are put into one of our basic math ... Our university college dean (Ed.D. ...
    (sci.math)
  • Re: Sarah Palin- creationist VP candidate?
    ... there will be many high school students ... mathematics is, for whatever reason, necessary for higher learning. ... algebra-free statistics courses have given very poor results, ... is quite bright) was unable to pass high school algebra, ...
    (talk.origins)
  • Prolems in Mathematics Education
    ... mathematics courses, and claim to have taken a calculus course in high ... yet place into our Intermediate Algebra (basically an Algebra I ... Most local students taking AP Calculus do not bother to actually ... Our university college dean (Ed.D. ...
    (sci.math)