I've developed a quote/order database in access 2003 for a customer, so this is primarily a VBA solution, but I'm mostly after theory today so vba or vb is irrelavant.
I have of course normalized tables containing historical and current orders.
Right now, I have a reminder type form that shows all completed orders from a year ago from current date + 30 days.
So if today is Feb 1st 2007, it shows all orders from feb 1st 2006 to march 2 2006 and allows the operator to click a button and have an email reminder sent to the customer asking if they'd like to reorder. Easy enough and done.
HOWEVER, suppose the customer doesn't order on a yearly basis? Maybe it's a 6 month order cycle or 7 month, or something non-even, like every may, oct and dec?
What I've tried is adding up all the months that a customer orders, so in the past 7 years, ignoring the year part of the dates, which month did the customer most often order?
Seams to give some decent results but I'd like to more accurate figures. Any suggestions?