SQL statement from multiple tables

**Backpacker** · 06-10-2011, 05:26 PM

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

**ssanfu** · 06-11-2011, 12:03 AM

There is a table called “tblUsers” which contains a list of users and their qualifications.

This sounds like you should also have a table of qualifications and possibly a junction table. A user can have many qualifications and a qualification can be had by many users.

I would use a saved query for the row source for the list box rather than using code to change the list box SQL. Since you posted the VBA, try this example:

Code:

Private Function UserSearch()
   Dim strSQL As String 

   strSQL = "SELECT DISTINCT tblUsers.UserID, tblUsers.FirstName, tblUsers.LastName"
   strSQL = strSQL & " FROM tblUsers LEFT JOIN tblUserAvailability ON tblUsers.UserID = tblUserAvailability.UserID_FK"
   strSQL = strSQL & " WHERE tblUsers.Qual = True"
   strSQL = strSQL & " AND ([StartDate]+[StartTime] <= [Forms]![frmSchedulingTest]![txtStartDate]+[Forms]![frmSchedulingTest]![txtStartTime])"
   strSQL = strSQL & " AND ([EndDate]+[EndTime] >= [Forms]![frmSchedulingTest]![txtEndDate]+[Forms]![frmSchedulingTest]![txtEndDate])"
   strSQL = strSQL & " ORDER BY tblUsers.LastName;"

   Me.lstSQLTest.RowSource = strSQL
   Me.lstSQLTest.Requery
   Me.lstSQLTest = Null
 
End Function

You might have to flip the "<" and ">" signs.

I start writing SQL statements by using the query designer. Then, if necessary, I edit the SQL. For me, this way is easier and quicker.

You had "WHERE NOT tblUsers.Qual = False " .
I used "WHERE tblUsers.Qual = TRUE ". Is there a reason you used "NOT" ? Also, how do you determine which of the qualifications to use or decide if a user is qualified?

Using "LEFT JOIN" will include a user if: they are qualified, but there are not any records in "tblUserAvailability".

**Backpacker** · 06-14-2011, 09:53 AM

ssanfu,

Thanks for the assistance. I took your code and modified it slightly to work in the way that I think it should for the dates rules. I believe that there needs to be an OR, because you can't have both true at the same time generally.