number records in a group, within access query

[Jeremy Phillips]

Can anyone help! I'm trying to number a series of records, each time a group
changes within a query. Tried ranking them, but this ranks all records and
not those within a group.

simplified example of what I am trying to achive. Col_A being the group, and
Col_B is where I am trying to number each record within a group.

Col A - Col B
A 1
A 2
A 3
B 1
B 2
C 1
D 1
D 2

 

[Ken Snell \(MVP\)]

You don't give us all the data fields, so here is a generic representation
of what the query would look like:

SELECT Table1.ColA, (SELECT Count(*) FROM
Table1 AS T WHERE T.ColA = Table1.ColA AND
T.PrimaryKeyFieldName <= Table1.PrimaryKeyFieldName)
AS ColB;

 

[Jeremy Phillips]

Thankyou, you have achived the imposible, everyone I have spoken to said this
was not posibble. I have managed to impliment this in my database, with
great success.

Many Thanks

 

[Ken Snell \(MVP\)]

I should point out that I inadvertently left out one item that will make
this more "generic" and usable (sorry 'bout that):

SELECT Table1.ColA, (SELECT Count(*) FROM
Table1 AS T WHERE T.ColA = Table1.ColA AND
T.PrimaryKeyFieldName <= Table1.PrimaryKeyFieldName
ORDER BY T.PrimaryKeyFieldName) AS ColB;

 

[aemAndy]

This is exactly the problem I'm trying to solve, but I'm not comprehending
your generic shorthand -

Let's flesh out the fields a little bit -

From Table 1, I have "District", which is also the primary key for that table.
Within each district, I want to collect the names of the salespeople, which
is kept in Table 2, the field name is "SalesName".
I want to generate a column numbering each salesperson within the district -

ColumnA ColumnB ColumnC
01Bedrock 1 Fred
01Bedrock 2 Barney
01Bedrock 3 Pebbles
02Island 1 Skipper
02Island 2 Ginger

So... would your generic script translate to (already know it isn't valid,
what am I missing?):

SELECT tblDistricts.DistrictName, SELECT tblDistricts.DistrictName, (SELECT
Count(*) FROM
tblDistricts AS Table WHERE tblDistricts.DistrictName =
tblDistricts.DistrictName AND
tblDistricts.DistrictName <= tblDistricts.DistrictName
ORDER BY tblDistricts.DistrictName) AS Num tblSalespeople.Name
FROM tblDistricts LEFT JOIN tblSalespeople ON tblDistricts.DistrictName =
tblSalespeople.DistrictName;

I think I'm just misinterpreting some of your shorthand....

 

[aemAndy]

Please ignore the obvious but uninteded repetition in selecting the
DistrictName field two times instead of one.