SELECTING A PARTICULAR ROW ID IN A RECORDSET

[Glint]

Hi Guys,

I like the domain aggregate function DFirst, DLast, DLookup etc. But how do
I select, say, the 17th record of a table or query when I have no idea what
the record contains? For example, if I know for certain that a query returns
5 records, how do I select the second or the third ot fourth?

 

[boblarson]

Considering that, unless you assign some meaninful order (such as a date/time
stamp), Access doesn't store records in any meaningful order within the
table, you really can't be assurred that you can select the 17th record and
then select the 17th record a day from now and it be the same record.

What are you ACTUALLY trying to accomplish? There may be a better way.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Glint]

Thanx Bob,
There are 17 groups of students, and each group has two to four leaders. I
am building a query, and in the query, I want the leaders selected as
Leader1, Leader2...4 into separate fields. I can use DFirst and DLast to
extract Leader1 and Leader4 into their fields. But how about Leader2 and
Leader3? In the original table, they were just placed there as leaders, no
differentiating marks.

 

[boblarson]

You should have something within your table that identifies a person as a
leader. Then you can identify them in your query.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
If my post was helpful to you, please rate the post.
__________________________________

 

[Jeanette Cunningham]

Hi Glint,
there are many answers on this newsgroup that show you how to count and rank
in a query.
Sounds as though this is what you want to.

Jeanette Cunningham

 

[Glint]

Hi Jeanette,
The problem is as simple as it sounds: there are four or more records in a
table. I want the names in those records selected into fields in my new
query. I can use DFirst aggregate function to get the first name, and DLast
function to retrieve the last name. So far so good. The problem is what to
use in my query to retrieve names other than first and last.
Please suggest a formula I can use in my query, assuming of course that the
original table or recordset has been sorted in a particular order.

 

[Jeanette Cunningham]

Glint,
if it were that simple, you wouldn't be asking on this newsgroup.
We will need the details of your table - the fields and a sample few rows
(change the data to protect privacy) of your table.

Jeanette Cunningham

 

[Glint]

Thanks, Jeanette.
I did not mean that the solution was simple, but that stating the problem
looked so. Here is the vClassArahatas query I was building:

SELECT SatsangClassInaugurated.*,
DFirst("[Arahata]","vArahatas","[InaugurationID]=" & [InaugurationID]) AS
ArahataI, IIf(DCount("*","SatsangClassArahatas","[InaugurationID]=" &
[InaugurationID])=1,Null,DLast("[Arahata]","vArahatas","[InaugurationID]=" &
[InaugurationID])) AS ArahataII
FROM SatsangClassInaugurated;

It has InaugurationID (primary) field among others.

The vArahatas query is as follows:

SELECT SatsangClassArahatas.InaugurationID, [Surname] & ", " & [Firstnames]
AS Arahata
FROM Membership INNER JOIN SatsangClassArahatas ON Membership.SerialNumber =
SatsangClassArahatas.Arahata;

They work very well. The problem I have is how to include a second or a
third Arahata as Arahata2 or Arahata3 in my vClassArahatas query in a simple
manner similar to using the domain aggregate DFirst or DLast functions. This
is because I want to display them in a form based on the query.

I sincerely look forward to any help.

 

[Ken Sheridan]

Lets assume you have tables StudentGroups and Leaders, with the latter having
a foreign key StudentGroup column referncing the key of the former, then you
can use a series of subqueries, each restricting its result set to one row by
means of a further subquery, e.g.

SELECT StudentGroup,
(SELECT Leader
FROM Leaders AS L1
WHERE L1.StudentGroup = StudentGroups.StudentGroup
AND (SELECT COUNT(*)
FROM Leaders AS L2
WHERE L2.StudentGroup = L1.StudentGroup
AND L2.Leader < L1.Leader)=0)
AS Leader1,
(SELECT Leader
FROM Leaders AS L3
WHERE L3.StudentGroup = StudentGroups.StudentGroup
AND (SELECT COUNT(*)
FROM Leaders AS L4
WHERE L4.StudentGroup = L3.StudentGroup
AND L4.Leader < L3.Leader)=1)
AS Leader2,
(SELECT Leader
FROM Leaders AS L5
WHERE L5.StudentGroup = StudentGroups.StudentGroup
AND (SELECT COUNT(*)
FROM Leaders AS L6
WHERE L6.StudentGroup = L5.StudentGroup
AND L6.Leader < L5.Leader)=2)
AS Leader3,
(SELECT Leader
FROM Leaders AS L7
WHERE L7.StudentGroup = StudentGroups.StudentGroup
AND (SELECT COUNT(*)
FROM Leaders AS L8
WHERE L8.StudentGroup = L7.StudentGroup
AND L8.Leader < L7.Leader)=3)
AS Leader4
FROM StudentGroups;

