Display missing records in a report

[oihjk]

I have several tables in this database 3 of which relate to this issue. tblMembers has several fields, but these are the relevant ones: Member ID, First Name, Last Name. tblEvents has all my events with fields Event ID, Event Name, Location, Date. tblAttendance has Member ID, First Name, Last Name, Event ID.

I already have the forms for data entry. What I'm wanting to do is show in a report the people who didn't attend a certain event. For example in the tblAttendance table list all the people who were present, then run a report that checks to see which member id's weren't there.

1)The Member ID's increment by 1 so I thought of checking the list for gaps (not sure how to do it). Then extract the missing numbers and print their records from the tblMembers table. I'm not sure how efficient this would be, it could be trouble if a member was deleted.

Preferred:
2)Somehow check the list against tblMembers and if it exists in tblMembers but doesn't in tblAttendance for the this Event ID then print those names.

My forms are like this [frmMembers], [frmEvents] w/ sub form on it [frmAttendance subform].

Any help is appreciated, even a nudge in the right direction will help.
Eric

 

[mark curtis]

Eric,

Where are you recording the members list who should attend?

You could add a Yes/No field to the tblAttendance and after the event check Yes if attended and run a query on "No".

Zip it up and post it to be looked at.

 

[Pat Hartman]

Create a query that joins tblMembers to tblEvents using a left join. Then use Is Null as the criteria for the MemberID field in tblEvents.

Select tblMembers.*
From tblMembers Left Join tblEvents On tblMembers.MemberID = tblEvents.MemberID
Where tblEvents.MemberID Is Null;

 

[oihjk]

Excellent...Almost

Thanks for the quick reply, sorry it took so long to re-reply.

My members list is in tblMembers, tblAttendance records the people that attended an event by having an Event ID field which relates to tblEvents. Example: Eric, Pat, and Mark are members. Pat has a Barbeque, but Eric and Mark don't show up. Pat is the only one that attended. So I want the query to show which members weren't there eg. Eric and Mark. (I suppose I should've posted this in queries, but wasn't sure if it would be query or code, I did know the end result would be report.

This is the how the SQL ended up:

SELECT tblMembers.*, [tblAttendance].[Member ID]
FROM tblMembers LEFT JOIN tblAttendance ON [tblAttendance].[Member ID]=[tblMembers].[Member ID]
WHERE ((([tblAttendance].[Member ID]) Is Null));

It works great if I've only got one Event, but I need to check it against the Event ID. So as it is now if I have a Barbecue at my house and Me and Mark come then if won't show any absentees. But if I could say show the absentees for Pat's Barbecue (in other words [event id])...that would be great. I tried =forms!frmEvents![Event ID], but all it showed then was one null record and I do understand why. I'll post a sample database so someone may get a better idea of what I want.
Thanks

 

[Pat Hartman]

So are you saying that every person is invited to every event? If that is the case, create a cartesian product by creating a query that includes both the member table (all columns) and the events table (only EventID), There is no common field in these tables so there will be no join line. Then replace tblMembers in the query I posted with the cartesian product that you just created and change the join to be on both MemberID and EventID. They both need to be set as Left Join.

 

[mark curtis]

Eric,

Send me an email to mark_curtis_69@yahoo.com

I have modified your db but I am having problems attaching it on the forum.

 

[oihjk]

Uh Oh!

I was working in one of my forms and I closed it and said I didn't won't to save it. Well I lost all the code for that form. Is there any way to get it back? Oh well if not, right.

Pat, yes everyone is invited to each event, but not everyone will come.

What you are saying with the cartisian product is to create a query with out the joins. Then in the other query instead of referencing tblMembers reference the new query?
I'll give it a try in the morning.

Thanks for the help.

 

[oihjk]

Query not working...

First Mark on the database you sent me...I appreciate the attempt, I don't want to just push that aside, but it seems like I would have to enter everyone's name in the attendance list for each event. The way I want to do it, is enter the people who showed up only and check that list against the members.

I created a query named qryCartisian Show Absentees. I then changed the query qryShow Absentees to this:

SELECT [qryCartisian Show Absentees].*, [qryCartisian Show Absentees].[Event ID]
FROM [qryCartisian Show Absentees] LEFT JOIN tblAttendance ON [qryCartisian Show Absentees].[Member ID] = tblAttendance.[Member ID]
WHERE (((tblAttendance.[Member ID]) Is Null) AND (([qryCartisian Show Absentees].[Event ID])=1))
ORDER BY [qryCartisian Show Absentees].[Member ID];

qryCartisian Show Absentees has tblMembers and tblEvents, showing all tblMembers and Event ID from tblEvents. No joins.

When I run qryShow Absentees it shows all of the Absenses correct if I only have one event, but when I add the second event the query shows the people that were absent from one or the other. If I only have one event then it will show that I missed that event. But with two, if I missed Pat's BBQ but went to my BBQ then it would not show me as absent from either one. Basically because my field isn't null anymore. Make sense?

Any thoughts? Thanks, Eric

 

[Pat Hartman]

You almost got it. The join needs to be on two fields. I also removed the explicit reference to Event ID in the select clause since it was redundant. BTW, It is really poor practice to use embedded spaces and special characters in your object and field names.

SELECT [qryCartisian Show Absentees].*
FROM [qryCartisian Show Absentees] LEFT JOIN tblAttendance ON [qryCartisian Show Absentees].[Member ID] = tblAttendance.[Member ID] AND qryCartisian Show Absentees].[Event ID] = tblAttendance.[Event ID]
WHERE (((tblAttendance.[Member ID]) Is Null) AND (([qryCartisian Show Absentees].[Event ID])=1))
ORDER BY [qryCartisian Show Absentees].[Member ID];

 

[oihjk]

Haha! Thanks Pat, that was the trick. I'm grateful for your hard work on that. Not that it was hard for YOU. I'll start using non-spaced names from here on out as well.

Thanks,
Eric