Counter instead of autonumber field

[hnunez4399]

How can I create a query / or some type of counter that will populate a field based on its order imported. Essentially, it would be like an autonumber, but I want to be able to reset the number to 1 when needed, and within access I only know of doing this by compacting the database.

Any help would be appreciated.
Thx,

 

[lespaul]

is there a date time stamp that indicates the imported order?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[hnunez4399]

No essentially I import an excel file that contains the following fields

sku
SumOfUnits
Cat
StyleDescription

This gets appened to a table that contains those fields plus an anutonumber field called "recnum"

Currently it is an autonumber field and if the database has been compacted the first record will be 1 but if not the number will not be 1. I want this field to always reset to 1 all the time / or when the file is imported, that the first record being imported is always 1 and increments by one.....

Let me know if this makes sense.

 

[PHV]

Before the import (VBA code):
DoCmd.RunSQL "ALTER TABLE yourTable ALTER COLUMN recnum COUNTER(1,1)"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hnunez4399]

I have never written VBA code.
Where would I put this. Sorry still learning.

 

[PHV]

Before the import (SQL code):
ALTER TABLE yourTable ALTER COLUMN recnum COUNTER(1,1);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hnunez4399]

I can't seem to get this to work.

 

[lespaul]

Have you followed the following steps?

create a new query, switch to SQL view, paste the SQL code above and run the query.

If not, what did you try that didn't work?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[hnunez4399]

I got it to work.
Thanks for your help I really appreciate it.

Are there any books you recommend that teach you how to program in access?