Totally Confused

[Aria]

This is my very first database and it's not going as smooth as I hoped. I am
trying to create a staff database for our school. There are 6 tables so far:
tblEmployees, tblRooms, tblKeys, tblKeysRequests, tblSubs and the juction
table, tblKeysEmployees.
The juction table has 2 primary keys:
KeyID and EmpID
When I join the the juction table to tblEmployees, I am not able to enforce
referential integrity. Should I be concerned with this? Also, I cannot
establish a many to many relationship. Isn't this what I need since many
employees can have the same key and many keys are assigned to many employees?
Also, since each key has a unique identifier and I have KeyID in multiple
tables, how do I handle that?
I know these are basic question that everyone already knows the answer to
but I would be very grateful for any help or suggestions. Thank you very
much!
but

 

[Arvin Meyer [MVP]]

Yes you should be concerned about not being able to establish referential
integrity. It most likely means that an EmpID has been deleted from the
Employees table, but data remains in tblKeysEmployees. For an example of how
to build a many to many relationship see:

http://www.accessmvp.com/Arvin/ManyToMany.zip

 

[KARL DEWEY]

Reduce you number of tables.
I woul drop tblRooms as the key identifies which room it goes to. Drop
tblKeysRequests as request would be entered into tblKeysEmployees pending
issue.
And I do not know what you intended tblSubs to do.

tblEmployees ---
EmpID - Autonumber - primary key
LName - text
FName - text
MI - text
Phone - text
etc...

tblKeys --
KeyID - Autonumber - primary key
Room - text
Type - text - Master, Schedlge, Weslock, Yale

tblKeysEmployees --
KeyEmpID - Autonumber - primary key
EmpID - Number - Long Integer - foreign key - associated to Employees
KeyID - Number - Long Integer - foreign key - associated to Keys
IssueDate - DateTime
EstReturn - DateTime
Return - DateTime
Rmks - text

Create a one-to-many relationship between Employees and tblKeysEmployees on
EmpID.
Create a one-to-many relationship between tblKeys and tblKeysEmployees on
KeyID.

Use form/subform for employee/keys and another for room/employees.

 

[Aria]

Thanks for replying Arvin. I failed to mention that there isn't any data in
the tables. I had tried creating the database before with a few entries, but
I had the same problem then as now; I couldn't enforce referential integrity.
Can you tell me how I should create the link between the employees table and
the juction table? I was told that I should link employee ID to both primary
keys in the juction table (emp. ID and Key ID). Is that true?

 

[John W. Vinson]

I was told that I should link employee ID to both primary
keys in the juction table (emp. ID and Key ID). Is that true?

No.

You should link the Primary Key of the Employee table to the EmpID, and link
the primary key of the Keys table to the KeyID.

 

[Aria]

O.k., so the Emp. ID in tblEmployees and the Emp. ID in tblKeysEmployees is
a one to one relationship? The Emp. ID in tblEmployees is an autonumber. I
want the same employee number throughout the database but in tblKeysEmployees
it does not reflect this. How should I handle this? Look-up wizard?

 

[Aria]

Karl,
Thank you for your suggestions. I wondered about the rooms table. The
instructor who taught the Access class told me I needed that table. Do I
need an autonumber for Key ID since each key has a unique identifier (XK-2,
BG-42, etc.)? I will add the issue/return dates and remarks to the table; I
overlooked that.

 

[KARL DEWEY]

Do I need an autonumber for Key ID since each key has a unique identifier
(XK-2, BG-42, etc.)?
No it is not necessary but I find it helpful. Otherwise you have to make
other change to the database -- The primary key field will need to be text to
handle the labels on the keys. Also the foreign key fields must be text
also.

 

[John W. Vinson]

O.k., so the Emp. ID in tblEmployees and the Emp. ID in tblKeysEmployees is
a one to one relationship? The Emp. ID in tblEmployees is an autonumber. I
want the same employee number throughout the database but in tblKeysEmployees
it does not reflect this. How should I handle this? Look-up wizard?

NO! <shudder> The lookup wizard will certainly not help and may cause even
more confusion and hassle than you have now.

Aria, you can see your database. You know your fieldnames. I cannot, and I do
not.

Please post the names of your tables; the relevant fieldnames and datatypes;
indicate the Primary Key of each table.

My intention was a one to MANY relationship, not a one to one relationship. It
sounds like you have an incorrect primary key set somewhere... but since I
don't know how your tables are structured I cannot say.

 

[Joan Wild]

