Excluding people

[mbhw99]

Hello again.

I have a query (Drawing Query) based off of two tables: "tbl eidandname" and "tbl occurance input".

What I'm looking to do, is build a report to include all names off of eidandname but exclude them if they show up in occurance input. The common field between the tables is "EID" and I have a relationship between them. What I've tried either comes back with nothing at all, or exactly what I don't want: what is in occurance input.

I'm sure the result is going to be so simple I'll probably slap myself.

I appreciate your time and thank you for your help.

 

[plog]

You need to create a LEFT JOIN from eidandname to occurance input. Then you bring down [occurance input].[EID] and set its criteria to NULL.

 

[mbhw99]

You need to create a LEFT JOIN from eidandname to occurance input. Then you bring down [occurance input].[EID] and set its criteria to NULL.

I might not be doing this exactly right, because I'm still getting no results.

Here is my SQL:

SELECT [tbl occurance input].ID, [tbl occurance input].Date, [tbl occurance input].EID, [tbl eidandname].[First Name], [tbl eidandname].[Last Name], Format([Date],"m/yy") AS Expr1
FROM [tbl eidandname] INNER JOIN [tbl occurance input] ON [tbl eidandname].EID = [tbl occurance input].EID
WHERE ((([tbl occurance input].EID) Is Null) AND ((Format([Date],"m/yy")) Like [Enter Date (m/yy):]));

 

[plog]

SELECT [tbl occurance input].ID, [tbl occurance input].Date, [tbl occurance input].EID, [tbl eidandname].[First Name], [tbl eidandname].[Last Name], Format([Date],"m/yy") AS Expr1

1. 'Date' is a bad name for fields. Its a reserved word in access and it should have barked at you when you tried to do it. Name it something more meaningful, prefix it with what date it actually is, i.e. 'creationDate', 'startDate', etc.)

2. Try to eliminate spaces in your field and table names, [tbl eidandname].[First Name] should be [tbleidandname].[FirstName]

3. Why are you selecting fields from tbl occurance input when you want that data to be blank?

FROM [tbl eidandname] INNER JOIN [tbl occurance input] ON [tbl eidandname].EID = [tbl occurance input].EID

4. You have a regular join, you need a LEFT JOIN: http://www.w3schools.com/sql/sql_join_left.asp

WHERE ((([tbl occurance input].EID) Is Null) AND ((Format([Date],"m/yy")) Like [Enter Date (m/yy):]));

5. For now, remove that last criteria where the user inputs the date. See if you can get the query to return results before you start adding more conditions on to it.

 

[mbhw99]

5. For now, remove that last criteria where the user inputs the date. See if you can get the query to return results before you start adding more conditions on to it.

Very good, sir. I suppose I wouldn't be getting any values if I pull the date from occurance input...duh on me.

 

[plog]

Good catch, I missed that as well. That criteria shouldn't be on there at all. Also, I take back what I said about including fields from the occurance table. Include the ID field, that way you can verify that all the records your query returns has no match in the occurance table because that field should be blank.

 

[mbhw99]

I do appreciate your help. One of the goals is to do this query within a date range, too. How do I go about doing that?

 

[plog]

Date range of what? Whatever date range, it has to be a date in the eidandname table because you want all the data in the other table to be blank.

 

[mbhw99]

The purpose for this query is to get a list of all our employees that have not been included in the occurance input table within a date range.

Hmm...I'll have to figure something out unless you have an idea?

 

[plog]

Now that is a different request than what you initially posted. This SQL should be what you want:

SELECT [tbl eidandname].[First Name], [tbl eidandname].[Last Name], Format([Date],"m/yy") AS Expr1
FROM [tbl eidandname] INNER JOIN [tbl occurance input] ON [tbl eidandname].EID = [tbl occurance input].EID
WHERE ((([tbl occurance input].EID) Is Null) OR ((Format([Date],"m/yy")) Like [Enter Date (m/yy):]))
GROUP BY  [tbl eidandname].[First Name], [tbl eidandname].[Last Name], Format([Date],"m/yy");

 

[mbhw99]

Thanks so much!

I apologize for the confusion.