Finding Minimum or Maximum Value 2

[swimtiger]

Hello,

I tried searching so I would not ask a repeat question, but I did not find my answer. Without any further ado:

I have a table that tracks clients check-in dates at centers. I would like my query to display a client's minimum check-in date and the center name. When I currently run my query and select Min for the date, I get the Min client check in date for each center. So, if a client visited 3 different centers on 5 different dates, my query will return 3 minimum dates for that client.

Client Check-In Center
1 06/17/03 CenterA
1 07/12/03 CenterB
1 08/14/04 CenterC

Currently, my query results in:
Client Check-In Center
1 06/17/03 CenterA
1 07/12/03 CenterB
1 08/14/04 CenterC

I would like its results to be:
Client Check-In Center
1 06/17/03 CenterA


I appreciate your help. This forum has really been a big help to me.

 

[amorous]

Try this:

SELECT TOP 1 * FROM yourtablename GROUP BY Client ORDER BY Check-In ASC

-VJ

 

[swimtiger]

AWESOME! Thank you.

 

[swimtiger]

OK, I thought this worked, but it did not. I looked at my original post and saw that I did not explain myself as well as I should have. This is what I would like to see in my query results.
- Every client who has checked-in.
- That clients first check-in date, regardless of center.
- The name of the center at the client's first check in.

THANKS!

 

[PHV]

Something like this ?
SELECT A.Client, A.Check-In, A.Center
FROM yourTable INNER JOIN
(SELECT Client, Min(Check-In) As CheckDate FROM yourTable GROUP BY Client) B
ON (A.Client=B.Client) AND (A.Check-In=B.CheckDate)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swimtiger]

I used your query, substituting your values with mine:

SELECT A.ClientID, A.PlacementDate, A.Center
FROM [AA Job Placements] INNER JOIN
(SELECT ClientID, Min(PlacementDate) As CheckDate FROM [AA Job Placements] GROUP BY ClientID) B
ON (A.ClientID=B.ClientID) AND (A.PlacementDate=B.CheckDate)
;

I get an error that says, Syntax Error in Join Expression.

 

[PHV]

Which version of access ?
You may try to create a saved query, say qry1stDate with this sql code:
SELECT ClientID, Min(PlacementDate) As CheckDate FROM [AA Job Placements] GROUP BY ClientID)
And then try this:
SELECT A.ClientID, A.PlacementDate, A.Center
FROM [AA Job Placements] INNER JOIN qry1stDate B
ON (A.ClientID=B.ClientID) AND (A.PlacementDate=B.CheckDate)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swimtiger]

Hi!

I'm running 2003 here, and the db was made with 2000. I saved the first query as qry1stdate and then ran the second query. Still getting the "Syntax error in JOIN operation."

Thanks

 

[PHV]

In 2003 no need for a saved query.
Sorry for the typo:
SELECT A.Client, A.Check-In, A.Center
FROM [AA Job Placements] [highlight]A[/highlight] INNER JOIN
(SELECT Client, Min(Check-In) As CheckDate FROM [AA Job Placements] GROUP BY Client) B
ON (A.Client=B.Client) AND (A.Check-In=B.CheckDate)
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[swimtiger]

Thank you to PHV. I appreciate your time and efforts. The quer works just like I wanted it to.

Kudos!