Primary Key and Custom Autonumber

[mond007]

Hi All, Apologies if this has been covered as I have looked and read quite a few threads.

I am trying to create the following table.

Request Reference Autonumber i.e. OVR1001, OVR1002, OVR1003 (Unique) etc.
Requester (text)
Dept Code (e.g. PT-1000, PT-2000 … to … PT-9000) (Read only).
Created By (System Username)
Programme Name
Budget Category
Requested Budget Amount
..
..
x 20 Fields (with lots of drop down values from other mini tables e.g. Budget Category).

I plan to have a Login Form that will verify from a Users table
(i.e. Username, Password, Dept Code, Admin Rights Level e.g. Kuldip Mond, PT-2000, Full RW)

I also plan for the login script populate a 1 row table : Current_Active_User table (Multi User db) i.e. Username, Dept Code, Admin Rights Level

Firstly, despite my efforts and extensive reading on ‘custom autonumber’ I have been unable to Autogenerate a primary comprising of the following : OVRxxxx where xxxx is the Dept Level e.g. If I was logged in as PT-2000 then the Autonumber would be OVR2001, OVR2002, etc… likewise a login of PT-9000 would generate number of OVR9001 etc. and start numbering from the last number used in that block see below.

I have tried creating a autonunmber ID field & Request Reference field but I cannot have two autonumber fields as it complains of “Resultant Table not allowed to have more than one autonumber field” which I can understand.
(Suffice to say that there is no point having a ID primary field when I will have a unique OVRxxxx number).

My goal is to end up with 1 table with Request References as follows :

OVR2001, kmond, PT-2000, … £5,000
OVR2002, kmond, PT-2000, … £8,000
OVR2003, kmond, PT-2000, … £10,000

OVR8001, dsmith, PT-8000, … £9,000
OVR8002, dsmith, PT-8000, … £1,000
OVR8003, dsmith, PT-8000, … £70,000

OVR9001, tchambers, PT-9000, … £589,000
OVR9002, tchambers, PT-9000, … £19,000
OVR9003, tchambers, PT-9000, … £67,000

Hope this makes sense because at some point I will be creating a Dashboard of Budget Requirements by Dept Code

Would appreciate any help.

Thanks in advance for your help. Kuldip.
ps I am proficient VBA programmer in Excel and in general.

 

[Pat Hartman]

Using this generated number as the PK is not advisable. Use an autonumber for that. Think about why you need an ID field with meaning at all. Can't you just store the DeptCode so you can identify the records for grouping that way? Having to parse a field each time you do a query is just a pain and completely unnecessary if you define the table correctly. Does it really matter what the number part of the code is? Is it essential that the number be assigned with incrementing values within each group? An autonumber will give you sequence of entry for each item for each department. It just won't be 1,2,3 but it might be 11, 75, 82. The numbers are still in order so you know which one came first and the dept code tells you what department it belongs to.

 

[mond007]

HI, Appolgies for the late reply but I had posted this subject in another forums and managed to get quite far in the Dev.

utteraccess.com and search for "Primary-Key-Custom Autonumber"
or search for post for user : mond007
(not allowed to insert a link at th emoment).

Thanks I will however need help on this further as I still don't get a few things.

Thanks in advance. Kuldip