reset autonumber back to 0

[Guest]

i'm using the autonumber type in my key field in 14 related tables. the
reason is so that all the tables have the same key inserted when the user
opens each table (form actually). i need to be able to set the staring
number back to 0 (or at least the same number) for all tables. unless there
is an easier way of inserting a unique identifier into all the tables at once.
thanks

 

[Rick Brandt]

i'm using the autonumber type in my key field in 14 related tables.
the reason is so that all the tables have the same key inserted when
the user opens each table (form actually). i need to be able to set
the staring number back to 0 (or at least the same number) for all
tables. unless there is an easier way of inserting a unique
identifier into all the tables at once. thanks

Delete all records then compact the file. What you're doing though will not
keep those table's AutoNumbers synchronized though. AutoNumbers are not that
well-behaved.

 

[John Vinson]

i'm using the autonumber type in my key field in 14 related tables. the
reason is so that all the tables have the same key inserted when the user
opens each table (form actually). i need to be able to set the staring
number back to 0 (or at least the same number) for all tables. unless there
is an easier way of inserting a unique identifier into all the tables at once.
thanks

If you're using the same primary key in 14 tables then a) you
certainly should NOT use autonumber for any of them, and b) your
database structure is almost certainly WRONG. If you're making users
open up fourteen forms in succession, you'll be facing a violent
rebellion soon!

STOP. You seem to be way down a user-hostile track! Could you explain
the business requirement here? What are these tables, what are the
users entering, and what are you trying to accomplish?

John W. Vinson[MVP]

 

[Joseph Meehan]

i'm using the autonumber type in my key field in 14 related tables.
the reason is so that all the tables have the same key inserted when
the user opens each table (form actually). i need to be able to set
the staring number back to 0 (or at least the same number) for all
tables. unless there is an easier way of inserting a unique
identifier into all the tables at once. thanks

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

I got to wonder about your database design. 14 tables and your question
raises questions. Maybe you should rethink the table and relationship
design before going on. That is the most basic part of a database. If you
don't have that right, everything else gets harder or impossible. Note: I
am not saying what you have is wrong, but it does appear very unusual.

 

[Guest]

thank you for your interest. i know it's going to sound crazy to you guys,
but opening up 14 forms will make the user's lives much simpler. the
application is for the nursing home industry. when they get a new resident
they must perform a plethora of assessments on them (up to about 350 "things"
may be checked). currently, the majority of them are doing this on paper
forms and the process can take up to 4 hours per admission. these
assessments consist of checking things like their mental health (depression,
anxiety, etc.), physical health (missing teeth, wearing glasses, etc.),
property (how many sweatshirts, how many slippers, any valuables, etc.). i
started with a table for resident info such as name, SS#, race, etc. this
table has key field of a unique patient ID# (data is entered once only). 1
other table (for medications) has the patientID# field and 5 other fields for
the multiple meds they may be taking (data may be updated at any time)(many
to one relationship). the other 12 tables consist of PatientID# field (key)
and whatever fields needed to check that category (one to one relationship).
what i'm trying to do is after the resident is registered (data entered into
table 1) i wanted the users to be able to go through the rest of the
assessment procedure without having to enter the patientID# on each of the
successive 12 forms. (it's here that i think i'm trying to make the user's
life simple and mine difficult). to add to the problem, there is another
application related to this that i want to be an add-on. it's similar in
scope with number of tables and fields except that there may be multiple
assessments for each patient.
between the 2 apps there are approximately 730 fields split into quite a few
tables with no repeating data except the patientID#.
clear as mud?
i'm thankful there are 3 heavy hitters like yourselves interested.

 

[Rick Brandt]

thank you for your interest. i know it's going to sound crazy to you
guys, but opening up 14 forms will make the user's lives much
simpler. the application is for the nursing home industry. when
they get a new resident they must perform a plethora of assessments
on them (up to about 350 "things" may be checked). [snip]

Instead of 350 *fields* spread over 14 tables you could have 350 *rows* per
patient with each row storing a "thing".

This is a bit more work to set up the interface for, but the REALLY big
advantage is that the same design can accomodate 1 "thing" or 1000 "things"
without having to change the basic structure of the database. With your current
setup if managment suddenly diecides there are more things to keep track of you
have a lot of changes you have to make.

 

[Rick Brandt]

would that mean a seperate table for each resident with however many
rows of "things"?

Nope. Just one table.

unfortunately, won't work. most of these fields
have to be in a pre-determined format. some fields are required to
be checkboxes, some have to be 15 characters long and all
capitalized, some have to be numeric and 8 characters long, etc.
that's why i have so many drill-downs. the lookup will have text
describing a condition but the data stored has to be a number.

Actually with links to related lookup tables ALL data can be stored as
numbers which is why you can accomodate different "types" of data in a
common field. A CheckBox can be stored in a number field, text values are
displayed as text but stored as numbers, and obviously numbers can be stored
as numbers. You just use an additional field to determine what the
"question" being answered is.

example: 0=does not wear glasses, 1=wears glasses for reading,
2=wears glasses all the time. when they choose the condition, the
corresponding number goes into the field. i have no choice about the
formatting, they're based on federal guidelines. i'm thinking i may
have to provide the users with a patient lookup on each form.
your thoughts are greatly appreciated.

For a good example of the concept see Duane's "At Your Survey" database
at...

http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'