Access numbering help needed!

[MattW]

All,

I am quite new to Access but have used Excel for a number of years
now. Most of the work I've done in Access has been trial-and-error
type of stuff. I've been given a project which requires some Access
work and I've hit a road block on part of my project. Here it goes...

I have a table in Access that is linked through ODBC. I pull the data
from that table to create quite a few different reports - which is
going well. The only thing is I need that table to have a sequential
number for each record. The program that creates the ODBC table can
not be modified to do this for me, so I need to find a way to do it
through Access. Currently, there are 36,000+ records in the database
and more being added daily.

Ideally, I need to assign a sequential number to each record. There
is a column in the table that is currently not being used for anything
that I would like to use for the sequential number.

Initially, I will assign the sequential number to each of the 36,000+
records in the table. The current 36,000+ records will always remain
- they won't change or be deleted. Each week, when I pull the report,
I would need to add continuing sequential numbers to the table, since
new records will have been added the previous week. This would
obviously require Access to search out the last number used each week
and continue adding to it.

It's really not critical that the numbers are sequential - as long as
they are unique for each record. If it's easier to assign the "new"
records a sequential number based on the date (i.e. 082407001,
082407002, etc.) I run the report each week, I'm o.k. with that too.

I'm not too familiar with how the whole ODBC-thing works, so if my
terminology is off on how that links up to the table, etc. I apologize
in advance. I do know that when I go into the Access table and make a
change, it does populate correctly back into the program that is
creating that table through ODBC. I've tested that part of it.

I appreciate any help you can provide me with this challenge!

Matt

 

[Jeff Boyce]

Already responded to in another newsgroup.

Posting to more that one 'group is rarely necessary, makes you work harder
to find an answer, and causes the volunteers who help here duplicate each
other's efforts.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Is there any primary key or any set of fields that can be used as the
primary key? If not the only solution I can think of would need to use a
vba function to update each record. If so, then you can use an SQL query to
assign the numbers.

UNTESTED Code - backup your data before using.

Sub AssignNumbers()
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim LngStart as LONG

Set dbAny = CurrentDB()
Set rstAny = dbAny.OpenRecordset ("SELECT TheNumberField FROM TheTable WHERE
TheNumberField is NULL")

With rstany
lngStart = nz(DMax("TheNumberField","TheTable"),0)
Do While not .EOF
LngStart = LngStart +1
.Edt
.Fields("TheNumberField") = lngStart
.Update
.MoveNext
Wend
End with

End Sub

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..