[mso] Re: Access Question

Christine,

You wrote: 
> I have a database with 3 tables.  Table one is the employee 
> listing table.  It has fields that include the employee name, 
> office number, office name and specialty.
>  
> Table 2 is a table of all the office numbers and corresponding names.

You have redundant information, it seems.

Table 1 (Tbl_EMPLOYEE) should include the following data:
        Employee_ID
        Employee Name
        Office_ID
        .... [other information pertaining to a paticular employee]

Table 2 (Tbl_Office)would then contain the following data
        Office_ID
        Office Name
        .... [other information pertaining to a particular location]

You would then create a relationship between the two tables on Office_ID and
a query (Qry_EmployeeDetails)to include the data you wanted on the Form.

Consider what happens if an employee moves office? (You simply update their
record in Tbl_EMPLOYEE with the new Office_ID)

Consider what happens if an Office location moves/changes? (You simply
change the record for that office in Tbl_Office)

It can get a whole lot more complicated, of course, if you wanted to include
extension phone numbers, management information, salary information, &c

1) A table contains information pertaining to one type of object only (e.g.
Employee, Office, Manager). 
2) Redundancy of information should be avoided
3) Relationships between tables should usually be on a one-many basis
(sometimes one-one).
4) Many-many relationships are avoided by creating small linking tables
which connect to other tables using several one-many relationships. (This
happens, for example, if an employee reports to more than one manager.  "A
Manager may have many employees reporting to her" and "An employee may
report to more than one manager".  

This may be going beyond what you need at the moment but it is worth
thinking about the 'future-proof' quality of your information.  It is a
well-known fact that what starts out as a simple phone-list can turn into a
company-wide employee management system!

It may be worth investing in a "Database for Dummies" book [fictitious
title, perhaps, but the 'For Dummies' are a good series] or checking out
'Database structure' and 'Database design', 'database relationships',
'joins', &c through Google.  Include 'tutorial' in your search string,
perhaps. Here are a couple of links at random (well, nearly):

http://www.schools.ash.org.au/olshc/infotech/dbdesign.htm
http://www.katsueydesignworks.com/tutorials_databases.htm
http://office.microsoft.com/en-us/assistance/ha010563211033.aspx


The Northwind database (supplied with Access since the year dot) has
examples of all these kinds of relationships, queries and forms.



Andrew  


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: