Autonumbers and Incremental Numbers

[stacies]

Hi All:
New to the forum but hoping I can get some help on a problem I am having with a ne database I am creating.

I am trying to make a database that will store some basic information but most importantly generate part numbers for our project documents as automatically as possible.

I will have a master table which will house the mrxnum (pk)
and then a second table "Part" which will house the partnum (long integer) and mrxid (lookup - to mrxnum) (fk)



So ultimately my part numbers should look like this:
mrxnum = 100
partnum = 100-100
partnum = 100-101

mrxnum = 101
partnum = 101-100
partnum = 101-101

etc...

I was using an autonumber for the MRX number so it is autogenerated and never repeated. But obviously can no do this with the Part Number...but essentially that is what I want. The less the users have to do is the key here.

Can any one help me?

Thanks In Advance!
Stacie

[RayMilhon]

Since the mrxnum is in your parts table I would have the partnum as it's own numeric field. Then your Primary key would be 2 fields mrxnum and partnum

mrxnum partnum
100 100
100 101
100 102
101 100
101 101
101 102

[ssanfu]

<snip>I was using an autonumber for the MRX number so it is autogenerated and never repeated. But obviously can no do this with the Part Number...but essentially that is what I want. The less the users have to do is the key here.

Can any one help me?

Thanks In Advance!
Stacie

Stacie,
If you are using an autonumber type field as a field that has meaning, you are using autonumbers wrong. See this article:
http://www.databasedev.co.uk/microso...utonumber.html

especially the second paragraph.

For both mrxnum & partnum you need to create your own "autonumbers". It's not hard; there are several examples at different sites.
See:
http://www.baldyweb.com/CustomAutonumber.htm

http://www.rogersaccesslibrary.com/forum/topic395.html


And here is a discussion in UtterAccess (with example):

http://www.utteraccess.com/forum/Cus...-t1953743.html