NUMBERING GROUP OF RECORDS IN QUERY

[ClaudioGuru]

My problem is to be able to number the records in a query according to
the group they pertain to.

Example

GROUP MEMBER NUMBER
A TOM 1
A MARK 2
A BILL 3
B DAVID 1
B STEVE 2
C MARK 1
C JEFF 2
C SEAN 3

Is there a way I can achieve this?

Thanks to anybody that can help

Claudio

 

[Marshall Barton]

My problem is to be able to number the records in a query according to
the group they pertain to.

Example

GROUP MEMBER NUMBER
A TOM 1
A MARK 2
A BILL 3
B DAVID 1
B STEVE 2
C MARK 1
C JEFF 2
C SEAN 3

Is there a way I can achieve this?

This can only do this if you have one (or more) fields that
can be used to determine a unique sort order for the records
in a group. You example numbers the records in an arbitrary
manner, but an algorithm doen't do that.

If you wanted them numbered in member name alphabetical
order and there are no duplicate names within a group, then
you could use something like:

SELECT T.Group,
T.Member,
(SELECT Count(*)
FROM thetable As X
WHERE X.Member <= T.Member) As Num
FROM thetable As T

 

[Steve]

Marsh,

Starting at the beginning, if you had the members and year of birth (YOB),
how would you divide the members into groups A, B and C?

Thanks!

MEMBER YOB
TOM 1954
MARK 1954
BILL 1954
DAVID 1965
STEVE 1965
MARK 1972
JEFF 1972
SEAN 1972

 

[Marshall Barton]

How *I* divide them into groups? Maybe, by throwing darts?

Seriously, **you** must have some kind of definition of what
makes a group. Whatever it is, you need to express the
definition in such an unambiguous way that it can be
translated into something that a computer can deal with.
Based on your examples, it looks like you are using the YOB
as the group identifier, but then what does a group mean if
everyone's YOB is in a different year?

How does this question relate to your original question?
I'm afraid that I really don't understand where this is
going.

 

[Steve]

Sorry, I was trying to keep it simple!

Group YOB
A !954 - 1964
B 1965 - 1971
C 1972 +

MEMBER YOB
TOM 1954
MARK 1957
BILL 1958
DAVID 1965
STEVE 1967
MARK 1972
JEFF 1975
SEAN 1979

The members would be assigned to a group A, B or C by YOB and then the
members in each group would be numbered according to their YOB. This being a
simple example, I can see using the IIF function or Switch function to
assign the members to a group. I'm hoping however, you will show me a
subquery solution that could be applied to more complex sets of data. I
follow your previous subquery solution to number the members in alphabetical
order and I can adapt your solution to number the members by YOB. You don't
need to show me that part.

Thanks!

How *I* divide them into groups? Maybe, by throwing darts?

Seriously, **you** must have some kind of definition of what
makes a group. Whatever it is, you need to express the
definition in such an unambiguous way that it can be
translated into something that a computer can deal with.
Based on your examples, it looks like you are using the YOB
as the group identifier, but then what does a group mean if
everyone's YOB is in a different year?

How does this question relate to your original question?
I'm afraid that I really don't understand where this is
going.
--
Marsh
MVP [MS Access]

Starting at the beginning, if you had the members and year of birth (YOB),
how would you divide the members into groups A, B and C?

MEMBER YOB
TOM 1954
MARK 1954
BILL 1954
DAVID 1965
STEVE 1965
MARK 1972
JEFF 1972
SEAN 1972

 

[Marshall Barton]

Create a table named Groups with three fields:
GrpId Low High
Old 0 1953
A 1954 1964
B 1965 1971
C 1972 2006
Err 2006 9999

Then the query would be:

SELECT GrpID, Member, YOB
FROM thetable LEFT JOIN Groups
ON YOB >= Low And YOB <= High
ORDER BY GrpID, YOB, Member

Rather than try to munge both operations into a single
qurey, Try using the above query as the data source (table)
in the numbering query.

 

[Steve]

Thanks, Marsh!

Create a table named Groups with three fields:
GrpId Low High
Old 0 1953
A 1954 1964
B 1965 1971
C 1972 2006
Err 2006 9999

Then the query would be:

SELECT GrpID, Member, YOB
FROM thetable LEFT JOIN Groups
ON YOB >= Low And YOB <= High
ORDER BY GrpID, YOB, Member

Rather than try to munge both operations into a single
qurey, Try using the above query as the data source (table)
in the numbering query.
--
Marsh
MVP [MS Access]

Group YOB
A !954 - 1964
B 1965 - 1971
C 1972 +

MEMBER YOB
TOM 1954
MARK 1957
BILL 1958
DAVID 1965
STEVE 1967
MARK 1972
JEFF 1975
SEAN 1979

The members would be assigned to a group A, B or C by YOB and then the
members in each group would be numbered according to their YOB. This being
a
simple example, I can see using the IIF function or Switch function to
assign the members to a group. I'm hoping however, you will show me a
subquery solution that could be applied to more complex sets of data. I
follow your previous subquery solution to number the members in
alphabetical
order and I can adapt your solution to number the members by YOB. You
don't
need to show me that part.

Thanks!