Query help needed

[Annette]

I have a table of clients and a table of services the client is
enrolled in. The relationship between client and services is one to
many -- a client can have many services.

The client's service table consists of the clienID, program enrolled,
enrollment start and end date.

Some clients will be enrolled in many programs.

I would like a query that specifically lists all the clients that were
enrolled in program 1030 that are now enrolled in program 1041. The
detail records for services for a client would show that they had a
start/end date for program 1030 and then a start and possibly an end
date for program 1041. It should follow protocol that program 1030
ended on one day and the start of 1041 started the next day.

How do I create this?

 

[John Spencer]

It can be done with three queries (in one query if your table and field names
consist of ONLY letters and numbers (no spaces, no non-letter characters such
as +.

Three query approach.
Query 1 return all clients and service data for attendees to program 1030
query 2 return all clients and service data for attendees to program 1041
Query 3 Add query 1 and query 2 to a new query. Join the clientid between the
two queries.

SELECT Clients.*, Service.Start, Service.End
FROM Clients INNER JOIN Services
ON Clients.ClientID = Services.ClientID
WHERE [Program Enrolled] = "1030"
(drop the quotes if the field is a number field and not a text field)


SELECT Clients.*, Service.Start, Service.End
FROM Clients INNER JOIN Services
ON Clients.ClientID = Services.ClientID
WHERE [Program Enrolled] = "1041"

SELECT Query1.*, Query2.Start as P1041Start, Query2.End as P1041End
FROM Query1 INNER JOIN Query2
ON Query1.ClientID = Query2.ClientID
WHERE Query1.EndDate = Query2.StartDate +1



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County