Totally Confused

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
--
Aria W.

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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Aria" wrote in message
...
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
--
Aria W.

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.
--
KARL DEWEY
Build a little - Test a little


"Aria" wrote:

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
--
Aria W.

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?
--
Aria W.


"Arvin Meyer [MVP]" wrote:

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
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Aria" wrote in message
...
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
--
Aria W.

On Mon, 2 Jun 2008 08:28:04 -0700, Aria
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]

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
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]

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.
--
Aria W.


"KARL DEWEY" wrote:

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.
--
KARL DEWEY
Build a little - Test a little


"Aria" wrote:

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
--
Aria W.

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.
--
KARL DEWEY
Build a little - Test a little


"Aria" wrote:

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.
--
Aria W.


"KARL DEWEY" wrote:

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.
--
KARL DEWEY
Build a little - Test a little


"Aria" wrote:

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
--
Aria W.

On Mon, 2 Jun 2008 10:51:00 -0700, Aria
wrote:

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.
--

John W. Vinson [MVP]

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
"Aria" wrote in message ...
: 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
: 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]
: