How to add sequence number to groups of records within a table

[Guest]

Hello, I'm looking to find a way to add a kind of "record number" to groups
of records within a table. I'm thinking this would be a tremendous help in
running crosstab queries against the data. Here's what I mean.

Rec_id Company SalesPerson Rec_Num (field to add)
-------- ----------- -------------- --------------------------
290039 ABC Corp Lina D 1
290039 ABC Corp Eric R 2
290039 ABC Corp Tony K 3 (up to 7 or 8 max)

377280 XYZ Inc Ronda R 1
377280 XYZ Inc Jason S 2
and so on...

Anyone know how I could update the "Rec_Num" field with a sequence that
starts again with each set of unique records according to the Rec_id and
Company values?
My goal in the crosstab queries is to get the Company to appear once with
the SalesPerson in multiple columns. The issue is that the data may contain
several hundred different sales people which blows out the 255 limit if I
pivot on the SalesPerson name. If I pivot on Rec_Num I would only get 7 or 8
columns since that's the most sales people on any single company.

thanks

 

[Duane Hookom]

There is a solution in the Crosstab reports at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This
method uses a small amount of code to assign an "alias number/letter" to a
column heading.

 

[John Spencer]

How about doing something using a ranking query and using that as the basis
of your cross tab query. This might be slow..

SELECT Rec_ID
, Company
, SalesPerson
, (SELECT Count(*)
FROM YourTable as YT2
WHERE YT2.Rec_Id = YT.Rec_Id
And YT2.Company = YT.Company
And YT2.SalesPerson < YT.SalesPerson) +1 as Rec_Num
FROM YourTable as YT

If you want to do this in an update query, you will probably be forced to
use DCount with the needed criteria.

BY the way if Company is the same for every occurence of Rec_Id, then you
don't need both in the subquery's Where clause.