Dcount function to only count customer once and maximum of 16...

[andrew v via AccessMonster.com]

i have a dcount function (an unbound text box) below which counts how many
seats has been reserved for a testing session (am/pm). my problem is that
i need it to have a limit of 16 people only per session per day.

tblCustomerInfo and tblEvents with a one to many relationship by
CustomerID. A customer can take many tests per session, however i only
need the customer to be accounted for once with the function.

for example, right now, if the customer is taking three different tests in
the morning session, there name will show up three times on the report and
will be accounted for three times instead of once.

=DCount("*","tblEvents","(EventDate=Date()) and (EventTime='AM')")

as a beginner, i know that the vba will have to be in the Before Update
event to account for a maximum of 16 customers and to account for the
customer only once in a session if they're taking more than one test per
session. perhaps involving 2 IF THEN ELSE statements.

any suggestions???

 

[ChrisJ]

You may have to create a query that returns distinct rows containing
CustomerID, EventDate and EventTime='AM' (say qryDistinctCustomers)
and use this in your function

=DCount("*","qryDistinctCustomers","(EventDate=Date()) and (EventTime='AM')")