Counter/AutoNumber in query

[Clas]

Hi.

I need a counter/AutoNumber ID in an query.
When I use "make table" query , after the new table is created, I must edit/add a AutoNumber in the new table.

In the "make table" query it should be nice to have an function to create rownumbers. rowid: function()


Thanks !

 

[jal]

One possible solution is to add a counter column after the fact:

ALTER TABLE Customers ADD COLUMN autonum COUNTER

 

[Clas]

Yes, but in
ALTER TABLE Customers ADD COLUMN autonum COUNTER
I must change table name for each table to create.

The application creates and deletes many tables as "temp-tables".
We need to save recordset temporary in tables.

 

[jal]

Yes, but in
ALTER TABLE Customers ADD COLUMN autonum COUNTER
I must change table name for each table to create.

The application creates and deletes many tables as "temp-tables".
We need to save recordset temporary in tables.

I don't see the problem. Run the above query in VBA code each time you create a table.

 

[pono1]

Adding to JAL's comment... Consider creating a procedure to handle this. Roughly...

Private Sub Blah
   ' Call the procedure
   MakeTempCustomerTable "CustomerUniqueName"
End Sub

Private Sub MakeTempCustomerTable(tableName As String)

Dim my_sql As String
my_sql = _
  "CREATE TABLE " & tableName & _
  "(First_Name TEXT(50), " & _
  "Last_Name TEXT(50), " & _
  "Address TEXT(50), " & _
  "City TEXT(50), " & _
  "Country TEXT(25), " & _
  "RecID COUNTER PRIMARY KEY)"

DoCmd.RunSQL my_sql
 
End Sub

 

[jal]

Adding to JAL's comment... Consider creating a procedure to handle this. Roughly...

Private Sub Blah
   ' Call the procedure
   MakeTempCustomerTable "CustomerUniqueName"
End Sub
 
Private Sub MakeTempCustomerTable(tableName As String)
 
Dim my_sql As String
my_sql = _
  "CREATE TABLE " & tableName & _
  "(First_Name TEXT(50), " & _
  "Last_Name TEXT(50), " & _
  "Address TEXT(50), " & _
  "City TEXT(50), " & _
  "Country TEXT(25), " & _
  "RecID COUNTER PRIMARY KEY)"
 
DoCmd.RunSQL my_sql
 
End Sub

Thanks, I like your way better than mine.

 

[pono1]

And I had thought it was your way...just added a little bit more code to help the OP who may also want to look at the SQL Count function -- depending on what he wants to do...

 

[gemma-the-husky]

different question

why do you need a sequential number - thats not what access is about

 

[Clas]

The "temp table" is to create spell.

-"Make Table query" creates new temp-table, order by Individual and date, from other querys.
row 1 Individual A MovingIn 2008-01-01
row 2 Individual A MovingOut 2008-05-05
row 4 Individual B MovingIn 2008-07-02
row 5 Individual B MovingOut 2009-02-02
row 6 Individual A MovingIn 2008-06-28
row 7 Individual A MovingOut 2008-10-10

First row is StartDate, second row EndDate.

Join table temp-table (rownumber) with temp-table (rownumber + 1),
then you have spell with startdate and enddate:
Individual A 2008-01-01 2008-05-05


But the problem is the "Make Table query".
There is no way to create an AutoNumber dynamic ?????
The other field are in the "Make Table query" and changes depending on underlying query.