Create a Sub Autonumber that re-assigns autonumber to each data groupin a sorted field.

[Junwenren]

I am buidling a database that has two field. The first one is the
vendor name, the second one is the project-timing (e.g. days) for the
project assigned to the vendor. The problem is, each vendor may have
multiple projects, that changes overtime, so the table will look as
follow:

VendorName ProjectTime

ABC 1
ABC 45
ABC 4
XYZ 4
XYZ 3
XYZ 765
XYZ 45
XYZ 45
DKE 32
DKE 23

I want to assign autnomber sequence to each vendor, so that auto-ID
field will look as follow

AutoID VendorName ProjectTime

1 ABC 1
2 ABC 45
3 ABC 4
1 XYZ 4
2 XYZ 3
3 XYZ 765
4 XYZ 45
5 XYZ 45
1 DKE 32
2 DKE 23


My purpose is to crosstab the project time field using the AutoID as
the column field, instead of using the projectTime as the column
field.


Thank You very much

 

[Allen Browne]

As it stands, the task is impossible, because your table provides no way to
determine the order of the records. It needs a primary key.

If you added a primary key autonumber to the table, you could use a subquery
to give you the ranking (your AutoID) number for each vendor. Details in:
Ranking or numbering records
at:
http://allenbrowne.com/ranking.html#query