Checking if Customers Have Ordered this Week Month Year

You have a table of customers who order on a regular basis (weekly monthly yearly) and you are storing there order frequency. You have a related table of invoices for these customers and want to return details of customers whose last order date was longer than their order frequency.

First of all we need a lookup table. Create a new table with two fields. The first field should have the same name as the field in your customer table containing their order frequency. The second field should be a number value containing the number of days that order frequency equates too. See table example below.

Save this table as tbl_FreqLookup

Create a new query. Bring in your customer table and your invoice table. Ensure the link is made between the two table on the appropriate Unique Key. From the view menu select Totals. This creates a new line in the query design grid. Drag in the linking field for the two tables and select group by for the total. Drag in the invoice date field and select Max for the total. Run the query, and it should show the last invoice for each customer. Save the query as qry_LastInvoice

Create a new query. Bring in the qry_LastInvoice query we just created the customers table and tbl_freqLookup table we created earlier. Link the customers table to the lookup table by orderfrequency. Link the customer table to the lastinvoice query by unique id. Drag the customer name field into the grid. Drag in the maxorderdate field from the lastinvoice query into the grid, and for its criteria type

Run the query. It should return all customer whose last order is older than todays date minus their order frequency.