Craig,
The first step is to create a query that will see if any records meet
the conditions you mentioned. This would be a Totals Query. If you are
using the query design window, you would select Totals from the View
menu, leave Group By in the Totals row of the query design grid for both
the Product and BestBeforeDate columns, and then add the table's
primary key field to the grid, and put Count in the Totals row of this
column, and >2 in the Criteria row. The SQL for this query will look
something like this...
SELECT Product, BestBeforeDate, Count(ComplaintID) AS ProductComplaints
FROM YourTable
GROUP BY Product, BestBeforeDate
HAVING Count(ComplaintID)>2
There would be a number of ways your could proceed from here. One
option would be to base a continuous view form on this query, and then
use a macro to open this form if there are any records. Such a macro
would use the OpenForm action, and then in the Condition of the macro,
something like this...
DCount("*","NameOfYourQuery")>0
If you have a form which automatically opens whenever the database is
opened, this macro could be assigned on the On Load event of this form,
so the user is alerted to any such data every time the database is
opened. Of course, you will also need a date criteria, or else some way
of "processing" the offending complaints records, otherwise once there
is a triple-complaint it will keep popping up on the list forever... but
I don't know enough about your project to advise further on this.
