Query generated autonumber

[M. Wild]

I am trying to generate an autonumber field in a query. I
successfully did this using a select command but it is
very very slow to generate
Uniq_loc: (Select Count(*) from WellPhysicalData Where
[loc_ID] < [WellPhysicalData_a].[Loc_id])+1

I am of the opinion that I need to develop a VB function
to do this in a timely manner but I am a VB novice.

Any help would be greatly appreciated.
Mike

 

[Rick Brandt]

I am trying to generate an autonumber field in a query. I
successfully did this using a select command but it is
very very slow to generate
Uniq_loc: (Select Count(*) from WellPhysicalData Where
[loc_ID] < [WellPhysicalData_a].[Loc_id])+1

I am of the opinion that I need to develop a VB function
to do this in a timely manner but I am a VB novice.

I would pose a question to you. Why do you need to do this? I see this as a
relatively frequent request in these groups and while solutions are always posted,
upon looking at them I always think that the method seems pretty inefficient;
particularly on larger tables. You're experience supports that notion.

I'm not saying that there isn't a legitimate reason for doing this I just haven't
ever come across this need in many years of working with Access so I'm curious as to
what people are doing this for. Many of the requests want to use the number to
"rank" the records, but this is so easily done in a report (without the inefficiency
issue) and queries aren't supposed to be used for presentation anyway so why not just
do it in a report?

 

[Bruce M. Thompson]

I am trying to generate an autonumber field in a query. I

successfully did this using a select command but it is
very very slow to generate
Uniq_loc: (Select Count(*) from WellPhysicalData Where
[loc_ID] < [WellPhysicalData_a].[Loc_id])+1

I am of the opinion that I need to develop a VB function
to do this in a timely manner but I am a VB novice.

Check out the following at Stephen Lebans' web site:

http://www.lebans.com/rownumber.htm

 

[M. Wild]

I too have seen many posts on this. Unfortunately, I have
yet to find a solution.
I deal with loads of ever-changing environmental data. To
visualize the data, I have to open it in a geographic
information system (GIS). The program, ArcGIS, requires
that tabular data, to be depicted as a point on a map,
must have a unique integer value as the first column.
It reads Access directly but not queries only tables.
Now I can easily add an autonumber field to tables used by
the GIS by first editing the design of the table. But,
since the tables must be frequently regenerated through
make table queries, it would be cumbersome to keep re-
editing the exported tables.

-----Original Message-----

I am trying to generate an autonumber field in a query. I
successfully did this using a select command but it is
very very slow to generate
Uniq_loc: (Select Count(*) from WellPhysicalData Where
[loc_ID] < [WellPhysicalData_a].[Loc_id])+1

I am of the opinion that I need to develop a VB function
to do this in a timely manner but I am a VB novice.

I would pose a question to you. Why do you need to do this? I see this as a
relatively frequent request in these groups and while solutions are always posted,
upon looking at them I always think that the method seems pretty inefficient;
particularly on larger tables. You're experience supports that notion.

I'm not saying that there isn't a legitimate reason for doing this I just haven't
ever come across this need in many years of working with Access so I'm curious as to
what people are doing this for. Many of the requests want to use the number to
"rank" the records, but this is so easily done in a

report (without the inefficiency

issue) and queries aren't supposed to be used for

presentation anyway so why not just

 

[M. Wild]

Thanks Bruce,
That code was in the right direction but only autonumbers
indexed fields. I'll keep trying.

-----Original Message-----

I am trying to generate an autonumber field in a query. I
successfully did this using a select command but it is
very very slow to generate
Uniq_loc: (Select Count(*) from WellPhysicalData Where
[loc_ID] < [WellPhysicalData_a].[Loc_id])+1

I am of the opinion that I need to develop a VB function
to do this in a timely manner but I am a VB novice.

Check out the following at Stephen Lebans' web site:

http://www.lebans.com/rownumber.htm

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.