A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lookup field and Relationships



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2010, 06:44 PM posted to microsoft.public.access.tablesdbdesign
jrav
external usenet poster
 
Posts: 1
Default Lookup field and Relationships

I am having trouble understanding a design concept. I am setting up an
employee database for the first time. At issue is how to handle the actions
entered into a log table. I have a table for employee's info, and log table
that i am using to keep track of actions performed on employees, i.e. new
hire, raise, termination, etc. It seems appropriate to have the log table
include a FK for an actions table with all of the possible actions.

in the design view of the log table, i can see that the action field is set
as a combo box with a row source of the actions table PK, yet I don't see a
relationship in the relationships view. Is this a problem? I tried dragging
the field over to establish the relationship, but the field properties don't
match. (log table = text) Changing the data type to number wants to delete
all of the entries. So it seems like I should do an update query to change
the text to the code of the FK from the actions table to create the
relationship.

Bottom line, why is there no relationship, and does it matter long term?

thanks in advance.
  #2  
Old April 15th, 2010, 08:34 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Lookup field and Relationships

It sounds like you are describing trying to use the "lookup field" data type
in an Access table. If so, then you are re-discovering reasons why this is
not a good idea.

For one thing, as you point out, it's hard to spot the relationships.

For another, the field in the table displays the looked-up value, but it is
actually storing the key value.

For yet another, working directly in the table like this is a mistake. If
you only had a spreadsheet, you'd use it ... but Access only stores data in
tables. You use Access forms to add/edit data because forms provide much
more control.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"jrav" wrote in message
...
I am having trouble understanding a design concept. I am setting up an
employee database for the first time. At issue is how to handle the
actions
entered into a log table. I have a table for employee's info, and log
table
that i am using to keep track of actions performed on employees, i.e. new
hire, raise, termination, etc. It seems appropriate to have the log table
include a FK for an actions table with all of the possible actions.

in the design view of the log table, i can see that the action field is
set
as a combo box with a row source of the actions table PK, yet I don't see
a
relationship in the relationships view. Is this a problem? I tried
dragging
the field over to establish the relationship, but the field properties
don't
match. (log table = text) Changing the data type to number wants to delete
all of the entries. So it seems like I should do an update query to change
the text to the code of the FK from the actions table to create the
relationship.

Bottom line, why is there no relationship, and does it matter long term?

thanks in advance.



  #3  
Old April 15th, 2010, 09:13 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Lookup field and Relationships

i can see that the action field is set as a combo box with a row source of
the actions table PK, yet I don't see a relationship in the relationships
view. Is this a problem?
Yes. You are attempting to use a lookup field in the table -- do not do
that.
tblEmployee --
EmpID - Primary key
FName
LName
Gender
DOB
etc

tblActions --
ActionID - Primary key
Type -

tblEmpActions --
EmpActionID - Primary key
EmpID - foreign key
ActionID - foreign key
ActionDate -
InitiatedBy -
EndDate -
etc

Set a one-to-many relationship between the tblEmployee & tblEmpActions and
tblActions & tblEmpActions selecting Referential Integerity and Cascade
Update.
Use a form/subform with Master/Child links set on the EmpID. Use a combo to
select action Type and the ActionID bound to the tblEmpActions table.

--
Build a little, test a little.


"jrav" wrote:

I am having trouble understanding a design concept. I am setting up an
employee database for the first time. At issue is how to handle the actions
entered into a log table. I have a table for employee's info, and log table
that i am using to keep track of actions performed on employees, i.e. new
hire, raise, termination, etc. It seems appropriate to have the log table
include a FK for an actions table with all of the possible actions.

in the design view of the log table, i can see that the action field is set
as a combo box with a row source of the actions table PK, yet I don't see a
relationship in the relationships view. Is this a problem? I tried dragging
the field over to establish the relationship, but the field properties don't
match. (log table = text) Changing the data type to number wants to delete
all of the entries. So it seems like I should do an update query to change
the text to the code of the FK from the actions table to create the
relationship.

Bottom line, why is there no relationship, and does it matter long term?

thanks in advance.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.