checkboxes, forms, and tables 2

[kellybw]

I am new to the world of access, and full of questions. First of all I guess I should explain what I am trying to do and then what I've got so far. I am having to create a database that will hold a user rolebase. We have 10 systems and around 900 employees. Each employee has different logins to the respective systems. I have created an EMPLOYEE table that has badge # (primary key), first name, last name, and department. I have also created a table that has each SYSTEM as well as employee badge #. I have been working on a form that has the employee badge#, first name, last name, department. That part of the form works great. I would like to list each system at the bottom of the form with checkboxes and as the user selects what system the employee has security to have a field pop up that would allow the user to enter in the employee's login information on the same form. And as the information is entered in it updates the system table. As I said before I am new to this and full of questions, so any help or suggestions would be greatly appreciated. Thanks!!

 

[LittleSmudge]

First off Kelly - Welcome to Tek-tips.


Before you launch into the FORM design - you need to sort out your schema ( tables, fields in each table, relationships etc. )
So of the 6 MSAccess boards on the tek-tips site you've hit the right one to start.

Next, as you're new - read the FAQ faq700-2190 on why you should NEVER use space characters in anything you give a name to.


So to the schema.

you have an Employees table and you'll need a Systems table to hold the list of all the availalbe systems.
Put them in a table rather than tick boxes on a form so that any old user can add new systems to the list when they come along - rather than having to redesign the form every time.

Then you need to be able to link these together in such a way that one Employee can be linked to many Systems and many employees can be links to any one system.
This is a classic Many-to-Many relationship and needs a Linking table between them to achieve the stunt.

Fields in the linking table are TWO
The first is a fireign key to the Employee table
The second is a foreign key to the System table

Then make both of these fields into a joint Primary key.
That way you Guarantee that the database can never link person 47 to System B more than once.


Then it is a matter of having two combo boxes.
The first one bound to the link table and showing just the records where the employee Id matches.
This shows a list of the currently allocated Employee-system links.

The second list box is linked to the system table and has a Where clause to Exclude any records listed in combo1
This lists all of the Systems available to be allocated to the current employee.

In the After_Update event of combo1 you put some code to REMOVE a record from the linked table based on the current employee No. and the row selected in the combo1

In the After_update event of combo2 you put some code to ADD a record to the linked table based on the current employee No. and the row selected in the combo2


Chew on that lot for a while and come back for more detail when you get to bits that you need more assistance with.


'ope-it-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[kellybw]

Thanks so much for your help!! I have done as you have suggested. I have an employee table, a systems table, and a linking table. The linking table is what I'm not too sure about. In this table I only have two fields. I have the badge# field from the employee table and an ID field from the systems table. But the ID field is an auto number field. Is that going to cause me problems? I didn't really have another field I could use from the systems table. I have set the badge# and ID fields in the linking table as joint primary keys.

Also my relationships, I'm not too sure about them. I have a one-to-one relationship between the employee table and the linking table. And a one-to-many relationship between the systems table and the linking table.

 

[willir]

Kelly

Review...

http://www.abcdataworks.com/fundamentals.htm

or

http://r937.com/relational.html

And more advance...

http://www.mvps.org/access/tencommandments.htm

When working with relationships, ask questions...
Can an employee have belong to many systems?
Then turn the question around.
Can a system have many employees?
(Graham. a long-time star of Tek-Tips, discussed this in his post)

If the answer YES to both, then you may have a many-to-many (M:M) relationship. You need a joiner table for this - tblEmpSystem with EmployeeID + SystemID.

If YES to one but NO to the other then you have a one-to-many (1:M). Here you include the primary key from the one-side (where you answered NO) as a "foreign key" on the many-side.

Assuming that you have the M:M, it works as follows...

tblEmployee
BadgeNum - primary key (consider dripping the "#" octophorp)
EmployeeLN - text (last name)
EmployeeFN - text (first name)
etc

tblSystem
SystemID
SystemName

tblEmpSystem
BadgeNum - foreign key to employee table
SystemID - foreign key to system table
AccessLevel
StartDate
TermDate

Primary key, as indicated by Graham is BadgeNum + SystemID

It is irrelavent if the primary key for tblSystem is an autonumber or not.

I included the AccessLevel, etc to show how you can capture information on the "joiner" table to use it as a profile.

...Moving on
A one-to-one (1:1) relationship is not common. As part of normalization (read literature for this process), anything on one table or the other should be on one table.

However, a 1:1 is used effectively in some situations....
Most common is in isolating secure information. In an employee table, you have public information (eg. their name, title, manager), and private or confidential informaiton (eg. salary, health perhaps age). The "public" table is commonly used by all. The "private" table may be on another (linked) database, and is only accessible to HR.

The last thing, and I have not seen it mentioned yet; when designing a database, look at your expected outcomes. What type of information are you trying to track - reports, trending, etc. This is a basic step in design since a design impacts what reports you can get. A flaw in the design may only become apprent if you find that you can not retrieve your data properly to provide informaiton because it is not tracked or accessible.

Good luck Kelly.

Richard

 

[LittleSmudge]

Thanks willir.

I've been at the MSDN technology roadshow event at the NEC for the last two days so I've not been able to get back here since before the weekend.

Your contribution has added significantly to what I was getting at.






G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[willir]

Thanks Graham for the kind words - coming from you, that means a lot.

 

[kellybw]

Thank you both so much for your help. I have my tables built and have imported my employee info into my employee table. I have tested a few employees by adding in their different system info, and thanks to ya'll it seems to be working great. I have now moved on to my form. I have the employee info on the form (employeenumber, lastname, firstname, and department). I have the system and userid fields in a subform. This seemed to be the easiest way to design it. What do ya'll think?

Again, thanks soooo much for your help. It's great knowing that there are guys out there like ya'll that take the time to help people like me, the "access challenged"....