Comparing dates and values from the same table.

[Guest]

I have a table where I want to compare two results with each other from
different dates.
The fields are of my table are: MemberID, Date Of Service, Value

I've been lookining for ways to get my query to give me the following
columns in my query.
Member ID ,1st Date of Service, Value associated to 1st date of service,
Last Date of Service, Value associated to last date of service.

How do I set up this query so I can get the 1st Date Of Service and value
and Last DOS and value?

Thanks!!

 

[John W. Vinson]

I have a table where I want to compare two results with each other from
different dates.
The fields are of my table are: MemberID, Date Of Service, Value

I've been lookining for ways to get my query to give me the following
columns in my query.
Member ID ,1st Date of Service, Value associated to 1st date of service,
Last Date of Service, Value associated to last date of service.

How do I set up this query so I can get the 1st Date Of Service and value
and Last DOS and value?

Thanks!!

A Self Join query with a couple of Subqueries will do the trick:

SELECT A.[Member ID], A.[Date of Service] AS FirstDateOfService, A.[Value] As
FirstDatesValue, B.[Date of Service] AS LatestDateOfService, B.[Value] AS
LatestDatesValue
FROM yourtable AS A INNER JOIN yourtable AS B
ON A.[Member ID] = B.[Member ID]
WHERE A.[Date of Service] = (SELECT Min(C.[Date of Service]) FROM yourtable AS
C WHERE C.[Member ID] = A.[MemberID])
AND B.[Date of Service] = (SELECT Max(D.[Date of Service]) FROM yourtable AS D
WHERE D.[Member ID] = A.[MemberID]);


John W. Vinson [MVP]