First|Last Function

Field “code� is a text variable in a table and it can be sorted properly in
the same way as a numeric variable. In a query, I want the dataset shows the
first record ONLY in the “Code� for each ID. I tried to use Function “First�
or “Last� in query design grid after I first sorted the records, it did not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
…

Your definitions of "First" and "Last" are probably not the same as Access'
definitions.

If you mean you want the "smallest" and "largest", try using Minimum and
Maximum.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"May" wrote in message
...
Field "code" is a text variable in a table and it can be sorted properly
in
the same way as a numeric variable. In a query, I want the dataset shows
the
first record ONLY in the "Code" for each ID. I tried to use Function
"First"
or "Last" in query design grid after I first sorted the records, it did
not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
.

Hi, Jeff:

Thank you for your help. I did not mean the numeric number for the largest
or smallest; I mean the FIRST or LAST record in a dataset.

I need to get only one record and the first one for each ID in a couple of
records (the # of records depends on the ID’s activity). As required, the
query produces a dataset which has distinct records.

Does the “First� and “Last� function in Access mean the First and Last
record in a dataset? If not, what I should do?

Thanks for help.

"May" wrote:

Field “code� is a text variable in a table and it can be sorted properly in
the same way as a numeric variable. In a query, I want the dataset shows the
first record ONLY in the “Code� for each ID. I tried to use Function “First�
or “Last� in query design grid after I first sorted the records, it did not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
…

First and Last do mean the first and last in the query's
**source** dataset. Another thing you need to be aware of
is that a table's records are ***not*** ordered. This means
that without one or more fields in your table that can be
used to create a unique sort, there is no such thing as the
first or last record for any subset of records.

If you do have a set of fields in the table for a unique
sort order, then you can use a query that sorts the records
as the basis for your query.
--
Marsh
MVP [MS Access]


May wrote:
Thank you for your help. I did not mean the numeric number for the largest
or smallest; I mean the FIRST or LAST record in a dataset.

I need to get only one record and the first one for each ID in a couple of
records (the # of records depends on the ID’s activity). As required, the
query produces a dataset which has distinct records.

Does the “First” and “Last” function in Access mean the First and Last
record in a dataset? If not, what I should do?

"May" wrote:

Field “code” is a text variable in a table and it can be sorted properly in
the same way as a numeric variable. In a query, I want the dataset shows the
first record ONLY in the “Code” for each ID. I tried to use Function “First”
or “Last” in query design grid after I first sorted the records, it did not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
…

Marshall,

Thank you for telling me that the records in a table are not ordered. Should
I try subquery?

"Marshall Barton" wrote:

First and Last do mean the first and last in the query's
**source** dataset. Another thing you need to be aware of
is that a table's records are ***not*** ordered. This means
that without one or more fields in your table that can be
used to create a unique sort, there is no such thing as the
first or last record for any subset of records.

If you do have a set of fields in the table for a unique
sort order, then you can use a query that sorts the records
as the basis for your query.
--
Marsh
MVP [MS Access]


May wrote:
Thank you for your help. I did not mean the numeric number for the largest
or smallest; I mean the FIRST or LAST record in a dataset.

I need to get only one record and the first one for each ID in a couple of
records (the # of records depends on the ID’s activity). As required, the
query produces a dataset which has distinct records.

Does the “First� and “Last� function in Access mean the First and Last
record in a dataset? If not, what I should do?

"May" wrote:

Field “code� is a text variable in a table and it can be sorted properly in
the same way as a numeric variable. In a query, I want the dataset shows the
first record ONLY in the “Code� for each ID. I tried to use Function “First�
or “Last� in query design grid after I first sorted the records, it did not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
…

A subquery (instead of a separate query) might work, but the
critical point is being able to use a unique sort order to
determine "first" and "last".

I'd be happy to suggest something but you have to explain
how to use what fields to create the unique sorting.
--
Marsh
MVP [MS Access]


May wrote:
Thank you for telling me that the records in a table are not ordered. Should
I try subquery?

"Marshall Barton" wrote:
First and Last do mean the first and last in the query's
**source** dataset. Another thing you need to be aware of
is that a table's records are ***not*** ordered. This means
that without one or more fields in your table that can be
used to create a unique sort, there is no such thing as the
first or last record for any subset of records.

If you do have a set of fields in the table for a unique
sort order, then you can use a query that sorts the records
as the basis for your query.


May wrote:
Thank you for your help. I did not mean the numeric number for the largest
or smallest; I mean the FIRST or LAST record in a dataset.

I need to get only one record and the first one for each ID in a couple of
records (the # of records depends on the ID’s activity). As required, the
query produces a dataset which has distinct records.

Does the “First” and “Last” function in Access mean the First and Last
record in a dataset? If not, what I should do?

"May" wrote:

Field “code” is a text variable in a table and it can be sorted properly in
the same way as a numeric variable. In a query, I want the dataset shows the
first record ONLY in the “Code” for each ID. I tried to use Function “First”
or “Last” in query design grid after I first sorted the records, it did not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
…

Here is the code I used before. I did not get what I wanted. Your help is
much appreciated. Field "Term" is text not number.

A scenario could be like this: a student could be admitted more than once in
the same program (withdrew then back) and transferred among different campus.
To determine if a student is promoted to a higher level is to check the
enrollment to a required class (Class). The earliest Term a student took
either HIST316 or DRAM300 is a level 3 student. The query should keep the
first Enrollment Date; the first enrolled CampusName, the first Term this
person is promoted to level 3 (the term a student took the required course),
and the course number.

ID CampusName EnrollDate Subject Level Term Class Mark
1 A Sep-96 HIST 3 199909 316 48
1 B Sep-96 HIST 3 200009 316 61
2 B Sep-97 HIST 3 199809 316 56
2 B Sep-99 HIST 3 200109 316 68
3 C Sep-96 HIST 3 199809 316 W
3 C Sep-99 HIST 3 199909 316 58
4 A Sep-96 HIST 3 199809 316 W
4 C Sep-96 HIST 3 200009 316 58
4 C Sep-96 DRAM 3 200209 300 58
5 D Sep-96 DRAM 3 200409 300 63
5 D Sep-00 DRAM 3 200409 300 63


What I wanted is as follows:
ID CampusName EnrollDate Subject Level Term Class Mark
1 A Sep-96 HIST 3 199909 316 48
2 B Sep-97 HIST 3 199809 316 56
3 C Sep-96 HIST 3 199809 316 W
4 A Sep-96 HIST 3 199809 316 W
5 D Sep-96 DRAM 3 200409 300 63


SELECT DISTINCT tblStudent.StudntID, tblAdmission.CampusName,
Min(tblAdmissionion.EnrollDate) AS MinOfEnrollDate, tblStudent.Level,
tblStudentCourse.Term, tblStudentCourse.CoursNum, tblStudentCourse.Grade

FROM ((tblStudent INNER JOIN tblStudentCourse ON tblStudent.StudntID =
tblStudentCourse.StudntID) INNER JOIN tblAdmission ON tblStudent.StudntID =
tblAdmission.StudntID) INNER JOIN tblStudentName ON tblStudent.StudntID =
tblStudentName.StudntID

WHERE (((tblStudentCourse.Subject)="HIST" Or
(tblStudentCourse.Subject)="DRAM") AND ((tblStudentCourse.CoursNum)="316"))
Or (tblStudentCourse.CoursNum)="300"))
ORDER BY Min(tblAdmission.AdmDate)


"Marshall Barton" wrote:

A subquery (instead of a separate query) might work, but the
critical point is being able to use a unique sort order to
determine "first" and "last".

I'd be happy to suggest something but you have to explain
how to use what fields to create the unique sorting.
--
Marsh
MVP [MS Access]


May wrote:
Thank you for telling me that the records in a table are not ordered. Should
I try subquery?

"Marshall Barton" wrote:
First and Last do mean the first and last in the query's
**source** dataset. Another thing you need to be aware of
is that a table's records are ***not*** ordered. This means
that without one or more fields in your table that can be
used to create a unique sort, there is no such thing as the
first or last record for any subset of records.

If you do have a set of fields in the table for a unique
sort order, then you can use a query that sorts the records
as the basis for your query.


May wrote:
Thank you for your help. I did not mean the numeric number for the largest
or smallest; I mean the FIRST or LAST record in a dataset.

I need to get only one record and the first one for each ID in a couple of
records (the # of records depends on the ID’s activity). As required, the
query produces a dataset which has distinct records.

Does the “First� and “Last� function in Access mean the First and Last
record in a dataset? If not, what I should do?

"May" wrote:

Field “code� is a text variable in a table and it can be sorted properly in
the same way as a numeric variable. In a query, I want the dataset shows the
first record ONLY in the “Code� for each ID. I tried to use Function “First�
or “Last� in query design grid after I first sorted the records, it did not
work.

Would you please help me with an example for this?

ID Code
1 122005
1 122305
1 122309
2 122406
2 122409
2 122409
3 122005
3 122305
…

May wrote:

Here is the code I used before. I did not get what I wanted. Your help is
much appreciated. Field "Term" is text not number.

A scenario could be like this: a student could be admitted more than once in
the same program (withdrew then back) and transferred among different campus.
To determine if a student is promoted to a higher level is to check the
enrollment to a required class (Class). The earliest Term a student took
either HIST316 or DRAM300 is a level 3 student. The query should keep the
first Enrollment Date; the first enrolled CampusName, the first Term this
person is promoted to level 3 (the term a student took the required course),
and the course number.

ID CampusName EnrollDate Subject Level Term Class Mark
1 A Sep-96 HIST 3 199909 316 48
1 B Sep-96 HIST 3 200009 316 61
2 B Sep-97 HIST 3 199809 316 56
2 B Sep-99 HIST 3 200109 316 68
3 C Sep-96 HIST 3 199809 316 W
3 C Sep-99 HIST 3 199909 316 58
4 A Sep-96 HIST 3 199809 316 W
4 C Sep-96 HIST 3 200009 316 58
4 C Sep-96 DRAM 3 200209 300 58
5 D Sep-96 DRAM 3 200409 300 63
5 D Sep-00 DRAM 3 200409 300 63


What I wanted is as follows:
ID CampusName EnrollDate Subject Level Term Class Mark
1 A Sep-96 HIST 3 199909 316 48
2 B Sep-97 HIST 3 199809 316 56
3 C Sep-96 HIST 3 199809 316 W
4 A Sep-96 HIST 3 199809 316 W
5 D Sep-96 DRAM 3 200409 300 63


SELECT DISTINCT tblStudent.StudntID, tblAdmission.CampusName,
Min(tblAdmissionion.EnrollDate) AS MinOfEnrollDate, tblStudent.Level,
tblStudentCourse.Term, tblStudentCourse.CoursNum, tblStudentCourse.Grade

FROM ((tblStudent INNER JOIN tblStudentCourse ON tblStudent.StudntID =
tblStudentCourse.StudntID) INNER JOIN tblAdmission ON tblStudent.StudntID =
tblAdmission.StudntID) INNER JOIN tblStudentName ON tblStudent.StudntID =
tblStudentName.StudntID

WHERE (((tblStudentCourse.Subject)="HIST" Or
(tblStudentCourse.Subject)="DRAM") AND ((tblStudentCourse.CoursNum)="316"))
Or (tblStudentCourse.CoursNum)="300"))
ORDER BY Min(tblAdmission.AdmDate)


Well., this sure clarifies the issue. Just goes to show how
we can waste time chasing down a mistaken guess of how to do
something instead of just solving the original problem.

We can forget about the First/Last questions use this kind
of (untested) query:

SELECT tblStudent.StudntID,
tblAdmission.CampusName,
tblAdmissionion.EnrollDate AS EarliestEnrollDate,
tblStudent.Level,
tblStudentCourse.Term,
tblStudentCourse.CoursNum,
tblStudentCourse.Grade
FROM ((tblStudent INNER JOIN tblStudentCourse
ON tblStudent.StudntID = tblStudentCourse.StudntID)
INNER JOIN tblAdmission
ON tblStudent.StudntID = tblAdmission.StudntID)
INNER JOIN tblStudentName
ON tblStudent.StudntID = tblStudentName.StudntID
WHERE ((tblStudentCourse.Subject)="HIST"
And tblStudentCourse.CoursNum)="316")
Or (tblStudentCourse.Subject)="DRAM"
And tblStudentCourse.CoursNum)="300"))
And tblAdmissionion.EnrollDate =
(SELECT Min(X.EnrollDate)
FROM tblStudent As S
INNER JOIN tblStudentCourse As C
ON S.StudntID = C.StudntID
WHERE S.StudntID = tblStudent.StudntID
And S.Level = tblStudent.Level
And C.Subject = tblStudentCourse.Subject
And C.CoursNum = tblStudentCourse.CoursNum)
ORDER BY Min(tblAdmission.AdmDate)

I'm not sure I got the subquery's Joins right, but I didn't
see a reason for including tables not needed(?) to find the
enroll dates.

--
Marsh
MVP [MS Access]