Auto Populate Field

[Guest]

I have a table that has the following fields:

ID (Primary Key) & (Autonumber field)
SOPID (This field lists my document numbers)
SOPTitle (Self explanatory)
CategoryID (There can be mutiple categories for each SOP)

This table is used to enter the SOPs and their categories. The table is
linked to other tables, therefore the SOPID, SOPTitle and CategoryID have a
Lookuo in the row source. Is there any way of setting entry to this table so
when I enter the SOPID, the corresponding SOPTitle populates the field?
Thank you

 

[Guest]

Souds like your tables are not normalized. You realy need three tables here:

1st table
SOPID (Primary Key)
SOPTitle (Self explanatory)

Second Table
CatID (Autonumber & PK)
CategoryID (There can be mutiple categories for each SOP)

Third Table
CatID
SOPID (Make a combined PK so that a catagory can be assigned to a SOP just
once)

Make query with these tables to base a form off of. It is not real good to
use the lookup field in table. Tables store data, it is better to use forms
and reports to edit and view the data. Please see
http://www.mvps.org/access/tencommandments.htm

 

[Guest]

Thank you for your help - This is my table structure:

tblDepartment
DeptID (PK)
DepartmentName

tblEmployees
EmployeeID (PK)
FirstName
LastName
StartDate
EndDate
DepartmentID

tblSOPS
ID (PK)
SOPID
SOPTitle
CategoryID

tblCategory
Category (PK)

tblDepartmentCategoryJoin
DeptID (PK)
CategoryID (PK)

tblTrainingEvents
TrainingID (PK)
EmployeeID
SOPID
TrainingDate

I have to leave now for the day - I would appreciate any suggestions.
Do you need to know the relationships?

Thank you, Karen

 

[Guest]

I am guessing the link is between CategoryID in tblSOPS and Category (PK) in
tblCategory. This allows you to only put one catagory per SOP, and then you
have to duplicat the SOP information to add another catagory. looks like you
need a many to many relationship to do this. By removing CategoryID in
tblSOPS and making a new table containing CategoryID and SOPID that links to
tblSOPS and tblCategory you can eliminate the duplicate entries. I would
also remove the ID for tblSOPS and make the SOPID the PK because I am
guessing that you do not have more than on SOP with the same SOPID. You may
want to do a search on normalization to help you understand this concept
more, because I do not know how well I have explained this. With the third
table you can then make a nice form that can contain combo boxes and you will
eliminate all of the double entry of data.