numbering (like autonumber) records in a table

[lawitt]

I have a MakeTable query whose resulting table i'm using in a crosstab query. The fields in the crosstab are constantly changing (weekly), so my ultimate goal has been to somehow create a dynamic report that takes into consideration the ever changing fields. that's been VERY tricky.

I've managed to get the MakeTable query to only display my 'top 10' fields and i've created a 'static' report that references a calculated field (Number) in that table. In effect, if i can automate the creation of an autonumber field within this 'made' table and call that field 'Number' (where the values would be 1 to 10), then my report will always look to that field for the report.

where i'm stuck is in automatically (either by macro, procedure or funtion) creating that Number field. I found this fellow's function (http://www.lebans.com/rownumber.htm), but have not been able to figure out how to apply it properly.

seems it would be MOST nice if i could create an OpenTable macro that simply added an autonumber field called Number... but that may just be impossible.

any help is greatly appreciated... i'm running XP and Access 2003 (SP 3)

-Lynda

[NeoPa]

Lynda,

I think it may be possible to add the field you require.

Check through How to ADD a column at FIRST in a table for a discussion on adding fields and moving their positions in the table.

If this seems to be what you want let us know and we can help find a solution I expect.