Adding a field to a table created byt SELECT INTO

[TOPSie]

Thought I had this cracked, needed to created a table on the fly from a query used to populate a report. Easy one-liner:

DoCmd.RunSQL "SELECT qryLoadingSheet1.* INTO [" & wLLTable & "]  FROM qryLoadingSheet1;"

Then THEY wanted to add an Index field (autonumber) and a blank column.

The blank column is easy

DoCmd.RunSQL "SELECT qryLoadingSheet1.*, '' AS QReturn INTO [" & wLLTable & "]  FROM qryLoadingSheet1;"

But how can I add an Index field (increments for each record)?

Tried

DoCmd.RunSQL "SELECT Id, qryLoadingSheet1.*, '' AS QReturn INTO [" & wLLTable & "]  FROM qryLoadingSheet1;"

but of course it prompts for Id and replicates the entered value in every record

[CJ_London]

Use a row number public function. In my phone but would be something like

function rownum() as long
static r as long

if isnull(r) then r=0
rownum=r+1
r=rownum

end function

sql would be

select rownum as ID, …….

as I said, on my phone but you may need pass a parameter,

[Micron]

Then THEY wanted to add an Index field (autonumber)

It's a one-time thing, so can't you just open the table in design and add an autonumber field?

[Gicu]

You can create the table with the make-table like you do now then add the autonumber field using DAO or ADO DDL:
https://stackoverflow.com/questions/...ss-at-run-time
Cheers,

[TOPSie]

Use a row number public function. In my phone but would be something like

function rownum() as long
static r as long

if isnull(r) then r=0
rownum=r+1
r=rownum

end function

sql would be

select rownum as ID, …….

as I said, on my phone but you may need pass a parameter,

Thank you - this looked the most hopeful reply. Unfortunately I have not got it to work. Running with a stop of the rownum function - then yes it is entered, but only once. Having got an answer "1" it proceeds to use it for every row of the table.

Don't want anyone to spend too much time so I have asked THEY if they can do without the Index. The table is never more than 30 records and other fields have unique qualities - if I was doing what THEY are doing I could easily cope with out it

[Welshgasman]

If it is only 30 or so records, then a DCount() would not hurt much?
https://www.google.com/search?q=gene...hrome&ie=UTF-8

[CJ_London]

Having got an answer "1"

As I said, you may need to pass a parameter. Now back at my machine and can confirm that is the case. Example sql - note the criteria which 'resets' the static value

SELECT admEvents.*, RowNumber([EventFromDT]) AS PK
FROM admEvents
WHERE (((RowNumber())=False))

and this is the function

Function RowNumber(Optional r As Variant = -1) As Variant
Static X As Long
Static s


    If r = -1 Then
    
        X = 0


    ElseIf s <> r Then
    
        X = X + 1
        s = r
        
    End If
    
    RowNumber = X
    
End Function

Note that the value you pass as a parameter has to be unique from the previous row- this example uses a date field as in this case there is only ever one event for any given date and time. Read and understand the code, you may want to play around with the elseif line and there is nothing to stop you concatenating fields to create a unique value.