Sounds like it should be easy, but is it? - date calculation question

  • Thread starter Thread starter JM
  • Start date Start date
J

JM

G'day.

I'm trying to extract from an Access 2003 database, a list of members
that received a service during the last week. The only catch is that
the ones I want cannot have received any other service during the 90
days prior to that. I thought this was going to be easy. Silly me.

I wrote a query that asks for the member_ids of those who received a
service (dateofservice) between the beginning of last week and the end
of last week. But that doesn't tell me which of those did not get any
other service in the 90 days before. The service table holds records
of every service the member ever received. Is there a query
expression that I can use? Do I need to make tables of some kind and
compare them?
The more I think about this, the more confused I get.
Any help is greatly appreciated.
 
well, speaking in general terms, you could create a query to pull all
members with a service record during the last week. write a second query to
pull all members with a service record during the 90 days prior to last
week. then use the Query Wizard to write an "Unmatched Records" query that
returns all the members from the first query who do *not* appear in the
second query.

hth
 
G'day.

I'm trying to extract from an Access 2003 database, a list of members
that received a service during the last week. The only catch is that
the ones I want cannot have received any other service during the 90
days prior to that. I thought this was going to be easy. Silly me.

I wrote a query that asks for the member_ids of those who received a
service (dateofservice) between the beginning of last week and the end
of last week. But that doesn't tell me which of those did not get any
other service in the 90 days before. The service table holds records
of every service the member ever received. Is there a query
expression that I can use? Do I need to make tables of some kind and
compare them?
The more I think about this, the more confused I get.
Any help is greatly appreciated.
Click to expand...

It's not trivial, actually! You need a "Correlated Subquery". Try:

SELECT MemberID, <other fields>
FROM Members INNER JOIN Services
ON Services.MemberID = Members.MemberID
WHERE SErvices.dateofservice > DateAdd("d", -7, Date())
AND NOT EXISTS
(SELECT * FROM Services AS OldService
WHERE OldServices.MemberID = Members.MemberID
AND OldServices.DateOfService BETWEEN DateAdd("d", -90, Date()) AND
DateAdd("d", -8, Date()))

Adjust table and fieldnames to match yours of course.


John W. Vinson[MVP]
 
What did you write in to create the first function - those who had service in
the past week?
 
Back
Top