One form entry created 50 form entries!

[jk42]

Why is it that Access stumps me every other day!? I had to create a table that would track two layers of employee experience:

This is what I did for my two levels of CRA experience (thanks to the help of another use):

You kind of have to break what you have now and start over--your EmployeeExperience table (which I hope you have) is obsolete. It will be replaced by a new table that links Employees to ExperienceSubCategories. You will have 4 tables to do this:

Employees
EmpId, EmpFirstName, EmpLastName, EmpStartDate
JK42, John, Smith, 5/2/2013

ExperienceCategories
ExpCatID, ExpCatDesc
1, Human Resources
2, Project Management

ExperienceSubCategories
ExpSubCatID, ExpCatID, ExpSubCatDesc
1, 1, Problem Solving
2, 1, Data Entry
3, 2, Industrial Engineering
4, 2, Mechanical Engineering

EmployeeExperience
EmpID, ExpSubCatID
1,1
1,2
1,3
1,4

Those are the minimum fields and tables you need to accomplish this. I used the sample data you provided to display how it will go into the tables. Look it over and if you have any questions let me know.

They are all related, although not all directly:

ExperienceCategories links to ExperienceSubCategories via ExpCatID
ExperienceSubCategories links to EmployeeExperience via ExpSubCatID
Employees links to EmployeeExperience via EmpID

So, I then made a query that had:
From Employee Table: Employee Name, Employee ID, Employee Status
Then from TableEmployeeExperience: FKSubcategory ID (which has the Experience category and subcategory together), and then Experience ID from the experience table.

The problem is that when I put in one employees experience, which had 50 entries... it created 50 entries of that employee, with all 50 experience things every time.

WHat am I doing wrong??! THank you in advance for any help!

 

[Mihail]

Use the Lookup Wizard to create related fields.
See, in the Relationships window if the links are OK. For that, open Relationships, right click on an empty area and choose Show All.

 

[Mihail]

I know only one reason to NOT use the lookup wizard: The field name do not reflect the FK name.
I mange this by uncheck the checkbox that say "Hide key column (recomanded)". This way, the wizard will propose the "good" name for FK field.
On the other hand, to hide the key column is a good recommendation. So, after I obtain the "good" name for field, I check that text box.
I'll say thank you if you show me other reasons.
I don't wish to hijack this thread but also I don't know how to proceed. By starting a new thread where to ask about the lookup wizard or to use a private message to ask YOU ?

 

[jk42]

i wouldn't be mad if it was hijacked. I'd find it an interesting read!

And Pat, Thanks. I"ll go look right now.

 

[jk42]

Actually, sorry, what do you mean by that? You mean in the relationships? I've got employeeID in the employeexperience table, that's connected to the Subexperience table, but NOT the experience table. Is that what you mean?

 

[Mihail]

In the relationships window you must see your database logic. Every table must have "links" to all children.
If you know that a table has a child and you not see the line between that table and the child table, right click the master table and choose Show Direct. That because the line can be hidden. If still that line do not appear that means that the tables are not (or no more) related.
In this case drag the field from master table over the field in related table. A wizard will appear (not the lookup wizard ) and will help you to configure the relation. The same wizard appear if you right click on a line and choose Edit.

To not forget:
After Pat has spoke about a LOT of reasons to NOT use the lookup wizard, I have discovered THIS article.
Personally still I think that the evil... is not as black )

Cheers !