Multiple Autonumbers

[davidfourie]

In a table I have several different departments.

Depending on the department I would like to have seperate autonumbers.

The tables primary key will be a combined primary key Department and ID
table = people[Department,ID,Name]

So the data would be something like:

Accounts, 1, Sally
Accounts, 2, Ben
Accounts, 3, Dave
HR, 1, Fred
IT, 1, Sean
IT, 2, Brenda
IT, 3, Lyn
HR, 2, Davina
Accounts, 4, Lisa
HR, 3, John

I want the autonumber to be automatic starting for each department

 

[ajetrumpet]

David,

I don't think you want that. Maybe you could set it up like this:

TABLE1 = departments (ID (PK), department)
TABLE2 = employees (ID (PK), employeefirstname, employeelastname)

You don't want all of this data stored in one table. Put a one-to-many relationship between the department table and employees. Your want here would probably not be good later on, as it would get too cumbersome.

 

[SOS]

Depending on the department I would like to have seperate autonumbers.

No, no, no, no, no...

Apparently you are not too clear on what an autonumber really should be used for. It ONLY guarantees you a unique number - nothing more. It is normally used for the primary key of a table and that is managed by the SYSTEM, and users should never see them.

You should have, like ajetrumet has said, tables similar to

tblDepartments
DepartmentID - Autonumber (Primary Key)
DepartmentDescription - Text

tblEmployees
EmployeeID - Autonumber (Primary Key)
LastName - Text
FirstName - Text
EmployeeCompanyNumber - Text
Address - Text
City - Text
State - Text
PostCode - Text
PhoneNumber - Text

tblEmployeeDepartment
EmployeeDepartmentID - Autonumber (Primary Key)
EmployeeID - Long Integer (Foreign Key from tblEmployees)
DepartmentID - Long Integer (Foreign Key from tblDepartments)

 

[davidfourie]

Not sure this works for me.

the employees details are stored on a paper based system.

They are put into files depending on their department (HR, ADMIN, ACCOUNTS) and ordered in chronological order by when they start.

hence the ADMIN 1, ADMIN 2 etc.

if it was on different tables i would need

tblAdmin
Department - "ADMIN"
EmployeeID - Autonumber (Primary Key)
LastName - Text
FirstName - Text
EmployeeCompanyNumber - Text

tblHR
Department - "HR"
EmployeeID - Autonumber (Primary Key)
LastName - Text
FirstName - Text
EmployeeCompanyNumber - Text

etc etc

That would work but this doesn't seem right. It seems to make more sense as

tblEmployee
DepartmentID - "HR" (combined Primary Key)
EmployeeID - (increamental number for the department) (combined Primary Key)
LastName - Text
FirstName - Text
EmployeeCompanyNumber - Text

maybe autonumber was the wrong term. I want the ID to increament depending on department by 1 each time.

 

[SOS]

It is obvious that you do not understand normalization. You need to read and UNDERSTAND this:
Normalization
BEFORE going further.

You should not have separate tables for Admin and HR because they have the exact same fields. And, frankly, you should let Access handle the primary keys. Use an autonumber for each of the primary keys and then if you can't have duplicates set a multi-field index.

tblEmployee should have
EmployeeID - Autonumber (Primary Key)

and NOT a composite between Admin and HR. You also don't put the Department ID in the Employees table. You use a JUNCTION table between the departments (ONE DEPARTMENTS TABLE FOR ALL DEPARTMENTS) and the Employees table. That way you can record the department they are in, with the effective date, because employees can, and do, move around. If you do that you will always have history as well.

 

[davidfourie]

Thank you SOS for your increadably insulting and complicated post. Let me answer you critism in a polite but firm fasion:

1.

It is obvious that you do not understand normalization.

this is increadably insulting. Yes I would say I am a bit rusty but to quote my self:

That would work but this doesn't seem right

I said in the post that splitting the employees into seperate tables depending on their department is not right. I can't see any fault with the level of normalisation in the table:

DepartmentID - "HR" (combined Primary Key)
EmployeeID - number (combined Primary Key)
LastName - Text
FirstName - Text


2.

(ONE DEPARTMENTS TABLE FOR ALL DEPARTMENTS)

I have never said I need to store department information. What is the point of having a table that would comprise of:

tbl department
departmentID (Autonumber - primary key)

3.

employees can, and do, move around

yes they do. But in this project it is irrelevent.

I hope that has gone some way to answering the critism laid on my question by SOS. I apologies to any others reading this but hope you might forgive the tone of this post, considoring the message that was it's inspiration.
____________________________________________________________

This is a very small database I am creating for a personal project. I do not need to worry about Bill Gates turning up at my house and complaining that it is not forth form normalised.

The question is

How do you have seperatly increasing numbers depending on another field?

if the new record is give FieldA = A, FieldB = FieldACounter + 1

Please if this isn't possible that is an answer but I am sure there is a method or at least an alternative without breaking the table up.

 

[SOS]

Thank you SOS for your increadably insulting and complicated post. Let me answer you critism in a polite but firm fasion:

1.


this is increadably insulting. Yes I would say I am a bit rusty but to quote my self:



I said in the post that splitting the employees into seperate tables depending on their department is not right. I can't see any fault with the level of normalisation in the table:

DepartmentID - "HR" (combined Primary Key)
EmployeeID - number (combined Primary Key)
LastName - Text
FirstName - Text

