Query to locate missing records

[LSPM11]

I am trying to setup a query that will return a list of gymnasts who have not turned in a permission slip for a given meet taking place within a given year. I created a query to return all gymnasts that are still active and want to compare the results of this query to the contents of a table that’s used to record the receipt of permission slips. I am new to Microsoft Access and do not know if this is even remotely close to the right approach, but I have included the SQL for what I attempted, which returns no results. I would appreciate any feedback on the best method for obtaining the desired data:

PARAMETERS [Enter Meet Name:] Text ( 255 ), [Enter the Year in which Competition Occurs:] Long;
SELECT qryActiveGymnasts.LastName, qryActiveGymnasts.FirstName, qryActiveGymnasts.Level, qryActiveGymnasts.EmailAddress, qryActiveGymnasts.HomePhone, qryActiveGymnasts.MobilePhone
FROM qryActiveGymnasts LEFT JOIN tblMeetAttendance ON qryActiveGymnasts.[ID] = tblMeetAttendance.[Gymnast]
WHERE (((tblMeetAttendance.Meet)=[Enter Meet Name:]) AND ((tblMeetAttendance.CompetitionYear)=[Enter the Year in which Competition Occurs:]) AND ((tblMeetAttendance.Gymnast) Is Null));

 

[Joe4]

Welcome to the Board!

You should be able to simply do an Unmatched Query between your two queries. There is actually a Query Wizard right in Access that will walk you through the steps of doing this.

 

[LSPM11]

Thank you. I tried to create an unmatched query, but it returns an error message when I attempt to run it:
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
Access doesn't seem to like the fact that I'm assigning parameters to the table it's searching.

 

[Joe4]

I think the issue may be with your WHERE clause. You may be trying to do too much in one step.
Note the last clause:

[COLOR=#333333]AND ((tblMeetAttendance.Gymnast) Is Null))[/COLOR]

This is used in conjunction with the LEFT JOIN to only return records where there is no matching record in the "tblMeetAttendence" table.

However, in that same WHERE clause, you have other criteria like this, trying to limit your search to certain records, in that SAME table:

[COLOR=#333333]WHERE (((tblMeetAttendance.Meet)=[Enter Meet Name:]) AND ((tblMeetAttendance.CompetitionYear)=[Enter the Year in which Competition Occurs:]) [/COLOR]

So, I don't think you can check for records being there and not being there at the same time like that in a single step!

I think you may need to use Subquerys, to first select which records in the "tblMeetAttendence" table that meet your parameter criteria, and then use THAT resulting query in your Unmatched Query Wizard.

If you need help figuring that part out, let me know, and when I have some more time, I will try to recreate a database that mirrors yours to see if we can get that code for you.

 

[LSPM11]

Thank you Joe! I will play around with that and see if I can figure it out. I very much appreciate your help.