How to add auto-number ID using SQL

[accessmatt]

Hello,

Using SQL, can someone show me how do I add a field to this query which is an ID that auto-numbers each row?

SELECT Table1.*FROM Table2;

Thanks,

[NTC]

numbering the rows/records of a query result is complicated code - essentially trying to do something which inherently is not available. In the Report object one has the numbering method available. But if you bing/google the topic of numbering query records you will find a variety of examples.

[Micron]

If this is a one-off thing, convert the query to a make table query then add the primary key (ID) afterwards. If not, then I think there is an issue with db normalization and design. Adding an auto id isn't something you normally do with queries - you design the table that way. Perhaps you need to elaborate more on what it is you're doing.

Is that a typo (how do you select all of Table1 from Table2)?

[accessmatt]

thank you both for weighing in.

[accessmatt]

How can I use sql or Access to create an autonumber field when I run an INTO (make table) query?

[orange]

When you do a SELECT INTO the design of the receiving table is already known.
If you want to create a Table with an autonumber field, you use a Data definition query such as

CREATE TABLE testAncestor (
	ancID counter
	,FirstName TEXT (20) NOT NULL
	,LastName TEXT (20) NOT NULL
	,CONSTRAINT PK PRIMARY KEY (ancID)
	);

This creates a new table called testAncestor with fields

ancId which will be autonumber (COUNTER is the datatype for autonumber)
FirstName text field 20 char
LastName text field 20 char

NOT NULL means a value must be supplied for each record.
The CONSTRAINT clause makes ancID the Primary Key for this table.

[accessmatt]

Thanks @orange but is there a way I can use Create Table in conjunction with fields and data in an existing query. The example you gave creates the definition but I want to pull data into there, with a new field called ID that auto-increments.

[orange]

Yes, but---

1. The table must exist before you can add data to it.
2. You can use and Append query (Insert with SELECT..)

Example:

You would create the table as mentioned previously, then this sort of thing

INSERT INTO Customers (CustomerName, Country)
    SELECT SupplierName, Country FROM Suppliers;

[accessmatt]

@orange, thanks that worked perfect.

[orange]

Happy to help. For SQL syntax and examples see w3schools