Need help with this query

[Remillard]

Another newbie question. I have a table to track employees with many fields, including dates for various training and what site they work at. I have a form which is used to pass data to a query.
The fields on the form are "Site" (a combo box with 4 possible work sites), "Training Expires Start" and "Training Expires End" (date picker boxes), and then a button to run the query.


The query needs to find anyone whose training expires between the two chosen dates and the value in the Combo Box. That part is easy. However, I also want to return all entries for staff who work at the chosen "Site" but whose training date field is empty. Sadly I can't quite get the ANDs and ORs quite right. Here's the query:

SELECT StaffBasicInformation.FirstName, StaffBasicInformation.LastName, StaffBasicInformation.Site, StaffBasicInformation.EECTraining
FROM StaffBasicInformation
WHERE ((StaffBasicInformation.Site)=Forms!Expiry![1stAidEECSite])
AND (DateAdd("yyyy",3,StaffBasicInformation!EECTrainin g))>=Forms!Expiry![1stAidEECStartDate]
AND (DateAdd("yyyy",3,StaffBasicInformation!EECTrainin g))<=Forms!Expiry![1stAidEECEndDate]
OR ((StaffBasicInformation.Site)=Forms!Expiry![1stAidEECSite]) AND ((StaffBasicInformation.EECTraining) Is Null)
ORDER BY StaffBasicInformation.LastName;


It's only returning those entries whose EECTraining field is null. In pseudo-code, it should be something like:

Select all the Staff from ThisSite whose Training will expire between ThisDate and ThisDate as well as Staff from the ThisSite whose Training field is empty.

Thanks in advance!

[orange]

I suggest you describe in plain English WHAT you are trying to accomplish. Forget Access, forms, query etc for the moment. Tell us what you are trying to do.

[Remillard]

I am trying to find all the staff who work at a chosen site whose training will expire between a chosen date and another chosen as well as the staff working at that site whose training date field is empty. Sorry, I thought the pseudo-code would work for clarification.

[orange]

My recommendation is to start with a data model or ERD (entity relationship model diagram).
This represents a picture of your database, its table and the relationships between tables.
From your English I see you are dealing with these "things" (entities)

staff
sites
training


Can a staff member have more than 1 training?
Can a training be taken by more than 1 Staff member?
Can a Staff work at more than 1 Site?
I think there is a Many to Many relationship between Staff and Training. You may need a junction table to remove the M:M relationship.

You might want to work through this tutorial to help put all concepts into context.
Good luck with your project.

[Remillard]

I appreciate your input orange and thank you for taking the time to respond. I recognize my db is rather simplistic and perhaps down the road I will re-work it but at the moment I am not in a position to make wholesale changes. That being said, I'm sure there is a way to mine the data I need (as described).
The document you provided is a good read. Thanks!

[orange]

If you try the query with just the OR criteria

eg.
SELECT StaffBasicInformation.FirstName, StaffBasicInformation.LastName, StaffBasicInformation.Site, StaffBasicInformation.EECTraining
FROM StaffBasicInformation
WHERE
((StaffBasicInformation.Site)=Forms!Expiry![1stAidEECSite]) AND ((StaffBasicInformation.EECTraining) Is Null)
ORDER BY StaffBasicInformation.LastName;

Does it give you some results?

Then try

SELECT StaffBasicInformation.FirstName, StaffBasicInformation.LastName, StaffBasicInformation.Site, StaffBasicInformation.EECTraining
FROM StaffBasicInformation
WHERE ((StaffBasicInformation.Site)=Forms!Expiry![1stAidEECSite])
AND (DateAdd("yyyy",3,StaffBasicInformation!EECTrainin g))>=Forms!Expiry![1stAidEECStartDate]
AND (DateAdd("yyyy",3,StaffBasicInformation!EECTrainin g))<=Forms!Expiry![1stAidEECEndDate]
ORDER BY StaffBasicInformation.LastName;

What does this give as result?

[Remillard]

Yes, they both work individually as intended. The first one returns all records where EECTraining is empty and the staff works at the chosen Site.
The second one returns all records where the EECTraining expires between the chosen dates and the staff works at the chosen site.
I need a marriage of the two.
Btw... you have got me ruminating on how I would redesign the db down the road.

[orange]

Try this

SELECT StaffBasicInformation.FirstName, StaffBasicInformation.LastName, StaffBasicInformation.Site, StaffBasicInformation.EECTraining
FROM StaffBasicInformation
WHERE (StaffBasicInformation.Site)=Forms!Expiry![1stAidEECSite])
AND   (
          ((DateAdd("yyyy",3,StaffBasicInformation!EECTrainin g) Between Forms!Expiry![1stAidEECStartDate]
           AND Forms!Expiry![1stAidEECEndDate])
          OR 
          ((StaffBasicInformation.Site)=Forms!Expiry![1stAidEECSite]) AND ((StaffBasicInformation.EECTraining) Is Null))
         )
ORDER BY StaffBasicInformation.LastName;

Pattern:
Where A and ( B or C)

[Remillard]

THANK YOU! I massaged what you gave me and I got what I needed. I should have gone back to my scripting thinking in terms of how I grouped with brackets. Here's the final results:
SELECT StaffBasicInformation.FirstName, StaffBasicInformation.LastName, StaffBasicInformation.Site, StaffBasicInformation.EECTraining
FROM StaffBasicInformation
WHERE ((StaffBasicInformation.Site)=Forms!Expiry!EECSite )
AND (
(DateAdd("yyyy",3,StaffBasicInformation!EECTrainin g) Between Forms!Expiry!EECStartDate And Forms!Expiry!EECEndDate)
OR
((StaffBasicInformation.EECTraining) Is Null)
)

And again, thanks for the primer paper. I may look at redesigning sooner than later.

Cheers!

[orange]

Glad it's working.
Good luck with your project.