Breaking Groups into 3 buckets based on whether or not one field has any 0s

I have events that are being treated differently based on whether or not expenses have 0 attendees.

Each event has a unique code with several expense line items. If the attendee field has ALL 0 for each expense in this event code, then I need to append the method field to say method 1, for each expense with this event code.

If ANY of the line items with the same event code have a 0 but the sum of all of them is > 0 then I need to append the method field to say method 2

If ALL of the line items are > 0 then I need to append the method field to say method 3

Thanks for any help! Sorry for not having more code in this, I am at a loss.

Sample Data:

Answer

Since MS Access does not support aggregation within update queries, one possible alternative might be to use the domain aggregate functions DMin & DMax, e.g.:

update YourTable t
set t.method = 
iif
(
    dmax("attendee","YourTable","EventID = '" & t.EventID & "'")=0,
    1,
    iif
    (
        dmin("attendee","YourTable","EventID = '" & t.EventID & "'")=0,
        2,
        3
    )
)

This assumes that attendee will never be negative.

Alternatively, if you wanted to avoid the domain aggregate functions, you could write the values using three separate update queries – one for each method value:

Method = 1

update YourTable t
set t.method = 1
where not exists (select 1 from YourTable t2 where t2.eventid = t.eventid and t2.attendee > 0)

Method = 2

update YourTable t
set t.method = 2
where exists (select 1 from YourTable t2 where t2.eventid = t.eventid and t2.attendee > 0)
  and exists (select 1 from YourTable t2 where t2.eventid = t.eventid and t2.attendee = 0)

Method = 3

update YourTable t
set t.method = 3
where not exists (select 1 from YourTable t2 where t2.eventid = t.eventid and t2.attendee = 0)

In all of the above, change all occurrences of YourTable to the name of your table, and the fields attendee & EventID to suit your field names.