This would return the leaders in four columns in alphabetical order from
left to right. If the Leaders table has the names as separate columns, e.g
FirstName and LastName then concatenate them:

SELECT StudentGroup,
(SELECT FirstName & " " & LastName
FROM Leaders AS L1
WHERE L1.StudentGroup = StudentGroups.StudentGroup
AND (SELECT COUNT(*)
FROM Leaders AS L2
WHERE L2.StudentGroup = L1.StudentGroup
AND L2.LastName & FirstName < L1.LastName & FirstName)=0)
AS Leader1,
< and so on>

Note that in his case the order of concatenation is reversed in the subquery
so that the names are sorted by last name then first name.

Ken Sheridan
Stafford, England

 

[Jeanette Cunningham]

Glint,
We will not use DFirst and DLast, but do it completely differently, more
like Ken's suggestion.
There is not any magic formula to give the result you want.
It is a process of building queries until we get the result that suits this
unique situation.

From your post I can see 3 tables:
SatsangClassInaugurated
SatsangClassArahatas
Membership

I created tables Membership and StasangClassArahatas
I made the query vArahatas and it looked like this:
InaugurationID Arahta
1 LastA aa
2 LastB bb
3 LastC cc
4 LastD dd


I am a bit lost figuring out which bit of the query refers to groups of
students, which bit refers to leaders, please clarify for me.
Please post quite a few rows from your query vArahatas.
Please also post a sample of how you want the final result to look.
How does the table SatsangClassInaugurated fit with the other 2 tables?

Jeanette Cunningham

Thanks, Jeanette.
I did not mean that the solution was simple, but that stating the problem
looked so. Here is the vClassArahatas query I was building:

SELECT SatsangClassInaugurated.*,
DFirst("[Arahata]","vArahatas","[InaugurationID]=" & [InaugurationID]) AS
ArahataI, IIf(DCount("*","SatsangClassArahatas","[InaugurationID]=" &
[InaugurationID])=1,Null,DLast("[Arahata]","vArahatas","[InaugurationID]="
&
[InaugurationID])) AS ArahataII
FROM SatsangClassInaugurated;

It has InaugurationID (primary) field among others.

The vArahatas query is as follows:

SELECT SatsangClassArahatas.InaugurationID, [Surname] & ", " &
[Firstnames]
AS Arahata
FROM Membership INNER JOIN SatsangClassArahatas ON Membership.SerialNumber
=
SatsangClassArahatas.Arahata;

They work very well. The problem I have is how to include a second or a
third Arahata as Arahata2 or Arahata3 in my vClassArahatas query in a
simple
manner similar to using the domain aggregate DFirst or DLast functions.
This
is because I want to display them in a form based on the query.

I sincerely look forward to any help.
--
Glint

Glint,
if it were that simple, you wouldn't be asking on this newsgroup.
We will need the details of your table - the fields and a sample few rows
(change the data to protect privacy) of your table.

Jeanette Cunningham

 

[Krzysztof Pozorek [MVP]]

(...)

I like the domain aggregate function DFirst, DLast, DLookup etc. But how
do
I select, say, the 17th record of a table or query when I have no idea
what
the record contains? For example, if I know for certain that a query
returns
5 records, how do I select the second or the third ot fourth?

The aggregate function, which you need, may be on example similar to this:

DLast("Field1","Table1","Field1 IN (SELECT TOP 2 Field1 FROM Table1)")
- for second record

DLast("Field1","Table1","Field1 IN (SELECT TOP 3 Field1 FROM Table1)")
- for third record

.... and so on. You can use also variable in such way:
Dim Nr As Long
Nr = 3
MsgBox DLast("Field1", "Table1", _
"Field1 IN (SELECT TOP " & Nr & " Field1 FROM Table1)"), , _
"Record No " & Nr

K.P.
www.access.vis.pl

 

[Jeanette Cunningham]

That is new to me.
Jeanette Cunningham

(...)


The aggregate function, which you need, may be on example similar to this:

DLast("Field1","Table1","Field1 IN (SELECT TOP 2 Field1 FROM Table1)")
- for second record

DLast("Field1","Table1","Field1 IN (SELECT TOP 3 Field1 FROM Table1)")
- for third record

... and so on. You can use also variable in such way:
Dim Nr As Long
Nr = 3
MsgBox DLast("Field1", "Table1", _
"Field1 IN (SELECT TOP " & Nr & " Field1 FROM Table1)"), , _
"Record No " & Nr

K.P.
www.access.vis.pl

 

[Glint]

