Adding Autonumber with MakeTable query

[jamsta]

Hello all,

I have a MakeTable query, which effectively just filters data from an existing table, but I want to add an autonumber field column to it. Looking around the internet it seems this isn't as straight forward as I'd hoped!

Seems like it should be simple, as I'm sure it's a common thing to want to do - any ideas?

thanks,
jamsta

 

[wiklendt]

why not make an empty table with the fields you want, then make your query an append query instead of a make table query?

 

[jamsta]

Hi wiklendt,

I could do an append query but I still don't know how to populate the autonumber field.

eg I have a table that's got one column, fld.
I want my query to produce a new table, with two columns: an autonumber ID and fld.

 

[Fuga]

If you do like wiklendt suggests, your table will automatically fill the autonumber field when your records are appended.

Is there a reason why you want to use a make table query?

So, create a table with fld and autonumber. In design view, set the field property to autonumber and assign a pk if you want. Then create the append query to fill in data from your existing table.

Fuga.

 

[jamsta]

Thanks both...

Think I was missing the point a little!

 

[greets04]

Hi all, I tried using the append method, but there is a problem with my situation. I need to clear the table and append new data to it each day. It is going to be exported as a new file every day. The problem is that the table keeps track of the increment value. So if I have 100 records one day, the next day, the autonumber starts at 101 even though I've deleted all of the records first. I would still like to find a solution for using a make-table query that creates its own autonumber field. I'm hoping that at least one of you might have tinkered with it just to see if it could be done.
Thanks,
g