Customer Due Dates

[Guest]

I am using Access 2000. What I want to accomplish is a report that will
display a list of all customers that are due for service.

Currently, I have a service entry form that allows me to enter the date in
which our company serviced an individual customer. The form shows all of the
times that we serviced an individual customer, though. We need to service a
customer every 7, 10, or 14 days depending on the customer preference.

I have been trying to use a query to look at the ServiceDate and return all
Customer info for those that have not been serviced according to
ScheduledInterval.

Ex, Customer "Smith" was serviced on Jan 1, 2005 and Jan 10, 2005. He is to
be serviced every 10 days. So his next Due Date is Jan 20, 2005

The problem is that I need to get the query to only look at the LastDate
that we performed a service otherwise it will return all the Customers
because it will see a service that was performed, say, last month and display
that customer even though we may have performed service for them within their
ScheduleInterval.

I would appreciate it greatly if someone could point me in the right
direction.

Thank you.

 

[AlCamp]

Curt,
If your ServiceInterval contains a number of days bewteen servicings...
Then, you can use the value of that field to subtract ServiceInterval from
today's Date, and get all records where he ServiceDate exceeds that range.
If the individual customer's ServiceDate is less than today's Date minus
that customer's ServiceInterval... then show that record.

In other words, the query to return "Overdues" will be specific to each
customer, and their individual ServiceInterval
hth
Al Camp

 

[Guest]

hi,
You can use the aggragate function MAX on the date then
group by on the customer. this will pick the maximum date
(last) of each customer.
be careful though. using an aggragate function with group
by can be tricky and if not use right, can return too much
info. this may only work for the 2 fields date and
customer.
good luck.