I am pretty new to access and am trying to create an SQL statement in Access 2007 with VBA. What I would like to do is create a list based on two different tables and criteria from each table. Here is a description of what I have:
This part of the database is for scheduling people based on their qualifications and if they are available or not. There is a table called “tblUsers” which contains a list of users and their qualifications. There is another table “tblUserAvailability” which contains date/time periods that the user will be unavailable to work. This table could contain multiple entries per user for different dates and time blocks. The two tables are linked via a PK “UserID” on “tblUsers” and a FK “UserID” on “tblUserAvailability”
The end goal is to have the query check to see if a user is qualified, then check to see if they have blocked out the time as unavailable and then finally make sure that they are not currently assigned to anything else during that time frame. Right now I am only working on getting the first two parts to work. Eventually there would likely be a third table to check based on current assignments.
I have tried different setups for the query but keep getting strange results that don’t make a lot of sense to me. I tried using “WHERE” and flipping the greater than/less than signs, as well as different combinations of AND & OR. One main problem I have is that I need any user that is qualified to show up if they are currently available.
The main problems are:
1. Some users are not listed at all in “tblUserAvailability” if their schedule is currently wide open.
2. Users with multiple entries show up multiple times even when “SELECT DISTINCT” is used based on them having multiple entries in “tblUserAvailability” it seems. The output that I want is just a list of who is available for an assignment during a certain time period.
Here is what I have right now:
Private Function UserSearch()
Dim strSQL As String
strSQL = "SELECT DISTINCT tblUsers.FirstName, tblUsers.LastName, tblUsers.UserID, " & _
"FROM tblUsers, tblUserAvailability " & _
"WHERE NOT tblUsers.Qual = False " & _
"AND (Forms!frmSchedulingTest!txtStartDate + Forms!frmSchedulingTest!txtStartTime) <= (tblUserAvailability.EndDate + tblUserAvailability.EndTime) " & _
"AND (Forms!frmSchedulingTest!txtEndDate + Forms!frmSchedulingTest!txtEndDate) >= (tblUserAvailability.StartDate + tblUserAvailability.StartTime) " & _
"ORDER BY tblUsers.LastName"
Me.lstSQLTest.RowSource = strSQL
Me.lstSQLTest = Null
I am unsure if using an inner or outer join would be more correct here and how exactly to implement that. Any suggestions or advice would be appreciated.
Try using Access' query designer. It's very user friendly, and there's less guesswork. Using VBA to create SQL statements is OK for advanced users, but I'm programming in Access for over 10 years, and I still prefer using the designer, unless it's a very simple statement.
Thanks for the reply, I have looked into using the Query designer, but it seems to me to be harder to accomplish the more complex tasks inside it, perhaps that is just due to me being new and not fully understanding its potential.
I did some further searching around and modified the query, which appears to work better, but still has some problems:
1. It does not seem to display the qualified users that do not have entries on the availability table for some reason. For example the user "Jane Doe" should show up, but does not.
2. For cases where there are date entries on multiple dates for a given user, the query will return them as being available, even when they are not, for example "John Doe" shows up if you put in 25 June 2011, as the date on the form because he has an entry on 21 June in the availability table.
3. Lastly it does not seem to always sort out people correctly based on the times they are unavailable.
I have attached a modified version of my project.
Any further ideas that anyone might have would be really appreciated.
I am still can’t seem to crack this problem. In order to hopefully clarify what I am trying to do, here are some examples of what I would like to happen.
The main steps are:
Check to see is the user is qualified
Check to see if the user has any times that they cannot work as listed in tblUserAvailability. If they are free, then list them as available for that event. A problem here seems to be that if John Doe is unavailable on 6/25/2011, since there is an entry on 6/21/2011 that is outside of the event date range, it shows him as available.
If a qualified person has no entries in tblUserAvailability then they are automatically available and should be listed as well. This does not seem to be occurring correctly for me.
AvailabilityID UserID LastName FirstName StartDate EndDate StartTime EndTime
11 1 Doe John 6/21/2011 6/21/2011 7:00:00 AM 9:00:00 AM
12 1 Doe John 6/25/2011 6/25/2011 10:00:00 AM 12:00:00 PM
15 1 Doe John 6/25/2011 6/25/2011 6:00:00 PM 8:00:00 PM
16 4 Smith Bill 6/25/2011 6/25/2011 12:00:00 PM 8:00:00 PM
UserID LastName FirstName Qual
1 Doe John Yes
4 Smith Bill Yes
5 Doe Jane Yes
6 Schmoe Joe No
Event Start Date: 6/25/2011, Event Start Time: 5:00 AM
Event End Date: 6/25/2011, Event End Time: 6:00 AM
Desired Results for Scenario 1
In this case, Joe Schmoe is not included since he is not qualified, however all of the other users are qualified and do not have any other periods of unavailability.
Event Start Date: 6/25/2011, Event Start Time: 10:00 AM
Event End Date: 6/25/2011, Event End Time: 11:00 AM
Desired Results for Scenario 2
In this case Joe Schmoe is still not included, but John Doe is removed because he has an overlap with his 10:00 AM to 12:00 PM blocked out time. This only leaves Jane Doe and Bill Smith.
Event Start Date: 6/25/2011, Event Start Time: 5:00 PM
Event End Date: 6/25/2011, Event End Time: 9:00 PM
Desired Results for Scenario 3
In this case Joe Schmoe is still not included. Now, John Doe is removed because he has an overlap with his 10:00 AM to 12:00 PM blocked out time, and Bill Smith is removed because of his 12:00 PM to 8:00 PM blocked out time. This only leaves Jane Doe as available.
I would like this to be done in a query if possible, I just can’t seem to figure out how to accomplish this task. Any further ideas would really be helpful.
I made some progress, making a few formatting changes to the SQL statement. It now works correctly as far as eliminating people from the list if they are not available.
I do still experience these problems though:
1. People that do not have any availability data should show up in the results, but don’t, i.e. Jane Doe.
2. If someone is busy during the event, then they won’t show up because of that entry, however if they are listed in the availability table on another date then they will show up incorrectly.
3. It seems that the distinct function does not work correctly as it will show multiple entries of the same person.