Using a composite number as a primary key will only cause you grief and pain. Use a freakin' autonumber as the key. The key is ONLY for Access to maintain relationships from one table to the next and as such IT should be the source to maintain those relationships. I did say you could add a multi-field index to your table so that you don't get duplicates of the data, which is basically all your composite key is good for anyway.

2.


I have never said I need to store department information. What is the point of having a table that would comprise of:

tbl department
departmentID (Autonumber - primary key)

Of course you don't have just that, you would have
departmentDesc - Text (HR, Admin, etc.)
as well to identify what the key goes to.

3.


yes they do. But in this project it is irrelevent.

I've heard people say that SOOOOOOOOO many times it would make your head spin and then 6-12 months later they are finding that they need to modify their structure because some management type decided they liked how things were being done with this database and then they wanted additional information or tracking. So why not design it correct up front and not suffer (or make someone else suffer) down the road?

I hope that has gone some way to answering the critism laid on my question by SOS. I apologies to any others reading this but hope you might forgive the tone of this post, considoring the message that was it's inspiration.

Sorry for the negative tone, but frankly your post does show (to anyone who is a professional database developer) a very high lack of knowledge and I'm sorry if the truth hurts. I'll try to not be as "blunt" in my postings from now on.

...and complaining that it is not forth form normalised.

that would be FOURTH (spelling) - but again -

If you BAND-AID FIX IT NOW, you will be back here later asking us how to fix the dang thing or how to make something else work which would have worked perfectly IF you had designed it correctly up front.

 

[SOS]

And as it is clear you do NOT want to do this correctly, I will no longer respond to your posts. I'll let others assist as I'm sure that there are some who frankly don't care if you build it badly and then come back for more band aid fixes. If I want to do a job I want to do it right the first time.

 

[Atomic Shrimp]

Not sure this works for me.

the employees details are stored on a paper based system.

They are put into files depending on their department (HR, ADMIN, ACCOUNTS) and ordered in chronological order by when they start.

hence the ADMIN 1, ADMIN 2 etc.

If you use a single autonumber, they will still generally sort in chronological order - as each person joins each department, they get assigned the next available autonumber, which should* be numerically higher than the autonumber allocated to the previous person who joined the department. It won't be consecutive, but why does that matter?

Better still (*and more reliable, because autonumbers aren't guaranteed not to skip a big chunk of numbers, then revisit that gap later), add a 'join date' field in the employee table and use that to sort them when you want them in the order they joined the company.

The question is

How do you have seperatly increasing numbers depending on another field?

There's no way to make an autonumber do this.
You'd have to write a custom function to generate the next available number, but as others have said (perhaps a little too pointedly, but still quite factually) it seems like a terrible idea.
Not because anyone is going to scorn your design, but because it seems very likely to cause problems for you in future.

You may say you don't think it will cause problems, but, and I mean this in the kindest possible sense, most people with experience of normalized database design are going to disagree with you.

What is the purpose of numbering the employees sequentially within department? - what do you intend to use the number for, that would not be achieved by sorting them by join date within department?

 

[davidfourie]

You'd have to write a custom function to generate the next available number

yes please.

Please can someone explain how I would do this. That sounds like it is the answer.

 

[Atomic Shrimp]

I honestly don't believe it is the answer - it's a stepping stone toward a future pitfall. Why do you need the records consecutively numbered within separate sequences per department?

What I'm trying to get at is: what do you hope this will this enable you do with the data that can't be achieved just as efficiently by some much simpler and more robust method?

 

[davidfourie]

Mike,

the records are stored on paper in departmental folders.

When i search the database for an employee it must give me the department they are in and their record number so I can go to the correct file and get out their record.

I am still don't understand the problem. With the set up. A combined primary key is a perfectably acceptable normalised structure. Is it the coding?

 

[Atomic Shrimp]

Mike,

the records are stored on paper in departmental folders.

When i search the database for an employee it must give me the department they are in and their record number so I can go to the correct file and get out their record.

I am still don't understand the problem. With the set up. A combined primary key is a perfectably acceptable normalised structure. Is it the coding?

Yes, it's potentially a moderately complex thing to code properly, and there are probably all kinds of unforeseeable ways it could go really horribly wrong later.

I wish I could help, but in all good conscience, I don't think I can feel confident enough of any solution I offered to this very specific brief - it seems to me that you're trying to create an electronic version of what is already a less than optimal physical filing/indexing system.

If the paper filing system dictates the numbering for the records in the electronic database (that is, if the files appear as numbered objects in the filing cabinet before they are created in the database), then you're probably going to have to input the numbers manually, or you'll be surprised one day when everything gets out of sync.

If the database is going to dictate how the paper filing system is arranged (i.e. the reference numbers originate in the electronic system you're creating), then why not just index them by date?

If you were designing the whole thing from scratch (paper filing and database), would you choose to do it this way?

 

[Atomic Shrimp]

If you do want to press on with this method, you might find something in some of the previous threads on similar subjects - here's one that is conceptually quite similar to yours:
http://www.access-programmers.co.uk/forums/showthread.php?t=173336

 

[sakthivels]

Hi,

I would suggest create new table say tblSerial and have as many fields as your department. Use a custom made function to set the next serial in the field and use them.

I got this idea from another post / url which I am not sure. But I am uploading the example which you can experiment with.

We must be thankful for the person who originally posted this.
Regards,
Sakthivel.