How to add AutoNumber field into a query

[pafkam]

I have read 15 articles but still can't figure. I have a simple query, non of the fields has unique values, and I am trying to add a field with AUtoNumber. Any help? THank you! Pavel

 

[Joe4]

There are instructions on how to do that here:
http://www.groupacg.com/AqryTip.htm#AUTONUM

 

[pafkam]

This link can't be used because I don't have unique field in the query.

 

[Joe4]

Note a key phrase in that link:

Critical to creating the auto number sub-query is that the table or query you are using as a source must contain a unique index and this field must be included in your auto number query.

If you do not have one, you may be out of luck (I don't know if there is any other way to do it without this).
Why can't you add one?

Or maybe you can explain exactly what you are hoping to accomplish by doing this. What is your end goal?
There may be a different way to get there.

 

[pafkam]

Thank you. I have this (very simplified):
field1:fraction of % , field2: Total $ .
I have two rows:
1st row reads: 55% (field1), 100$ (field2)
2nd row reads: 45% (field1), 100$ (field2)
When I run field1 * field 2 I get an allocation of 55$ and 45$ in each row.
I want to add an additional autonumber field so I would assign 1 to the first row and 2 to the second row in a separate column (this will give me a unique row identifier)

 

[Joe4]

Why not just an Autonumber field to your table?

Then you will have a unique identifier which will then allow you to use that code to create query autonumber (I assume that you want it to begin with 1 each time).

 

[pafkam]

I unfortunately can not add any fields to that table. THat is why I need to use query to sort of re-create the table but with this additional unique autonumber field.

 

[Joe4]

I am afraid you may be caught in a classic "catch-22", you need the query to return a unique counter, but creating a unique counter appears to require a unique field in your query.

There is a way around that. You can create a Make Table Query to create a temporary table which contains all of the fields in your original table along with an AutoNumber field. You can then run this query you are trying to create off of this new temporary table (and make use of your new autonumber).

 

[jain_dhirendra]

I have read 15 articles but still can't figure. I have a simple query, non of the fields has unique values, and I am trying to add a field with AUtoNumber. Any help? THank you! Pavel

Hi,

Pls use create table statement as below :

create table my_table (my_column counter not null)

The above statement creates tables named my_table with 1 column my_column which is of type Autonumber (counter)

Thanks
Dhirendra