Autonumbers

  • Thread starter Thread starter Betsy
  • Start date Start date
B

Betsy

Is there any way to generate an autonumber without having
to add any other fields? It seems like autonumbers are
only generated AFTER another field is filled in. For
example, can you just tell it to set the value of a field
to the next number in line?

Thanks, Betsy
 
Betsy said:
Is there any way to generate an autonumber without having
to add any other fields? It seems like autonumbers are
only generated AFTER another field is filled in. For
example, can you just tell it to set the value of a field
to the next number in line?
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Short answer: No.

AutoNumber is really a Record ID number. If there isn't any other data
besides the AutoNumber there is no record. Therefore, no Record ID.

If you just want incrementing numbers in a table you can write a VBA
routine to put in the number sequences, or, use a query to add one
number at a time:

INSERT INTO TableName (SequenceNo)
SELECT MAX(SequenceNo) + 1 FROM TableName

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQIQxo4echKqOuFEgEQLaTACdFZ+Tezkx1C7FQji079gK+ZEKb+kAoLRE
oSA9ho9FEXRBpdXi6fy18di+
=/j/m
-----END PGP SIGNATURE-----
 
Thanks, I didn't think so. Here's my problem.

I have 1 table with 3 different kinds of clients. All have
a unique primary key, AppID and the same basic info, name,
address, etc. BUT, each kind of client also has to have
its own unique ID field so that they can be connected, ie.
parent, to child, to grandchild. But you can't have 2
autonumber fields in same table, so I'm having to create a
separate table, just to get the second autonumber unique
key. I could make it something else, like a truncated part
of the name or something, but I though this way would be
easier. Any ideas?

Betsy
 
The usual solution to this type of problem is to have a
column called parentId that is a foreign key to its own table.
So to find all the children for a particular parent, you
would use SQL like
SELECT C1.appId FROM Clients C1 INNER JOIN Clients C2 ON
C1.parentId = C2.appId
and to find the grandchildren
SELECT C3.appId FROM Clients C1 INNER JOIN (Clients C2
INNER JOIN Clients C3 ON C2.appId = C3.parentId) ON
C1.appId = C2.parentId
and so on

Hope This Helps
Gerald Stanley MCSD
 
Thanks for the help, but I'm afraid I'm still confused.
I'm not sure what you mean by foreign key to its own table?

Also, how would C1.parentID ever = C2.appID, if they're
both unique fields?
 
It sounds like your data is not normalized properly

From what you have said, I think this may help

table1
AppID - primary ke
Nam
Rest of field

table2
AppId1 - composite primary ke
AppId2 - composite primary key (AppId1 and 2
relationship (parent, child, etc

That should get you started
HT
SMK
 
A foreign key is usually a primary key from another table.
In the solution I gave, the parentId column is the primary
key from another row in the same table.
Say you have a client with appId 1234. You want to
establish him as the parent of another client whose appId
ia 9876. So the row for the child will look like
appId 9876
name Joe Bloggs
parentid 1234

Hope This Helps
Gerald Stanley MCSD
 
Back
Top