Duplicate Values for one Field

[Canadiangal]

I have a table called Department. One employee may belong to more than one department. So I created three tables, Emptable, Depttable, and DeptDatatable. The Emp table and The dept table both have a primary key. Emp# in Emptable to Emp# in Dept Data Table. Dept# in Depart Table Primary key to foreign key in Dept# key in Dept Data Table. In other words:



Employee One to Many Department Data both using Emp#
Department One to Many to Department Data both using Department#

I have created a form for data entry for the young lady that enters the data. Right now, there are two entries in the department data for any employee that belongs to more than one department. My question is how do I create a form that allow the person that is entering data to only enter the employee in once and enter multiple values into the department field. How would it create then two entries into the Dept Data Table? I have been reading up about fields that allow duplicate data but this treats the fields like memo fields. I need to draw queries from Departments, so not sure if that would work. I also need the Department to be a list that they choose from.

Thank you again for all your help!

[June7]

This is a Many-to-Many relationship which requires junction table - your DeptDataTable. Set the fk fields as compound primary key and this will prevent duplication of employee/department pairs.

Options for helping the data entry:
1. form/subform http://office.microsoft.com/en-us/ac...010098674.aspx
2. use VBA code in BeforeUpdate event to set the DefaultValue property of the employee combobox, like
me!Control.DefaultValue = """" & me!Control.Value & cQuote & """
http://access.mvps.org/access/forms/frm0012.htm

The list you want can be a combobox http://datapigtechnologies.com/flash...combobox1.html

[Canadiangal]

June I set the foreign keys as you suggested. I would like the data entry person to be able to select a new department for the employee from the dropdown menu. After that I would like the department ID to auto fill in. Can you direct me here? I am assuming it is a lookup from within the query.

[June7]

Sounds like you just need a basic combobox for the department value. Reference the last link in my previous post.

[Canadiangal]

Thanks once again June!