Totally Confused

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


"John W. Vinson" wrote:

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]

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


"Joan Wild" wrote:

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

On Tue, 3 Jun 2008 12:07:03 -0700, Aria
wrote:

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

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


"Joan Wild" wrote:

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

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


"John W. Vinson" wrote:

On Tue, 3 Jun 2008 12:07:03 -0700, Aria
wrote:

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

John W. Vinson [MVP]

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


"Arvin Meyer [MVP]" wrote:

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


"Joan Wild" wrote:

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

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


"Arvin Meyer [MVP]" wrote:

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


"Joan Wild" wrote:

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

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


"Arvin Meyer [MVP]" wrote:

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


"Arvin Meyer [MVP]" wrote:

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


"Joan Wild" wrote:

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