Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unanswered: SQL statement from multiple tables

    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:

    Code:
    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.Requery
    Me.lstSQLTest = Null
    
    End Function
    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.

    Thanks,
    Kevan

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're missing the JOIN clause, aren't you?

    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.

    SL

  3. #3
    Join Date
    Jun 2011
    Posts
    6
    Sam,

    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.

    Thanks,
    Kevan
    Attached Files Attached Files

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I can't unzip the file. I get an error message saying it's corrupted.

    Try clicking on the file right here, and see if you can open it.

    SL

  5. #5
    Join Date
    Jun 2011
    Posts
    6
    Sam,

    The link works for me, and the file opens after uncompressing. I'll attach another copy using a different zip program to this post to see if that has better results.

    Thanks,
    Kevan
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2011
    Posts
    6
    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:
    1. Check to see is the user is qualified
    2. 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.
    3. 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.


    tblUserAvailability
    Code:
    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
    tblUsers
    Code:
    UserID	 LastName	 FirstName	 Qual
    1		  Doe		  John		  Yes
    4		  Smith		Bill		  Yes
    5		  Doe		  Jane		  Yes
    6		  Schmoe	   Joe		   No
    Scenario 1
    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.

    Available Personnel
    Code:
    LastName	 FirstName
    Doe		  John
    Doe		  Jane
    Smith		Bill
    Scenario 2
    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.

    Available Personnel
    Code:
    LastName	 FirstName
    Doe		  Jane
    Smith		Bill
    Scenario 3
    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.

    Available Personnel
    Code:
    LastName	 FirstName
    Doe		  Jane
    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.

    Thanks,
    Kevan

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Still getting the same problem.

  8. #8
    Join Date
    Jun 2011
    Posts
    6
    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.

    Any other advice would be great.

    Thanks,
    Kevan
    Attached Files Attached Files

  9. #9
    Join Date
    Jun 2011
    Posts
    6
    With the help of a coworker I think I was able to get it working the way it should. I am not sure if this is the best design of the query, but it appears to solve all of the previous problems.

    The main change made was to use a nested query to first make a list of all people that are unavailable based on tblUserAvailability, and then select all qualified users that were not in the subquery.

    I have attached the current version that appears to now work correctly.

    Thanks,
    Kevan
    Attached Files Attached Files

  10. #10
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Good. I'm happy for you.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •