Extra Autonumber Field On Table For Each Unique ID Number

[stildawn]

Hi All

Not sure how to describe this but here goes.

I have a table as below:

PrimaryKeyID (Autonumber)

FRTRatesID (linked to FRTRates table)

Name

Value

OrderBy

PrimaryKeyID - Standard issue autonumber, no duplicates unique for each record
FRTRatesID - A unique number from FRTRates table, however on this table can be multiple records with the same number (so they all link to 1 record on FRTRates table)


Name - Simply a text field for the value name
Value - Currency value
OrderBy - This is the field I need help with, I want it to autonumber starting from 1 for each unique FRTRatesID number..


So an example of what I want a set of data to look like:

PrimaryKeyID (Autonumber)	FRTRatesID (linked to FRTRates table)	Name	Value	OrderBy
1	1	Terminal	$100	1
2	1	Docs	$50	2
3	1	Admin	$30	3
4	2	Terminal	$200	1
5	2	Docs	$100	2
6	2	Admin	$80	3

So you can see for FRTRateID 1 there is 1, 2, 3 in the OrderBy, same for FRTRateID2, I want this OrderBy to autopopulate.

This is so a user later on in the database can change them to order however they like, so if they wanted Docs to be first they could change it to a 1 etc.

Is this possible?

Cheers

[isladogs]

You can only have one autonumber field in a table so that isn't possible.
You need to create a rank order by group where the FRTRatesID field is the group value.
To do this you need a subquery.
I did something similar yesterday in another thread at a different forum. See post #19 in this thread https://www.access-programmers.co.uk.../#post-1780147

[ssanfu]

Be aware that "Name", "Value" & "OrderBy" are reserved words in Access and shouldn't be used for object names.
Plus, "Name" and "Value" are not very descriptive - Name of what? Value of what?

See Problem names and reserved words in Access

[stildawn]

Be aware that "Name", "Value" & "OrderBy" are reserved words in Access and shouldn't be used for object names.
Plus, "Name" and "Value" are not very descriptive - Name of what? Value of what?

See Problem names and reserved words in Access

Yeah I know, sorry they are not actually called that, its just for simplicities sake on the internet

[stildawn]

You can only have one autonumber field in a table so that isn't possible.
You need to create a rank order by group where the FRTRatesID field is the group value.
To do this you need a subquery.
I did something similar yesterday in another thread at a different forum. See post #19 in this thread https://www.access-programmers.co.uk.../#post-1780147

Thanks will take a look

[Micron]

Why don't you just order by the PK of FRTRates table? What difference would it make if the order is 1,2,3 for FRTRates(1) or if 4,5,6 for FRTRates(2)? Or does the rates table not have an autonumber (or similar) ID field?