splitting up tables and building relationships

[ljscott]

hello, i need help with splitting up a huge spreadsheet of information and entering it into a access database.

here is what i have:

a bunch of 'public' info: name, job title, work phone, etc.

private info: home address/no., soc sec, etc.

problems:

1.should this info be split into two different tables (ie. private and public information)?

2.i would also like to leave peoples info in the database after they leave the company(turnover is pretty high here), but i'm not sure of a good way to designate whether they are with the company still or not.

3.I would like job titles to show up in a drop down menu, with the option of entering a title that isn't there. Should I create a separate table to contain these job titles? If so, can I set if up so that when a person fills in a new job title using a form, the list of previously entered job titles gets updated?

4. if the information is split in private v. public info, do i include the persons name in both tables, or link the two of them in some other manner? what should the primary keys be?

5. also, is it possible to make a certain portion of the database viewable by users (public table) and make another section (private table) require a password?

i obviously don't know much about what i am doing here. I have created this database already, but i am not happy with what i have. i know just enough to confuse myself easily. i would appreciate any input.

Thanks!

 

[LittleSmudge]

1) Theres no good reason to split Public/Private - The Forms and the User/Group security will do all that for you

2) Include a DateOfLeaving field. Current employees are then those with a Null value in that field.

3) Yes.
2 choices come to mind
A) On your form you put a combo box bound to the JobTitle field
In the combo's RowSource you put
"SELECT Distinct JobTitle FROM tableName"
and you set the combo's LimitToList property to No

B) Extract the current titles into a table and add a primary key called JobId
The main table then has a JobRef field of the same type as JobId
The combo box on the form is BOUND to JobId with the Row source looking like
"SELECT JobRef, JobTitle FROM tblJobTitle"
Set LimitToList to Yes
Set ColumnCount to 2
Set ColumnWidth to 0cm;

Then in the OnNotInList event you have to put code that :-
Check with the user that they REALLY want to add a new Job Title
Then adds the NewData to the tblJobTitle.

( Being a control freek where data of this type is concerned - I only allow certain users to add data to tables like tblJobRef - otherwise you end up with all sorts of miss spellings, adaptations and it quickly becomes a complete mess )

4 As 1 - Don't do it.

5 ( as well as 1 and 4 )
Read up on Access's own Security Model
Type Security into the Help Wizard
You do not want a password on the database. You need User/Group security.
Firm Advice - Take a backup before you start and make regular backups as you go through the security process.
Mistakes - even by the experienced - have been known to be unrecoverable.

Having said that Access security is a useful tool to master.


'ope-that-'elps.





G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.