Attendance List with only lists of attendees and spouse names

[NewbyForAccess]

I've created an event attendance list from my table. I've selected only classmates with a Yes/No field which works fine. However, when I try to add spouses attending to this list, because some spouses are not coming, I've tried many combinations of criteria to show only those names of spouses who will attend and not those who are not selected to attend. I use a Yes/No field titled Spouse Attending and click to mark Yes. I cannot get anything I've come up with to work to select all classmates attending and yet only add those spouses' names who are marked as Spouse Attending. Instead, my list will show every spouse name I have in the table beside the classmate associated with this spouse. Please, can someone tell me what criteria to put in the design view or the SQL to get this to work correctly. I do not want spouses' names who do not plan to attend the event to come up in my attendance list. I've spent a month toying with this and am getting more confused by the minute. Please help! Here's the SQL I have for it right now.



SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Last Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Last Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [Contact Name], Contacts.[Attending Reunion], Contacts.[Last Name], Contacts.[First Name], Contacts.[Spouse Attending], Contacts.Spouse
FROM Contacts
WHERE (((Contacts.[Attending Reunion])<>0)) OR (((Contacts.[Spouse Attending]) Is Not Null) AND ((Contacts.Spouse)=IIf([Spouse Attending]<>0,[Spouse],"")))
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Last Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name]));

[June7]

Maybe expression in query:

SpouseAttend: IIf([Spouse Attending], Contacts.Spouse, Null)

[NewbyForAccess]

I tried your suggestion and it's still not leaving out spouse names who will not be attending. I put it into the query and got the same list as before. Thanks for trying to help me.

[June7]

Did you leave the Spouse field in the query as well?

Does the expression work?

[Micron]

I was going to suggest a UNION query, but have second thoughts. Those would be
- create a query that gets all primary attendees
- a 2nd query that gets all spouses that are attending
in both queries, you need a common field; hopefully you have a primary key
- create a 3rd query that uses the first 2, joined by that common field.
In this query, you don't need to show the PK field, but I would at the beginning so that you can check the results easier. Thus 2nd field comes from the primary attendees (qry1); 3rd field comes from 2nd query as spouses.

No wonder you've been at it so long. All those IIF's make my head spin.

[NewbyForAccess]

Yes, I left the Spouse field in the query. The expression, when I tried your method, gave the same results. My datasheet shows all attendees, but it does still show all spouses and not just those attending. I was hoping to just have the attending spouses show but yet not lose the entire list of the main attendees. I actually tried putting your suggestion in the same main criteria rather than the or which gave me a list of only attendees with spouses attending as well, but removed attendees without spouses. I'll have to just keep trying or use more than one list which isn't want I was hoping to do. Thanks, again.

[NewbyForAccess]

I've been able to create separate queries for primary attendees and one that gives only spouses. I tried to create a third query based off the other two and I've not figured out why the program will only allow me to use one query and not pull from two so I can create a 3rd query that you're be referring to. I do have a primary key for my table. I understand their importance which is why I have them. And, yes, all those IIf's can make your head spin. Thanks!

[June7]

If you want to conditionally show spouses, don't include the field directly, use the conditional expression.

You say [Spouse Attending] is a Yes/No field, however, Yes/No field can never be Null, it's either yes or no. So testing for Null is useless.

Query should be like:


SELECT IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Last Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [File As], IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Last Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name])) AS [Contact Name], Contacts.[Attending Reunion], Contacts.[Last Name], Contacts.[First Name], Contacts.[Spouse Attending], IIf([Spouse Attending], Contacts.Spouse, Null) AS SpouseAttend
FROM Contacts
WHERE Contacts.[Attending Reunion])<>0
ORDER BY IIf(IsNull([Last Name]),IIf(IsNull([First Name]),[Last Name]),IIf(IsNull([First Name]),[Last Name],[Last Name] & ", " & [First Name]));

[NewbyForAccess]

June7, Thank you so very much! This did work. Before I saw this, I went ahead and created new queries and used them to create union queries which ended up working, but it took me a while to get what I needed. I just tested your query and I only had one change. There was an extra parenthesis on the WHERE Contacts.[Attending Reunion]<>0. Once I took that out, it ran the query and brought back exactly the results I wanted. I can't thank you enough!

[June7]

Sorry about the typo. Glad it's working now.