Design Issue

[accessdummy]

Hi, can someone help me out with this?

I have 3 tables, namely assignment table, employee table and job table.

assignment table contains fields: assignmentID, employeeID, NumHrsAssign, Remarks

employee table contains fields: employeeID, firstName, lastName, jobID

job table contains fields: jobID, jobname, wageperhr

I've intended to use autonumber for the IDs for all the tables, mainly because

a. huge data involved
b. i wan to hide the id away from the end users to refrain from bad data entry.

however, it's kindda confusing to differentiate btw the various IDs and I'm thinking of customising data type by concatenating text and autonumber.

for example:

for assignmentID, it will be "YYYYMMAutoID" so the first ID should show 2005100001, second one follow 200510002 etc etc.

for EmployeeID, it will be "lastnameautoid" so the first ID should show Joe001, second ID to be Peter002, third ID to be Joe003

for jobID, Job+Autoid..

Definitely there will be an input form for all tables. I'm approaching in a way such that I can manipulate textbox .. but i got stuck because i have no idea how to increment the last data in the table.

e.g: the last assignment ID is 200510(006).. my input form should reflect 200510(007) n thus this data will be stored in the table.

how about recommending other ways of implementing ID? i'm open to suggestions. thanks.

 

[statsman]

Why not just cut out the middleman. Instead of using autonumber use:
Employees would start "EMP001"
Jobs would start "JOB001"
Assignments would start "ASN001"

Just a random thought

 

[accessdummy]

good idea.. but how?

 

[accessdummy]

Let's say my last record is EM004.. then can i "autoincrement" a EM ID for my enduser who is inputing data in the FORM? been trying to figure out how to.. i don't want my enduser to key in the ID (instead it should autoincrement). thus the need to merge text and autonumber.. unless of course, there are better solutionssssss.......