Find Dates using Max() or ?

[geod]

I have a small customer database, 6,000+ records. This is linked to a Invoicing DB. I need to find all customers that last ordered in 1999, 2000, 2001 and 2002, and so forth. I thought setting up Max(InvoiceDate) calculation field in Invoices would allow me to search from customers inserting a range in the field ie. 1.1.2000...12.31.2000. But I am getting unpredictible results,... then I tried this creating the calc in customers over a relationship. But it will not work either. I do not necessarily need to use Max() but I do need the search to work.

I feel like it is staring me in the face but I cannot see it. Forum I need your wisdom. Thank You!

[Vaughan]

If the records were pieces of paper, how would you manually perform the task?

[geod]

Hi Vaughan, I would stack the Invoices by customer, sorted by date with the latest date on top. Then i would arrange the stacks so all of the last purchases were in groups by year.Then i would have all of the customers for the various years.

I am thinking that putting a year function in front of the Invoice date may improve the find. I am not in front of the computer with the records right now. But previously I had imagined that with a relationship from customer to invoice header via the customer key, by placing the field on the search form of the customer database that the find would be able to get those customers by putting in a range for the year you are looking for, into the Max(invoice date) field. But it is not working. I appreciate your challenging me to think, but "the noggin isn't nodding" at the moment.

[danjacoby]

Have a relationship where the invoices are sorted by Invoice Date in descending order. Then create a Calc field (that returns a date) in the main DB that equals the related Invoice Date -- it will return the most recent Invoice Date for each customer.

Then search on that field.