Add AutoNumber Column to Query

[badgerman]

I'm just looking for a way to add an additional column to a select query that will just be autonumbered down all the way to the bottom of the data. How can I accomplish this?

[June7]

What is purpose of this in query? Not easy to do and the resulting calculated field can't really be referenced in other queries, forms, or reports. Review:

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

http://allenbrowne.com/ranking.html

Textbox on report has a RunningSum property that can be used to generate a sequential number.

[badgerman]

The purpose of this query is to generate a list of entries from a reference table using certain criteria, and then numbering the results sequentially. These numbers would also have to be referenced in other queries. So, sounds like I'm out of luck unless anyone has any ideas?

[June7]

I could be wrong about using the calculated sequence in other queries. Review the Limitations notes in Allen Browne's article.

[orange]

I think we need to know more about your intended application. An autonumber is basically a means for the system to uniquely identify records in a table. If an "autonumber" has special meaning to you (human), then autonumber is probably the wrong choice.

Why doesn't the reference table have a unique identifier you could use?
You may get some ideas here

Good luck.

[badgerman]

Here's my situation: I have a reference table that shows firms, addresses, and individuals. Each individual is tied to a specific address and firm. I need to make 3 tables: one that lists unique firm names and then gives them a number 1-whatever, one that lists unique addresses and gives each address a number 1-whatever, and one that lists unique individuals and gives them a number 1-whatever. This is why I need an autonumber in the query (i have the unique identification part of the query all figured out fine).

Then on top of that, the address table will need to also reference which firm goes with each address and display the firm # that was assigned to that firm in the firm table. Following this, the individual table will need to reference which firm that individual is with and the address where they are, and the firm # and address # that were given to each in the firm and address tables.

[June7]

You want to split 1 table into 3 normalized related tables? Link firm and addresses then link firm and individuals? The individual's address is retrieved through the firm link?

1. Create a table of unique firm names. The query to retrieve distinct names could be:
SELECT DISTINCT firm FROM tablename;
Use that query as the source for a MAKE TABLE or copy/paste records to new table. Let an autonumber type field generate a unique identifier.

2. Create a new field in the original table for FirmID. Run an UPDATE query that populates this field with the unique ID from the new Firms table, link on the firm name fields. Then can delete the firm name field.

3. do similar for linking firms and addresses

[badgerman]

excellent, thank you

[June7]

More info:

After creating and populating the Firm foreign key field in the original table, create the table of unique addresses by:

1. query to select unique FirmID and address:

SELECT FirmID, <address fields here> FROM tablename GROUP BY FirmID, <address fields here>;

2. use that query as the source for another MAKE TABLE action

3. delete the address fields from original table