Query based on two different criterias

[BRZ-Ryan]

I have an Attendee combobox which is populated with all names from the People table, the only criteria is their role cannot be "dbadmin" (see below query). The Attendee combobox are people who go to scheduled events. Occasionally, we have a temp person as a fill-in for these events. I use VBA to add these temps to the People table, with a role of "(temp)"; I then re-query the combobox to get them to appear in the Attendee combobox for selection. I do not want these temps to appear when I am creating a new event or if I re-visit a previous event UNLESS they were an attendee at that event. If I re-visit an event in the db that they attended, I want them to appear in the combobox. I basically need to scan the people table, if their role is "temp", scan through attendance table and see if there is an attendance record for this event with their EmployeeID, if so they should be included in query. Also, the attendee combo is 2 column, 1st being ID, 2nd Name, bound to 1st)



1. Creating a new event, need to use "bob smith" as a temp fill-in
2. He is added to People table, with role of "(temp)", re-query combo and he is then selectable in my attendee combo
3. I navigate to create a new or re-visit an older record (which bob did not attend), "bob smith" should not appear
4. I revisit the new event from steps 1-2 and he is listed in the combo boxes.

When I select an Attendee from the combobox (as attending the event), a new record is created in the Attendance table (AttendanceID; EventID; EmployeeID).

People table fields:
EmployeeID
Name
Role

Attendance table fields:
AttendanceID
EventID
EmployeeID

Event table fields:
EventID
Date
Time
Scheduler (etc..)

Current query

SELECT People.ID, People.Name
FROM People
WHERE (Role<>"dbadmin") 
ORDER BY People.Name;

[June7]

So you need help with item 3?

Try code in the combobox GotFocus or Enter event or the form Current event to set combobox RowSource property based on value of PeopleID field and requery the combobox.

[BRZ-Ryan]

3 and 4. I need it to show everyone except "dbadmin" and "temp". The exception is to show temp if they have an attendance record for the current event.

I am creating an event which a temp person will attend
I use VBA to add a person to people table, role is "temp"
Requery to populate combo, get all except dbadmin, if I come across a "temp", check attendance table to see if he is at this event

New EventID = 55
The temp EmpoyeeID that I just added = 32

AttendanceRecord
ID=1 (irrelevant)
EventID=55
EmployeeID = 32

If that attendance record exists, include the person in my combo populate query

[June7]

Did you try the suggestion? Write code. Post for analysis if you encounter issue.

[BRZ-Ryan]

Doesn't RowSource require a query for this? Which is what I'm confused on.

I'm not sure how to tie in 3 different tables for the query

SELECT People.EmployeeID, People.Name
FROM People, Events, Attendance
WHERE ((Events.ID = Attendance.EventID) AND (People.EmployeeID = Attendance.EmployeeID)) AND (People.Role<>"dbadmin")
ORDER BY People.Name;

[June7]

Yes, VBA code can set the RowSource property to an SQL statement.

If this condition true Then
Me.combobox.RowSource = "SELECT ..."
Else
Me.combobox.RowSource = "SELECT ..."
End If
Me.combobox.Requery

The real trick is figuring out what event(s) to put the code in.

The tables should be joined in query on PK/FK linking fields. Design the SQL statement with the query builder then copy/paste it into VBA and edit as needed for VBA syntax (enclosed in quote marks, etc).

[BRZ-Ryan]

In the query builder, I set the appropriate relationships, if that's what you mean by PK/FK linking fields. I have the Attendance table set in the middle, with the ID from People linked to EmployeeID in Attendance and ID\Events to EventID in Attendance.

Here's what Access came up with for my design

SELECT People.ID, People.Name
FROM People INNER JOIN (Events INNER JOIN Attendance ON Events.ID = Attendance.EventID) ON People.ID = Attendance.EmployeeID
WHERE (((People.Role)<>"dbadmin")) OR (((People.Role)="Attendee (temp)") AND ((Events.id)=[Attendance].[Eventid]) AND ((Attendance.EmployeeID)=[People].[ID]))
ORDER BY People.Name;

[June7]

Now try code that sets the RowSource property. The WHERE clause will have to be different depending on the condition in the If Then structure. With the table joins, there is no need for the ID=ID parts in WHERE clause. However, INNER joins might not be appropriate.

Dim strSQL

strSQL = "SELECT People.ID, People.Name FROM People INNER JOIN (Events INNER JOIN Attendance ON Events.ID = Attendance.EventID) ON People.ID = Attendance.EmployeeID "
If this condition Then
strSQL = strSQL & "WHERE Role<>'dbadmin' "
Else
strSQL = strSQL & "WHERE Role<>'dbadmin' AND Role<>'Attendee (temp)' "
End If
Me.combobox.RowSource = strSQL & "ORDER BY People.Name;"
Me.combobox.Requery

or this version

Me.combobox.RowSource = "SELECT People.ID, People.Name FROM People INNER JOIN (Events INNER JOIN Attendance ON Events.ID = Attendance.EventID) ON People.ID = Attendance.EmployeeID WHERE Role<>'dbadmin'" & IIf(this condition true, "", " AND Role<>'Attendee (temp)'") & " ORDER BY People.Name;"
Me.combobox.Requery


BTW, Name is a reserved word and should not use reserved words in naming.

[BRZ-Ryan]

I'm confused on what you are thinking for the condition.

I put together a sample db to help. The person Tom Jones should only appear in the comboboxes in the first event since his Role = "Attendee (temp)" AND he has an attendance record for this event, Roy Patel should never appear in the comboboxes since his role = "dbadmin"

[June7]

Why is the EventID textbox on Events form named Combo0?

Just hit me what you are trying to do.

Should people be able to attend many events? If you designate a person in People table as a (Temp), this means they can never be a non-(Temp) at some other event? Also, person designated dbadmin could never be an Attendee. Maybe should be indicating the attendance type in the Attendance table?

[BRZ-Ryan]

I put that together very quickly just for demonstration..

A temp person is very rare so we can add them as a new temp to people table for each occurrence. It would probably be easier than trying to figure out a way to re-use the same temp that already exists in the people table.

[June7]

Will there ever be records in People table without Role value? If not, try:


Set the Employee combobox RowSource to:

SELECT People.EmployeeID, People.PeopleName, DLookUp("EmployeeID","Attendance","EventID=" & [EventID] & " AND EmployeeID=" & [EmployeeID]) AS Test FROM People WHERE ((Not (DLookUp("EmployeeID","Attendance","EventID=" & [EventID] & " AND EmployeeID=" & [EmployeeID])) Is Null) AND ((People.Role)<>"dbadmin"));

Then code in the Employee combobox GotFocus event procedure:
Me.Combo0.Requery