Access SQL Subqueries

[stringman]

Hello,

I am having a problem getting the expected results from a SQL subquery. The point of the query is to return all users and user titles that have not been entered into the database for the current month (not in tblHours). Some users have 2 titles and some just one title. Each month we enter hours worked per title so we can track labor progress. The problem lies w/ the individuals w/ 2 titles. The SQL only keys on the user id, but I need to return both user id and title. The variables called iMonth and iYear are passed from the form and the query is executed via a command button. qryTitlesPerUser contains each users title, name, and user id.

Here is the query:

  strSQL = "SELECT qryTitlesPerUser.Name, qryTitlesPerUser.title FROM qryTitlesPerUser "
  strSQL = strSQL & "WHERE qryTitlesPerUser.UserID AND qryTitlesPerUser.title NOT IN "
  strSQL = strSQL & "(SELECT tblHours.UserID, tblHours.title FROM tblHours "
  strSQL = strSQL & "WHERE tblHours.month = " & iMonth & " AND tblHours.year = " & iYear & ") "
  strSQL = strSQL & "ORDER BY qryTitlesPerUser.Name"

Any help would be greatly appreciated.

Ken

 

[ByteMyzer]

Try:

strSQL = "SELECT qryTitlesPerUser.Name, qryTitlesPerUser.title "
strSQL = strSQL & "FROM qryTitlesPerUser "
strSQL = strSQL & "WHERE NOT EXISTS "
strSQL = strSQL & "(SELECT tblHours.* FROM tblHours "
strSQL = strSQL & "WHERE tblHours.UserID=qryTitlesPerUser.UserID "
strSQL = strSQL & "AND tblHours.title=qryTitlesPerUser.title "
strSQL = strSQL & "AND tblHours.month = " & iMonth
strSQL = strSQL & " AND tblHours.year = " & iYear & ") "
strSQL = strSQL & "ORDER BY qryTitlesPerUser.Name"

 

[stringman]

ByteMyzer,

Thanks for the input. I tried, but w/out success. I also tried your query with the "WHERE NOT IN" clause. Any other ideas. This doesn't seem like it should be this complicated, but ...

Ken

 

[ByteMyzer]

How about providing some specifics? In what way does it not work? Are you getting an error message? Are you getting records in the result that you don't think should be there, or not getting records that you think should be there? Specify, specify, specify!!!

 

[stringman]

After I enter the hours for a user w/ 2 titles I run the query to see how many users and titles are left. The query should show the user name just entered and the remaining title. Currently, it doesn't show the user at all. In other words:

User: Bob
Titles: title 1, title 2

Before entering any data for Bob I will see the following in the query output:
Name Title
Bob title 1
Bob title 2
Betty title 1
Betty title 2
etc

After entering in hours for title 1 I should see:
Name Title
Bob title 2
Betty title 1
Betty title 2
etc

This second part is what the query is not showing. It shows:
Name Title
Betty title 1
Betty title 2
etc

Ken

 

[ByteMyzer]

Would it be possible for you to attach a ZIP-ed copy of your database to this message thread? There is no apparent reason for the query I gave you not to work, so there's something else going on that we can't see here.

 

[stringman]

ByteMizer,

Problem solved. I had the following code after the SQL statement:

On Error Resume Next
DoCmd.Close acQuery, "qryUsersRemaining"
DoCmd.DeleteObject acQuery, qryUsersRemaining
Set qdf  = dbs.CreateQueryDef("qryUsersRemaining", strSQL)
DoCmd.OpenQuery ("qryUsersRemaining")

I did not have quotes around qryUsersRemaining when calling the DeleteObject command, which gave me a "wrong data type" error message. However, I never saw the error message since I used "On Error Resume Next" statement. Therefore, my old query was not being deleted and replaced by the new query you wrote.

Thanks for your help. I should know by now that when something appears obviously correct, then I should look somewhere else.

Ken

 

[ByteMyzer]

There you go. When debugging, always remember to comment out all On Error statements until your code is validated in Run-Time.

I'm glad it worked.