Creating a field involving autonumber

[graveyard]

hi people, i have problem with creating a field that reads as

1.01
2.01
3.01
4.01
.........
x.01

where x is running autonumber from ascending order

i need it to be in a query and I've tried to put in autonumber in the query itself which works fine but it splits up the number. for e.g.

1.01 Record1 500
2.01 Record1 100
3.01 Record2 1000

and i really need to show it as

1.01 Record1 600
2.01 Record2 1000

i have also tried creating a separate table with ID (Autonumber) and linked with the query but the outcome has duplicated records despite me having linked that to the primary key

Can anyone advise what is the best way to create the field? thanks!

Can anyone

 

[stopher]

I assume Record1, Record2 is a field?

So you can generate your numbering field like this:

myAuto: Replace([RecordID],"Record","") & ".01"

Then just run an aggregate query to sum by this expression.

hth
Chris

 

[graveyard]

I assume Record1, Record2 is a field?

So you can generate your numbering field like this:

myAuto: Replace([RecordID],"Record","") & ".01"

Then just run an aggregate query to sum by this expression.

hth
Chris

Hi chris, sorry i didnt make it clear from the start. Record1 and Record2 are values within the same field

hmm i didnt quite get that. Do i still create a separate table to provide the ID field? I was doing myAuto: [ID]&".01" previously with ID field sorted ascending but that split up the numbers (hence there are multiple numbers for Record1 in my previous example)

I dont understand the field "RecordID" . could you pls explain. thanks