Query question - how to display an expression field if certain criteria is met? (1 Viewer)

[lithium1976]

Hello, I need to create a query which shows when users have completed at least two or three particular tasks out of a bigger amount of tasks.

I have a table displaying users details and the tasks they have completed to allow them to be given a certificate.

There are about 8 tasks in total but users only need complete at least two of them and also hopefully a complete a third task if possible as a bonus to get a certificate. To gain a "Certificate" status the tasks to complete are "1" and "2" but also and if possible they can also complete task "3" to get a certificate.

For example, if a user completes task number 1 and 2 then the expression field will say "Certificate". And, if another user completes tasks 1, 2 & 3, the expression field will also say "Certificate".

If a user completes tasks 1 & 3 but not task number 2, then I want "No Certificate" to be displayed in the expression field. If they haven't completed any of tasks 1, 2 or 3, then I don't want them to be displayed at all.

I don't know the best way to approach this, would it be an expression field with an IIF statement within the query, I'm not sure how to do this.

 

[Mark_]

Just to be clear, if the user has completed 1 AND 2, show certificate? 3+ don't come in to this as they MUST have 1 and 2?

How are tasks 1 and 2 saved? Date fields? Boolean?

 

[lithium1976]

It is a bit confusing,

If they complete 1 & 2 then they get a certificate.

If they complete 1, 2 & 3 then they get the same certificate for reasons beyond this thread! (Basically the certificate will get an extra hand-written signature on it).

If they complete 1 & 3, then no certificate.

If they complete 2 & 3 then no certificate.

If they complete say 4 & 5 then no certificate.

The tasks are actually text fields, but I used numbering to make it easier to understand. Or, that’s what I hoped haha.

 

[Mark_]

So you are actually asking

IIF( Field1 <> "",IIF( Field2 <> "","Certificate","No Certificate"), "No Certificate")

IF you have a value in field one,
...IF you have a value in Field two, print "Certificate", else print "No Certificate"
else print "No Certificate"

Personally I don't like nesting IIF structures as they become difficult to read.

 

[lithium1976]

If they have done tasks 1 & 2 print certificate. And if they have done 1,2 & 3 print same certificate. Anything else, don’t print certificate.

What would be a good alternative to a nested IIf statement?

 

[Mark_]

Are you familiar with creating public functions?

In a query, having nested IIF statements often makes it difficult to follow exactly what you are trying to do. If you had a public function to return your text, you could replace the code with

exp: MyPublicFunction(Field1,Field2)

By giving it a decent name you can easily tell what you are trying to do, so it could look like

TxtCertificate: CertQual(Field1,Field2)

and you'll more easily remember what you were doing.

For single results, an IIF work fine. Once you start checking more than one field (or have more than one possible answer) moving it to a function makes life much easier for you, the programmer.

 

[lithium1976]

Unfortunately I’ve never made a public function. I’ve done basic run sql commands in VBA and including basic if statements , but never a public function.

 

[Mark_]

Did the code sample I put up show you how to do what you needed?

 

[lithium1976]

I haven’t had chance to try it yet, I will when I’m back at my laptop in the morning.

 

[lithium1976]

I've just tried the IIf statement, I have created a table at home, with tasks. But the statement produces "certificate" for every task.

 

[Mark_]

Can you post the exact IIF you are using?

 

[lithium1976]

This was my first attempt

Certificate:IIf([Task]="1","CERTIFICATE",IIf([Task]="2","CERTIFICATE",IIf([Task]=“3”,”CERTIFICATE”,"No Certificate")))

 

[Pat Hartman]

I'm going to assume that the table is normalized and that each task is a separate row. I would create two queries and then join the two queries. I'm sure this can also be done with subqueries but the separate queries with the join may be easier to understand and reproduce.

query1 - get the people who completed the basic requirements
Select PersonID, LastName, FirstName, Count(*) As TaskCount
From YourTable
WHERE Task In (1, 2)
Group By PersonID, LastName, FirstName
HAVING Count(*) =2;

query2 - get the people who completed any extra requirements
Select PersonID, LastName, FirstName, Count(*) As ExtraTaskCount
From YourTable
WHERE Task NOT In (1, 2)
Group By PersonID, LastName, FirstName;

Final query - use this as the recordsource for your certificate report.
Select q1.PersonID, q1.LastName, q1.FirstName, q2.ExtraTaskCount
From query1 as q1 Left Join query2 as q2 On q1.PersonID = q2.PersonID;

On your Certiricate, you can print "with distinction" or something if ExtraTaskCount is not null.

 

[Mark_]

If Pat's answer does not help, please post your table structure so we know what you are working with. I was under the impression you had one field for each "Task" based on how you originally asked the question.

 

[lithium1976]

The table structure (at least for trying to work it out) is as simple as possible,

personID (Primary Key), (Autonumber)
LastName (short text)
FirstName (short text)
Task (Number)
TaskDate (Date/Time)

In the screen shot, Mike Smith get a certificate (for completing the tasks 1,2 and 3), Dave Jones doesn't get one (he's only done task 1), John Roberts gets a certificate (for completing tasks 1 and 2), Billy Williams doesn't get one (He has done task 4).

 

[Pat Hartman]

My example will solve the problem. If you are not fluent with SQL, just create three separate queries as I have described them. Test the first two individually and then check the join.

 

[lithium1976]

I cannot get the first query to return any results. If I use the example table and it’s data, the first query will run, but no results are displayed, just the headings. I've attached the example database.

 

[isladogs]

As each person may have more than one task completed, the data needs to be split into 2 tables with a one to many relationship: tblPerson & tblTask

Your requirement then becomes fairly simple:
I've done it in 2 queries to make it easier to follow but other solutions are possible
a) qryTasks1&2Completed - identifies people who've done both tasks
b) qryPersonCert - links tblPerson & the above query with an outer join to get your result

See attached

 

[Pat Hartman]

Because the tables are not defined as I expected them to be, you can't include PersonID. I removed personID and changed the final join. I expected PersonID to be the id of the person. in the sample you have build it is the ID of the completed task.

The solution Colin proposed works if you don't care about the extra tasks. If you care, you should probably use what I suggested. Colin was nice enough to fix up the tables to be more of what we expected.

 

[lithium1976]

Thank you, I will take a look at the database this afternoon.