Microsoft Access Office VB VBA Help and Examples
           
             

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.

OrderFrequencyNumDays
Weekly7
Monthly31
Quarterly92
Yearly365

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

< now() - numdays

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

 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.