Lookup field and Relationships
in [Tables]
|
Prev: Auto Populated a Table From SQL View When New Record Shows in View
Next: how to get rid of "Add new field"
From: jrav on 15 Apr 2010 13:44 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.
From: Jeff Boyce on 15 Apr 2010 15:34 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" <jrav(a)discussions.microsoft.com> wrote in message news:06E3424D-6559-4DD7-BC5F-EEF7BE6706E3(a)microsoft.com... >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.
From: KARL DEWEY on 15 Apr 2010 16:13
>>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. |