EmpID is an autonumber in tblEmployees
KeyID is an autonumber in tblKeys (I assume)
The junction table is called tblKeysEmployees. This table has EmpID (needs to be defined as a Number of Long Integer size; and it has KeyID (again define it as Number/Long Integer).

It sounds to me as though you defined EmpID ad KeyID in tblKeysEmployees as autonumbers.

--
Joan Wild
Microsoft Access MVP
: O.k., so the Emp. ID in tblEmployees and the Emp. ID in tblKeysEmployees is
: a one to one relationship? The Emp. ID in tblEmployees is an autonumber. I
: want the same employee number throughout the database but in tblKeysEmployees
: it does not reflect this. How should I handle this? Look-up wizard?
: --
: Aria W.
:
:
: "John W. Vinson" wrote:
:
: > On Mon, 2 Jun 2008 08:28:04 -0700, Aria <[email protected]>
: > wrote:
: >
: > >I was told that I should link employee ID to both primary
: > >keys in the juction table (emp. ID and Key ID). Is that true?
: >
: > No.
: >
: > You should link the Primary Key of the Employee table to the EmpID, and link
: > the primary key of the Keys table to the KeyID.
: > --
: >
: > John W. Vinson [MVP]
: >

 

[Aria]

O.k. I don't have data in any of the tables. The first time I tried, I input
a few entries into the tables but the result was the same as now.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
Type(Classroom, Auditorium, Grand Master, etc.)- text

tblKeysEmployees
Key ID (PK)- text
EmployeeID (PK)- Autonumber

tblKeysRequests
Key ID (PK)- text
LN- text
FN- text
M.I.- text
Rm. number- text
Rm. phone- text
Date Requested- Date/time
Date Recvd.- Date/time
Date Issued- Date/time

tblRooms (Advised to drop this table)
Key ID (PK)-text
Room number- text

 

[Aria]

You're right. Key ID is not listed as long integer but as text because each
key has its own identifier which is a combination of text and numbers. EmpID
is listed as an autonumber, long integer. I'll change that. Thank you.

 

[John W. Vinson]

O.k. I don't have data in any of the tables. The first time I tried, I input
a few entries into the tables but the result was the same as now.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text

Might one person be responsible for multiple subjects? or work in more than
one department? If so you may really want a many to many relationship.

Personal Info (LN FN MI etc.)- text

Separate fields I hope? Should be.

Emergency Info - text

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
Type(Classroom, Auditorium, Grand Master, etc.)- text

tblKeysEmployees
Key ID (PK)- text

Same field size as the Key ID in tblKeys, it should be...

EmployeeID (PK)- Autonumber

BZZT!!! That's the problem. The employee ID in Employees can be autonumber,
this must be Long Integer.

tblKeysRequests
Key ID (PK)- text
LN- text
FN- text
M.I.- text
Rm. number- text
Rm. phone- text
Date Requested- Date/time
Date Recvd.- Date/time
Date Issued- Date/time

I don't think the Key ID should be the PK here: that would allow each key to
have one and only one request, ever, period. Key ID should be a *foreign key*,
and the table should have its own primary key (perhaps an Autonumber, perhaps
a two field key consisting of the Key ID and the Date Requested.

If LN, FN, Rm Number are the same as fields in tblEmployees and tblKeys then
they SHOULD NOT EXIST in this table. That would be redundant. All you need is
the link, the Key ID and Employee ID; you can pick up the other fields using a
Query joining the tables (or a Combo Box on a form). A person has a last name;
a request for a key does NOT have a last name!

tblRooms (Advised to drop this table)
Key ID (PK)-text
Room number- text

You might need the rooms table if each room can have multiple keys and you're
tracking the keys individually.

 

[Arvin Meyer [MVP]]

If possible, keys should be long integers. There are several reasons, but in
my opinion, the most compelling is that searches are faster through a 4 byte
number than through text which can vary both in bytes and complexity.
Besides, it is quicker and easier to deal with numbers in your code than
with text.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Aria]

I read your responses and before I comment, I just want you and all of the
other MVPs to know how grateful I am for your patience, your advice and your
generosity with your time. You have no idea. I have also been very impressed
by not only the responses I have received, but others that I have read. I
really want to learn how to do this but I don't have any one to ask here and
I haven't been able to figure it out on my own. Thank you so much.

tblEmployees
School Data (Classification, Title, Dept. Name, Subject)--You are correct.
One person CAN teach multiple subjects but usually not different depts. I
hadn't considered that at all. Many-to-many relationship? I have a question
about this.

Personal Info--Yes, these are seperate fields.

tblKeysEmployees
EmployeeID--I have corrected this error and it now says long integer.

tblKeysRequests
Key ID (PK)--Only one key request ever?! WHY?! No, we can't have that. Given
my lack of knowledge and experience, maybe I should make this an Autonumber
as you suggest.

I do understand that a person has a last name and a request does not. I just
didn't know how to get the information I needed.

tblRooms
Yes, each room can and does have multiple keys (Grand/Wing master,
classroom) and I am tracking each key individually so I will keep this table.

I forgot to include:
tblSubs
Sub ID (Auto number)- PK
Personal Info (Sub LN, FN, MI)
Preferred Subject
Key ID
Date Issued
Date Returned

I said I had a question about the many-to-many relationship. Actually, I'm
having problems creating any of the relationships I need. I have checked my
books and the help section for Acces. I believe I am following the steps
correctly but I don't know what the problem is. To link the tables, I clicked
on the relationship button and then dragged and dropped the Primary Key to
the Foreign Key. It defines the relationship for me and I can't make any
changes. I have noticed that for some of the tables it says the relationship
is one-to-one when I need one-to-many. I feel like an idiot. What am I
missing?

 

[Aria]

Arvin,
I was going to ask you additional questions about this but I ran across
another post you answered where you included a link for Crystal's basics
tutorial. I read it and now I understand. Thank you.

 

[Arvin Meyer [MVP]]

There is a HUGE amount of information at the 3 websites in my sig, below.
There are links on 2 of them:

http://www.mvps.org/access
http://www.accessmvp.com

to many other MVP and former MVP websites with even more information. It
would takes months (or possibly years) to absorb it all.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Aria]

I've been to the 2 sites listed below. Yes, there is LOTS of info. So far,
absorption seems to ebb and flow like the tide. Sometimes I understand
clearly, then 10 minutes later I DON'T understand. It's frustrating...