Autonumber problem

[Liverlass3981]

I am working on a database which holds details of clinical studies. The main
table holds the main data and is the ‘one’ of the relationship with other
tables. This table, called ‘Study details’ has a primary key/field called
‘StudyRefNo’. So when I add a new record, I give it a number, normally the
next number after the previous record, so I have to look at what the last
record was, e.g if it was 6, I would type in 7 for the new record.

The primary key, StudyRefNo from the table Study details is linked to the
primary key fields in ‘many’ relationship tables. For example, I have a
table, ‘Project funding grants’ to hold the funding details of the studies
and the records here are also identified as ‘StudyRefNo’, with the same
format too as the primary key in Study details table. One study can have many
funding grant records.

My problem is that having already put data into the tables, I would have
preferred to have had the StudyRefNo in the Study details table as an
autonumber – to save me manually typing in a number and making sure the
number came after the previous record number (1, 2, 3, 4……). However I do not
know how to get round this problem because the primary keys in the ‘many’
tables have to be the same format (and name) as the primary field in my Study
details (‘One’) table, in order to identify each record. The StudyRefNo
fields in the many tables cannot be autonumbers also because they must be
already entered into the primary table first. So how can I make the
StudyRefNo an autonumber without changing the StudyRefNo’s in the ‘many’
tables?

I don’t want to lose the data already entered if I make changes, but before
I do attempt to do anything, I would be very grateful if someone could give
me some easy to follow, step by step instructions as to how to go about the
above. Hope I’ve made sense! Thanks. Liz.

 

[mnature]

Having the primary key set for autonumber will not give you a guaranteed list
of consecutive numbers, it will only guarantee that your numbers are unique.
There are numerous ways that autonumber will convince itself to "skip"
numbers.

 

[Liverlass3981]

Hi,

Thanks for your quick response. However, I'm still stuck with my problem Can
you or anyone make some suggestions as to how I can solve it. I still have a
lot to learn about Access design!

 

[Lynn Trapp]

Liz,
It sounds to me like you are entering this data directly into your tables.
Is this correct? If it is you should change your process and create a
form/subform that will be linked on your StudyRefNo.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Liverlass3981]

Hi Lynn,
I've actually created forms for each of my tables and enter the data in the
forms. I have a start-up form where you click on a command button, 'Addnew
record to Study details' and here, I put in the data, but I have to manually
put in the StudyRefNo. I'd like a new unique number to come up automatically
for each new record (these do not have to be consecutive numbers). But how do
I do this whilst maintaining my StudyRefNo in the 'many' tables/forms (since
any number here must be matching in the primary table, Study details.
Hope you can help,
Liz.

 

[Lynn Trapp]

Liz,
If you have the "main form" (the form for the "one" side of the
relationship) joined to the "sub form", using the Linked Master and Child
fields, then you shouldn't have to do anything. Can you post the structure
of your two tables?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Bob Miller]

Create an identical table to the one you now have except set the
StudyRefNo as autonumber. You can copy the original tables structure
only to a new one. Create an append query of your current table that
will add the data to the new table and run it. Your current numbers
will be the same as the original table and will begin numbering new
records at the largest StudyRefNo +1. Don't forget to change the name
of the new table to the original table's name (after deleting or
renaming the original one - best to rename and not delete just in
case.)

 

[Pat Hartman\(MVP\)]

As long as the ids in the many-side table are unique, you can turn them into
autonumbers by following the following instructions:
1. Remove any relationships.
2. Turn off Name AutoCorrect
3. Rename the many-side table
4. Copy and past the structure of the many-side table to the original name.
5. Edit the design of the empty table and change the primary key from number
to autonumber.
6. Create an append query that selects all the data from the renamed
many-side table and appends it to the new many-side table.
7. Re-establish relationships. Do not forget to enforce referential
integrity and specify cascade delete.

The column names of foreign keys are given the same name as the primary key
to which they refer for the sake of human eyes. Access doesn't care what
the column names are. It only cares about their data type. So, since most
primary keys are autonumbers, most foreign keys are defined as long
integers.

The ONLY way to supply a value for an autonumber field is with an append
query. If Access didn't support this, there would never be a way to convert
legacy applications to Access.