Thanx again, Jeanette.
Every SatsangClassInaugurated has some members designated as Arahatas. So
There is the SatsangClassInaugurated table (which takes details of the
meeting time, venue, day of the week or month etc) and then an associated
SatsangClassArahatas table (joined through the InaugurationID field) that
takes the assigned leaders or Arahatas. These leaders are numbers in the
Membership table, so the SatsangClassArahatas table is related to the
Membership table.

4 Records of the SatsangClassInaugurated look like this:

InaugurationID Zone SatsangName Venue MeetingDay MeetingTime CurrentDiscourse Book LastInauguration PostedBy Officiator
1 AMUWO ODOFIN AMUWO SATSANG 1 5TH AVENUE FESTAC 2ND SUNDAY 5:00 PM THE ECK
SATSANG I 09-Dec-2007 AJAO, RAFIU OLUGBENGA NWAGHODOH, OKAFOR JOHN ANIEMEKE
2 AMUWO ODOFIN AMUWO SATSANG 2 C CLOSE, 207 RD, FESTAC 2ND SUNDAY 4:00
PM THE ECK SATSANG III 09-Dec-2007 AJAO, RAFIU OLUGBENGA NWAGHODOH, OKAFOR
JOHN ANIEMEKE
3 AMUWO ODOFIN AMUWO SATSANG 4 ABULE ADO 2ND SUNDAY 10:30 AM ECK DREAM
I 09-Dec-2007 AJAO, RAFIU OLUGBENGA NWAGHODOH, OKAFOR JOHN ANIEMEKE
4 AMUWO ODOFIN AMUWO SATSANG 6 ABULE ADO 2ND SUNDAY 10:30 AM LETTERS OF
LIGHT AND SIOUND II 09-Dec-2007 AJAO, RAFIU OLUGBENGA NWAGHODOH, OKAFOR JOHN
ANIEMEKE

4 records of the SatsangClassArahatas look like this:

ArahataID InaugurationID Arahata
1 1 FAGBEMI, AYODELE OMOYIOLA
2 1 AKO, PAUL
3 2 NWAGHODOH, OKAFOR JOHN ANIEMEKE
4 3 BELLO, MUYIDEEN

The Membership table is quite large but a few rocrds look like this:

SerialNumber Zone Surname Firstnames ID# YearOfReg LastInitiation CurrentLevel ExpiryDate SubZone Sex DOB Status OfficeAddress HomeAddress PostalAddress E-mail OfficeTelephone HomeTelephone GSM Post Active PostedBy
6 AMUWO ODOFIN ADEPOJU GABRIEL
ADEYINKA 2154522 3 05-Apr-2001 FESTAC M Adult HOUSE 13, E CLOSE, 5TH
AVENUE FESTAC P. O. BOX 3896 FESTAC (e-mail address removed) 881304 ACTIVE
7 AMUWO ODOFIN ADEPOJU ADEYINKA
GABRIEL 2154522 3 05-Apr-2001 FESTAC M Adult HOUSE 13, E CLOSE, 5TH
AVENUE, FESTAC P. O. BOX 3896 FESTAC (e-mail address removed) 881304 ACTIVE
8 AMUWO ODOFIN AJAO RAFIU OLUGBENGA 2539754 3 03-Dec-2005 MAZAMAZA F 03
May Adult 41 OLD OJO RD, MAZAMAZA. 41 OLD OJO ROAD, MAZAMAZA P. O. BOX 5211,
FESTAC. OR P. O. BOX 5321, LAGOS. (e-mail address removed) 01.8792623 08023958108
LOCAL DIRECTOR
ACTIVE AJAO, RAFIU OLUGBENGA
9 AMUWO
ODOFIN AKERELE THERESA 2206500 2 29-Sep-2001 FESTAC F Adult ARAHATA
ACTIVE

The SatsangClassArahatas table is related to the Membership table via the
SerialNumber field.

All the forms work well; the SatsangClassInaugurated form has the
SatsangClassArahatas form as a subform. A SatsangClassInaugurated must have
at least one leader or Arahata, and not more than 4.

The problem I had arose when I tried to create a form to capture the
essentials of each SatsangClassInaugurated. In the query I was building, I
wanted to see the Arahatas associated with each SatsangClassInaugurated
displayed in separate fields. That was when I found that I could use the
domain aggregate function to see the the first or last record of the
associated SatsangClassArahatas. But then what do I use to display the second
or third, since there is no function like DSecond or DThird?

I usually use such a query and a form based on it to present a simple view
that allows me to filter the records anyway I like, insted of having to
construct many reports.

I hope I have given you a fair idea of the problem I had.
--
Glint

Glint,
We will not use DFirst and DLast, but do it completely differently, more
like Ken's suggestion.
There is not any magic formula to give the result you want.
It is a process of building queries until we get the result that suits this
unique situation.

