query that brings a autonumber field

[Ben]

Hi all,

I would like to write a query, where I can create an auto number field.
That is if my query brings back 100 or 1M records, this field would contain
that value, ie, in the first record, the autonumber field would be 1, in the
second record, the autonumber field would be 2...etc all the way to the last
record, if it is 100 then the field value for the last record on this
autonumber would be 100.

Is there a way to do that? Thanks for sharing your thoughts.

Ben

--

 

[Jeff Boyce]

Ben

Autonumbers (Access Autonumber data type) are just that, automatic. Access
decides what they are, not you.

And if you ran this twice, with two different sets of records, you wouldn't
want to be re-using "1", "2", ... would you?

If you use an append query, and append to a table that has an autonumber
field, Access will take care of automatically filling in that value.

Now, why?! Why do you want to determine the values? I assume you have a
business need you think doing this will help you solve.

If you'll provide a bit more description of that underlying need, folks here
may be able to offer alternate ways to get it done.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ben]

Hi Jeff,

Basically, I would like to create a query in my field that reflects the
position count of the recordset if you will. It is more like an index rather
than autonumber.

So, if my query returns 100 records, this field, I will call it, myIndex,
would be something like this:
myIndex Field1 Field2
1 somevalue somevalue
2 ... ...
3 ... ...
....

100 somevalue somevalue



I need to do this, because another group requests if I can provide that. I
know what I can do in a long way is to CREATE TABLE with an autonumber field,
then append the records into this table. And the next time I run it again, I
will drop the table again and re-run the CREATE TABLE query and append again.
This I think will guarantee that I have an autonumber/index field.

But I thought may be it can be straight from my query. That's my goal.

Thanks,

Ben

 

[Ben]

Jeff,

I was hoping that I can create a query with a field that numbers each
record. Basically like this, if this was the output of my query with 100
records:

MyRecNum Field1 Field2
1 somevalue somevalue
2 ... ...
3 ... ...
....
100 ... ...

Another group in office needs this data to help them simplify that work and
they ask me to provid this field. I would rather have a query to do this, if
it's possible.

Thanks,

Ben

 

[KARL DEWEY]

Try something like this --
SELECT [Change Requests].[Date open], [Change Requests].[Date close],
(SELECT Count(*) FROM [Change Requests] As AA WHERE AA.[Primary_Key] <=
[Change Requests].[Primary_Key]) AS RowNum
FROM [Change Requests];

 

[Ben]

Jeff,

Thansk for sharing your suggestiion, it was very useful.

Ben

 

[Ben]

Karl,

Thanks so much for taking the time to share your thoughts and suggestion.
It was very helpful. Thank you again.

Ben

--

Try something like this --
SELECT [Change Requests].[Date open], [Change Requests].[Date close],
(SELECT Count(*) FROM [Change Requests] As AA WHERE AA.[Primary_Key] <=
[Change Requests].[Primary_Key]) AS RowNum
FROM [Change Requests];

--
KARL DEWEY
Build a little - Test a little

Jeff,

I was hoping that I can create a query with a field that numbers each
record. Basically like this, if this was the output of my query with 100
records:

MyRecNum Field1 Field2
1 somevalue somevalue
2 ... ...
3 ... ...
...
100 ... ...

Another group in office needs this data to help them simplify that work and
they ask me to provid this field. I would rather have a query to do this, if
it's possible.

Thanks,

Ben