need help on simple(?) query

[John Fitzgerald]

Hi, I have the following two tables:
1. Client Table
ID - Primary Key

2. Enrollment Table
EnrollID - Primary Key
ClientID - Foreign Key
Enroll Date
I need to find the earliest enroll date for each client, but I can't
figure out how to do this, even with subqueries.
Any tips would be much appreciated.

[Jim Allensworth]

On 12 Jul 2004 10:14:54 -0700, "John Fitzgerald"
<jr**********@gmail.com> wrote:

Hi, I have the following two tables:
1. Client Table
ID - Primary Key

2. Enrollment Table
EnrollID - Primary Key
ClientID - Foreign Key
Enroll Date
I need to find the earliest enroll date for each client, but I can't
figure out how to do this, even with subqueries.
Any tips would be much appreciated.

Try using First for the enrollment date. Somethin like ...

SELECT tblClient.ClientID, First(EnrollDate) AS FirstEnrolled
FROM tblClient INNER JOIN tblEnrollment ON tblClient.ClientID =
tblEnrollment.ClientID
GROUP BY tblClient.ClientID;

- Jim

[PC Datasheet]

Jim,

There's no guarantee this will work! First will only give you the first date
that was entered. If the first date that was entered was not the earliest date,
the returned record will not be correct.

Rather, the poster needs to use a totals query where Group By in the EnrollDate
field is changed to Min.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Jim Allensworth" <ji****@datacentricsolutions.com> wrote in message
news:40****************@news.west.earthlink.net...

On 12 Jul 2004 10:14:54 -0700, "John Fitzgerald"
<jr**********@gmail.com> wrote:

Hi, I have the following two tables:
1. Client Table
ID - Primary Key

2. Enrollment Table
EnrollID - Primary Key
ClientID - Foreign Key
Enroll Date
I need to find the earliest enroll date for each client, but I can't
figure out how to do this, even with subqueries.
Any tips would be much appreciated.

Try using First for the enrollment date. Somethin like ...

SELECT tblClient.ClientID, First(EnrollDate) AS FirstEnrolled
FROM tblClient INNER JOIN tblEnrollment ON tblClient.ClientID =
tblEnrollment.ClientID
GROUP BY tblClient.ClientID;

- Jim

[Jim Allensworth]

On Mon, 12 Jul 2004 18:51:56 GMT, "PC Datasheet" <no****@nospam.spam>
wrote:

There's no guarantee this will work! First will only give you the first date
that was entered. If the first date that was entered was not the earliest date,
the returned record will not be correct.

Yep, you are correct Min would be better.

SELECT tblClient.ClientID, Min(EnrollDate) AS FirstEnrolled
FROM tblClient INNER JOIN tblEnrollment ON tblClient.ClientID =
tblEnrollment.ClientID
GROUP BY tblClient.ClientID;

- Jim

<Ads, etc. snipped>