From your post I can see 3 tables:
SatsangClassInaugurated
SatsangClassArahatas
Membership

I created tables Membership and StasangClassArahatas
I made the query vArahatas and it looked like this:
InaugurationID Arahta
1 LastA aa
2 LastB bb
3 LastC cc
4 LastD dd


I am a bit lost figuring out which bit of the query refers to groups of
students, which bit refers to leaders, please clarify for me.
Please post quite a few rows from your query vArahatas.
Please also post a sample of how you want the final result to look.
How does the table SatsangClassInaugurated fit with the other 2 tables?

Jeanette Cunningham

Thanks, Jeanette.
I did not mean that the solution was simple, but that stating the problem
looked so. Here is the vClassArahatas query I was building:

SELECT SatsangClassInaugurated.*,
DFirst("[Arahata]","vArahatas","[InaugurationID]=" & [InaugurationID]) AS
ArahataI, IIf(DCount("*","SatsangClassArahatas","[InaugurationID]=" &
[InaugurationID])=1,Null,DLast("[Arahata]","vArahatas","[InaugurationID]="
&
[InaugurationID])) AS ArahataII
FROM SatsangClassInaugurated;

It has InaugurationID (primary) field among others.

The vArahatas query is as follows:

SELECT SatsangClassArahatas.InaugurationID, [Surname] & ", " &
[Firstnames]
AS Arahata
FROM Membership INNER JOIN SatsangClassArahatas ON Membership.SerialNumber
=
SatsangClassArahatas.Arahata;

They work very well. The problem I have is how to include a second or a
third Arahata as Arahata2 or Arahata3 in my vClassArahatas query in a
simple
manner similar to using the domain aggregate DFirst or DLast functions.
This
is because I want to display them in a form based on the query.

I sincerely look forward to any help.
--
Glint

Glint,
if it were that simple, you wouldn't be asking on this newsgroup.
We will need the details of your table - the fields and a sample few rows
(change the data to protect privacy) of your table.

Jeanette Cunningham




Hi Jeanette,
The problem is as simple as it sounds: there are four or more records
in a
table. I want the names in those records selected into fields in my new
query. I can use DFirst aggregate function to get the first name, and
DLast
function to retrieve the last name. So far so good. The problem is what
to
use in my query to retrieve names other than first and last.
Please suggest a formula I can use in my query, assuming of course that
the
original table or recordset has been sorted in a particular order.
--
Glint


:

Hi Glint,
there are many answers on this newsgroup that show you how to count
and
rank
in a query.
Sounds as though this is what you want to.

Jeanette Cunningham

Hi Guys,

I like the domain aggregate function DFirst, DLast, DLookup etc. But
how
do
I select, say, the 17th record of a table or query when I have no
idea
what
the record contains? For example, if I know for certain that a query
returns
5 records, how do I select the second or the third ot fourth?

 

[Glint]

Thanks, K. P.
That is a very good idea. I appreciate it.

 

[Larry Linson]

The problem is as simple as it sounds: there are four or more records in a
table. I want the names in those records selected into fields in my new
query. I can use DFirst aggregate function to get the first name, and
DLast
function to retrieve the last name. So far so good. The problem is what to
use in my query to retrieve names other than first and last.

Please suggest a formula I can use in my query, assuming of course that
the
original table or recordset has been sorted in a particular order.

The problem you face is that you appear to want the leaders retrieved in the
order they were entered, and they are not -- repeat NOT -- stored in that
order. By definition, the records in relational tables are unordered. And,
although we see that (often) the records are retrieved in primary key order,
that is not necessarily going to be so on every retrieval. So you are
asking for an ordering that does not exist.

I am not sure that any of the clever SQL you've received is going to work
consistently, as it appears to rely on the records being in some particular
order, but on which assumption, by definition, you cannot rely. Nor do I
have a comfort level that you can rely on your assumption that DFirst and
DLast will retrieve the records you expect every time.

Larry Linson
Microsoft Access MVP

 

[Glint]

Larry,

Thanks for your post. You were right somehow when you indicated that the
suggested SQL might not work consistently: I found that to be true as I
sometimes got the first name or record repeated as number 2, or the record in
the second position repeated as the third; it was not consitent, even though
the Select TOP 2 (or 3 or 4) query worked fine.

I was not looking for an order per se; I just wanted to be able to retrieve
the names into specific fields. In the end, I had to create multiple queries
(for the TOP 2, TOP 3 and TOP 4 values) based on a sorted query of the table,
all with query parameter set to my form's InaugurationID. This time, I got
consistent results when I used the DLast aggregate function to retrieve the
second, third and fourth names, after testing for nulls.

Again, thanks for your help.