The first time an event occurs - Help please.

[Simon Jester]

Hi everyone. I want to set up a followup process for our customers.
The customers receive one or more of several services. The services
can be one of 3 types: Admin, Direct, and Support. I have a chart
which tells me which of the services falls into which category based on
their 3 digit service id. The records contain the customer id number,
the service id number, and the date the service was provided. I want to
set up a query that will tell me when 45 days have passed since the
customer first received a Direct service. The thing is, the customer
could have had several Direct services on the same day or over a period
of time. Is there a way in Access to tell which of these (possibly)
many services was the initial Direct service so I can calculate a date
to schedule a followup?
I hope I explained this clearly - I'm kinda confused myself. Any
advice you could give would be ever so greatly appreciated.
Thanks
SJ

 

[tina]

try something along the lines of

SELECT customer id number, service id number, service date
FROM table name
WHERE service id number = "direct" AND service date <= Date()-45;

you'll have to use the correct table and field names, of course, and use
whatever number is the code for "direct". but the above should at least show
you the logic.

hth

 

[John Spencer]

Use a totals query to identify the first direct service for each customer.

SELECT CustomerID, Min(DateOfService) as FirstService
FROM ServicesTable
WHERE ServiceType = "Direct"
GROUP BY CustomerID

You can build that query in the query grid.
--Add your table to a new query
--Select CustomerID, DateofService, and ServiceType fields into the grid
--Select View: Totals from the menu
-- Change "Group By" under DateOfService to Min
-- Change "Group By" under ServiceType to Where
-- Enter the criteria under ServiceType to get just the Direct records

You can now use this query to identify the earliest direct service. And if
you want you can use the DateAdd function to add 45 days to the earliest
date.
DateAdd("d",45,Min(DateOfService)

 

[Simon Jester]

Thank you John. I'll give this a try. Much appreciated.
SJ