[Solved] Query criteria using AND on the same field

Creating tables and queries

[Solved] Query criteria using AND on the same field

Postby AmyBUK » Tue Nov 13, 2018 2:16 pm

Hi

I have a query, which I would like to only show a list of all staff who have completed three different training courses. I have attached the relationships. I would like to know who has completed CourseTitle: Asbestos Awareness, Facefit SR100 and Asbestos Medical, but I'm struggling to get a list.

To make it more difficult, the Asbestos medical has an expiry date, so I would only like those who's course is active in the list too.

Is this something that I can even do within Base?

Thanks for any help in advance.
Attachments
Training Relationships.PNG
Relationships
Last edited by Hagar Delest on Tue Dec 11, 2018 9:21 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby eremmel » Tue Nov 13, 2018 10:47 pm

What about:
Code: Select all   Expand viewCollapse view
SELECT OL."Surename", CL."CourseTitle"

FROM "CourseList" CL
   INNER JOIN "Training Log" TL ON CL."CourseID" = TL."CourseID"
   INNER JOIN "Ops List" OL ON TL."OpID" = OL."OpID"

WHERE
   CL."CourseTitle" ='Asbestos Awareness'
   OR CL."CourseTitle" ='Facefit SR100'
   OR CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE

Did not validate the SQL in all details, but this might help to find your way.
 Edit: Literals with single quotes iso of double, thanks to Sliderule 
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 10:57 am

Thanks, this really helps me. However, it gives the list of everyone who has one, two or three of them. I would like a list of people who only have all three. Is that possible?
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby robleyd » Wed Nov 14, 2018 11:03 am

Replace the OR with AND

Code: Select all   Expand viewCollapse view
WHERE
   CL."CourseTitle" ='Asbestos Awareness'
  and CL."CourseTitle" ='Facefit SR100'
   and CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE


 Edit: Incorrect advice 
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2634
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 11:09 am

That gives me no one on the list, but there are people that have all three records under their name?
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby eremmel » Wed Nov 14, 2018 11:48 am

Try experimenting with your query:
1: Note a guy that has all three
2: Remove both conditions with AND and validate that guy is listed
3: Add one of the two conditions and validate the guy again
4: Repeat step 3 till it is failing. Now examine your database in great detail.
Doing it yourself is the best way of learning.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Query criteria using AND on the same field

Postby robleyd » Wed Nov 14, 2018 1:47 pm

Might need parentheses on the last two criteria
Code: Select all   Expand viewCollapse view
WHERE
   CL."CourseTitle" ='Asbestos Awareness'
  and CL."CourseTitle" ='Facefit SR100'
   and (CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE)

 Edit: Incorrect advice 
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2634
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Postby eremmel » Wed Nov 14, 2018 2:01 pm

The approach of finding users that satisfy all conditions need to be solved differently: might be too difficult to realize based on experimenting.
The WHERE-clause conditions will check for a single records (defined relationship). So the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time.
You have two possible solutions.
- Take the query with the OR on CL."CourseTitle" and count if you find users with three hits.
- Create 3 relations from the CL table each for one specific "CourseTitle".

The latter might look like:
Code: Select all   Expand viewCollapse view
SELECT OL."Surename"

FROM "Ops List" OL
   INNER JOIN "Training Log" TL ON TL."OpID" = OL."OpID"
   INNER JOIN "CourseList" CL1 ON CL1."CourseID" = TL."CourseID"
       AND CL1."CourseTitle" ='Asbestos Awareness'
   INNER JOIN "CourseList" CL2 ON CL1."CourseID" = TL."CourseID"
      AND CL2."CourseTitle" ='Facefit SR100'
   INNER JOIN "CourseList" CL3 ON CL1."CourseID" = TL."CourseID"
      AND CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE

Note: that the conditions are shifted from the WHERE-clause to the specific JOIN condition: this is to express the tables relation
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Query criteria using AND on the same field

Postby robleyd » Wed Nov 14, 2018 2:06 pm

So the check on CL."CourseTitle" refers to a single record and can never be AND a AND b at the same time

What was I thinking? :knock: Sorry for the brain hiccup and ignore my two posts above :ouch:
Cheers
David
Apache OpenOffice 4.2.0 Build 9820 alpha version - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2634
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 2:18 pm

Thanks :D I'll play around with these to see if I can get it all to work.......
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 3:03 pm

I'm not having much luck on the relations. It's just freezing OpenOffice each time. I might just export the list of all in the OR list to Excel and create an easy pivot table for the right data.

Unless anyone else has any ideas I can play around with?
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm

Re: Query criteria using AND on the same field

Postby eremmel » Wed Nov 14, 2018 4:09 pm

Can you attach a test document (stripped private data) so our proposals can be tested. It might be that the SQL is getting complex. So you might try this as alternative:
Code: Select all   Expand viewCollapse view
SELECT OL."Surename"

FROM "CourseList" CL
   INNER JOIN "Training Log" TL ON CL."CourseID" = TL."CourseID"
   INNER JOIN "Ops List" OL ON TL."OpID" = OL."OpID"

WHERE
   CL."CourseTitle" ='Asbestos Awareness'
   OR CL."CourseTitle" ='Facefit SR100'
   OR CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingsExpiryDate" >= CURRENT_DATE

GROUP BY OL."Surename"
HAVING COUNT(*) = 3

Note: each OL.field in the SELECT-clause should also be listed in the GROUP-BY clause. This only works if there are no double entries in the log for any course / user combination.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Query criteria using AND on the same field

Postby AmyBUK » Wed Nov 14, 2018 4:24 pm

Oh my - that's fixed it!!!! Thank you so much. I'll practice with this so I understand it going forwards. The final SQL code I used is:

Code: Select all   Expand viewCollapse view
SELECT OL."FullName"

FROM "Course List" CL
   INNER JOIN "Training Log" TL ON CL."CourseID" = TL."CourseID"
   INNER JOIN "Ops List" OL ON TL."OpID" = OL."OpID"

WHERE
   CL."CourseTitle" ='Asbestos Awareness'
   OR CL."CourseTitle" ='Facefit SR100' AND TL."TrainingExpiryDate" >= CURRENT_DATE
   OR CL."CourseTitle" ='Asbestos Medical' AND TL."TrainingExpiryDate" >= CURRENT_DATE

GROUP BY OL."FullName"
HAVING COUNT(*) = 3


There will be duplicate records in the future, but there is a 'RefresherTrainingCompleted' tick box which I can use to exclude the old entries :D

Thanks again for all your help on this.
OpenOffice 4.1.5 on Windows 7
AmyBUK
 
Posts: 12
Joined: Tue Nov 06, 2018 6:36 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests