Access/SQL: How can I check if in a table all duplicates (same date) have a value of 0 except one?

I have a table in Access (tblExample) which consists of the fields ID, tripID, day and value.

This table is edited by a form. When the user closes the form Access should validate the entries made. It is ok to have multiple records of the same date in day. BUT…if there are multiple records of the same date only one can have a value other than 0. How can I make Access to check for that?

My idea: Creating a query which returns all records which don’t fullfill the above requirements. Afterwards let VBA check if the query is empty or not. If it is not empty show an error message box. How would such a query look like in SQL? How can I check in VBA if that query returns no records?

Maybe my idea is too complicated. Is there an easier way to check for it?

Answer

Most databases have facilities to do this easily — either using a filtered unique index or an expression-based unique index.

MS Access does not provide such capabilities. Basically you have two options:

The first is to create triggers — both update and insert triggers — that validate the data. The advantage of triggers is that they enforce the data correctness in the database. The disadvantage is that they are hard to maintain.

The second is to do the check at the application level when you insert or update data. This is probably the simplest method in MS Access.

Basically, you would check:

select count(*)
from tblExample
where day = @day and value > 0;

If this returns a value larger than 0, then do not insert/update the table, returning an error.

I will note that this is not thread-safe. That is, two different users could end up inserting non-zero values at the same time. I will add an editorial comment: if you are worried about such things (and concurrency issues are quite important to worry about), then you should be using a more sophisticated database than MS Access.