FAO Pat Harman or any other helpful people

Lyncroft

QPR for ever
Local time
Today, 22:52
Joined
May 18, 2002
Messages
168
I have 9 regions. Within each region there are 50 odd councils. Within each council an employee is given a certificate number which is unique to that region.

For example, if the council is in Region 1 the employee gets given GO1/1, the next person in that region gets GO1/2, etc. In Region 2 it would go GO2/1, GO2/2 and so forth.

Whats the best way to design the tables and relate them. At the moment I've got one table for the Regions and created separate tables for each region with the relevant ID autonumber (GO1/, GO2, etc) on each table. I 've then related them by a 1-many.

To input the info I planned to a main form with a combo fox for the regions and when you select the region the relevant subform appears.

Is this the bes way to go about it bearing in mind I'll need to do queries etc.

Thanks
 
You need 1 table for region, 1 table for council, 1 table for region/council, and 1 table for employees.

You are making a lot of extra work for yourself by adding "intelligence" to the EmployeeID. Just use an autonumber and keep the region and council as data fields.
 
Thanks for that Pat. Out of interest what do you mean by "intelligence"?
 
you wrote
<<
For example, if the council is in Region 1 the employee gets given GO1/1, the next person in that region gets GO1/2, etc. In Region 2 it would go GO2/1, GO2/2 and so forth.
>>

What the comment about "intelligence" means is that you identify the Region by a value in the key; 01 for region 1, 02 for region 2, etc.

It is usually better not to do this. Make the key a plain old AutoNumber and make Region an attribute. Intelligent numbering schemes seem great at first but over time the scheme usually fails.

For example, if you use G01 through G99 as part of a key,what happens when region one hundred appears ?

RichM
 
Have to admit a bit confused now. How would I tally up the employee to his certificate number then. I'm bound to using GO1/1, GO1/2 and GO2/1, GO2/2, etc because thats what they're using.

Have to admit it does look very cumbersome but with each region having a different certificate code not sure how to progress. As it stands the customised autonumber will just continue even when it gets to 100
 
Sorry see what you mean about the regions. No the number of regions will always remain at 9
 
you wrote
<<
Have to admit a bit confused now. How would I tally up the employee to his certificate number then. I'm bound to using GO1/1, GO1/2 and GO2/1, GO2/2, etc because thats what they're using.
>>

No problem. We assume the employee knows that their cert number is "GO1/1".

In a table, GO1, GO2, etc would be stored as "region". The part after the slash would be stored as some other named variable, call it "bob" for now.

The primary key of the table would still be an AutoNumber that is unknown to the human user.

When you want to find a specific record, say GO1/1, you create a WHERE clause like
tblEmp.[Region]='GO1' AND tblEmp.[bob]=1

When you need to add a new employee, you enter the Region or select it from a combo/list box. If you want to maintain the value of "bob" in increments of one, then you have to write a little code to find the maximum of "bob" for Region GO1 and add 1 for a new bob.

you also wrote
<<
No the number of regions will always remain at 9
>>

No, they just told you it will remain at 9 :)

And some day they will tell you or your successor; oops sorry we really didn't mean that. Been there many times.

HTH,
RichM
 
Rich

Thats very helpful and I'm gradually digesting it. ..................just one last thing ..............................what would that "little code" be that you mention.

Thanks again
 
Somethink like
MaxBob = DMax("[bob]","tblEmployee","tblEmployee.[Region]='GO1' ")

NewBob = MaxBob + 1

Insert a record with the value of NewBob.

RichM
 
Rich - one last thing then I promise to leave you alone. Where exactly would this code go?

Cheers
 
Lyn,

I assume your application will have some form to maintain people in an Employee table. On this form you would expect to have textboxes where the user can enter data for a new employee. Then you would make a command button to "Add".

In the sub for the "Add" On Click event, you would write code to insert a new record. The Region for the new record would be from user entry on the form. The code example I posted would be placed before you add the new record.

BTW, I don't remember if DMax returns zero or Null if there is no existing record for a Region. If Null, then NewBob = 1.

RichM
 
It stand for For the Attention of - must be an English thing I guess
 

Users who are viewing this thread

Back
Top Bottom