Complex Unmatched Query

[marvelousme]

I have a customer service database where workers log details about issues. I also have users log how many hours they work each day so I can calculate their productivity. I want to alert users so that each time they log in, if there are any days where they have logged issues BUT did not log hours for that date, they will get a pop telling them which days have not been accounted for so that they can go in and correct this.

I have already created 2 queries to help me: Issues Logged_Current User shows me all of the issues logged for the one logged into the system and the corresponding dates. My second query, Hours Logged_Current User, shows me the date the current user worked and the hours they logged. I want to compare these 2 so that a query shows me what days have not been logged based on the dates they have produced work. I thought I could do this with the Unmatched Query Wizard, but it's only showing me the data that matches. I want the data that doesn't match. Here is a copy of what the data looks like currently. My comparison query, based on the criteria I have given, should produce the dates of 1/3, 1/4, 1/6, and 1/7 because work has been logged but no hours have been logged on those dates--but that's not happening in my Unmatched Query Wizard. What am I doing wrong??

Issues Logged_Current User
Sam Smith 1/1/2016
Sam Smith 1/2/2016
Sam Smith 1/3/2016
Sam Smith 1/4/2016
Sam Smith 1/5/2016
Sam Smith 1/6/2016
Sam Smith 1/7/2016
Sam Smith 1/8/2016

HoursLogged_Current User
Sam Smith 1/1/2016 6.0
Sam Smith 1/2/2016 8.0
Sam Smith 1/5/2016 7.5
Sam Smith 1/8/2016 4.25

 

[Frothingslosh]

Basically, you're looking at something like this:

SELECT ilcu.UserName, ilcu.IssueDate 
FROM [Issues Logged_Current User] As ilcu 
LEFT JOIN [Hours Logged_Current User] As hlcu 
    ON ilcu.IssueDate = hlcu.DateWorked 
WHERE hlcu.DateWorked Is Null

Obviously, you'll need to update field names with the correct names.

Also, as a side note, you really should avoid putting spaces in table, field, and query names. Access really doesn't react well to that, and it just makes life more difficult for you in the long run. Instead of Issues Logged_Current User, for example, you should have IssuesLoggedCurrentUser or even IssuesLogged_CurrentUser.

 

[marvelousme]

Thanks so much for the quick reply! Unfortunately, this isn't helping me. It is returning all of the dates in my Issues table instead of the Missing dates from my Hours Worked table. Any other suggestions?

 

[Frothingslosh]

Did you include that WHERE clause? It limits the records returned to those that have a listing in Issues Logged but none in Hours Logged.

Post the SQL from your query (along with a screenshot of some dummy data and an example of what you are expecting, if you can) and I'll take another look at it.

 

[Frothingslosh]

Scratch that, I gave you a bad query, brb with a fixed one.

 

[marvelousme]

Hmmm.....well I tried it in a "dummy" database and it worked perfectly, but it's not working in my live database. I am going to look at my data again. Each date field in both tables is formatted as a "short date", so if the formatting is the same, the query should technically work, correct

 

[Frothingslosh]

Okay, what you want is the following:

SELECT [Issues Logged_Current User].UserID, [Issues Logged_Current User].IssueDate, [Hours Logged_Current User].HoursWorked 
 FROM [Issues Logged_Current User] 
 LEFT JOIN [Hours Logged_Current User] 
     ON ([Issues Logged_Current User].IssueDate = [Hours Logged_Current User].DateWorked) AND ([Issues Logged_Current User].UserID = [Hours Logged_Current User].UserID) 
 WHERE ((([Hours Logged_Current User].HoursWorked) Is Null));

I've attached a sample database where it works so you can see in the query builder how I did it. Basically, you need to do a left join on both the user's unique ID AND the date.

 

[marvelousme]

You're a genius! Thank you so much! This is perfect.