By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,238 Members | 1,659 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,238 IT Pros & Developers. It's quick & easy.

need help on simple(?) query

P: n/a
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.

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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>
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.