splitting up tables

[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!

 

[psemianonymous]

It looks like you have good questions, even if you don't know the specifics of the "hows" to achieve what you need.

1. Depending on how sensitive this information is, you may want to set up this file in a separate network folder that only you have access to. Talk to your supervisor and/or your IT/IS department as for the specifics of this (and gauging(sp?) the need for security). The only downside to splitting the information is your database's complexity, but the benefits aren't that great either. This all depends on who is going to have access to the data.

Let me just add that if you are going to implement Access' built-in security, splitting the tables is VERY necessary.


2. There are many ways to do this, growing in complexity each time (but maybe getting better solutions?)

-An IS_CURRENTLY_EMPLOYED flag (Yes/No field) that you update to "false" when they leave and "true" when they re-arrive. Can be troublesome for historical reporting.

-Two fields that list BEGIN_DATE and END_DATE. Current employees have no END_DATE filled in. If an employee returns, you fill out an entirely new record for them with a new BEGIN_DATE and a new (blank) END_DATE. Their address and phone number have probably changed anyway, right?

-A table that has an employee number, a begin date, and an end date. Using complex queries, you set the end date when they leave and create a new "interval" when they re-join the company. This is very similar to the above solution, but more normalized (meaning no redundant data). For all the hassle though, consider using the above method instead.


3. You can do this a few ways, too. Simple first:

-Just have a predetermined list of values and let the user enter whatever they want, even if it is not in the list of predetermined values. Very simple.

-Set up your table and pull information into your combobox from the table. Also allow your users to enter whatever they want into a new employee's title. Periodically pull information from the employee table and "update" the main table via a query to include all entries that were manually entered. Again, somewhat simple.

-There is a "NotInList" feature that will allow you to add the manually-typed value into the table immediately, but I'm not going to go into those details. Search Google Groups or this forum for others who have asked the same question. It could be as simple as plug-and-play, but it could be monstrously complicated (as a rule, dealing with control and/or form events is not simple).


4. There is a whole debate about what to use: meaningless ID number or meaningful unique field? I'd recommend meaningless Autonumber field to store the employee ID, unless your company already has ID numbers. Also, beware of using the company ID number if you plan to allow the same employee two items in the "employee table" for returning employees. This means that Bob, after a two-month leave, returns to work with the same employee ID but a different ID in your system. Anyway.


5. Access' built-in security has a pretty steep learning curve, but depending on the sensitivity of the information and who is looking at the information, you may be able to avoid using Access security.

-An extremely simple method is to have TWO network folders containing your information: folder one has a database with the public information, folder two has your sensitive information. Use linked tables to access the sensitive information; if the user does not have access to the "sensitive" network folder, they will not be able to see any of the info. This is honestly as simple as it gets.

-Beyond that, search for the "MS Access Security FAQ", located everywhere on the web.

http://www.abcdataworks.com

has one copy and is the easiest link I can remember.



Good luck

Pete

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[psemianonymous]

PS--about linked tables. Once you have a linked table set up, in works in every way like a local table. The network folder solution is probably best because, as others will tell you, there is no good security solution for Access databases. Restricting the users' access to the file itself is the ONLY way of protecting the information from people who shouldn't be seeing the data. Even with the best Access built-in security set up, you data is still crackable.

Linked table setup accessible via:
File->Get External Data->Link Tables

if you switch the location of the database file you are linking to, you will need to switch the table links as well. Delete the old and import new ones.