Hey all,

I have been working on an Access database, and tried to follow the 3NF rules and design the database the best way I could but now I am stuck with an error about relatioships, I think. I have an employee table for all my employee information. There are 3 kinds of employees, such as clrek, managers, and staff. Each employee can take many different vacation days. There are different kinds of vacations. There are different vacation hours associated with each vacation and employee type. For example a manager can take a single day for 8 hours and a clrek can take a single day but for 7 hours only. So I have 4 tables:

-------------------------------------------
legend:
table name
+ is for PK
- is for other fields (FK)
-------------------------------------------

employee
+employeeID (number)
-employeeTypeID (number)

employeeType
+employeeTypeID (autonumber)
-employeeTypeName (text)

vacationDate
+vacationID (autonumber)
-employeeID (number)
-vacDate (date)
-vacationTypeID (number)

vacationType
-vacationTypeID (autonumber)
-vacationHours (number)
-employeeTypeID (nubmer)

I'd like to believe that this is a good design in 3NF. You can correct me on that if I'm wrong, but my main problem is with the forms not the tables. In the form where the user is supposed to enter the vacation dates, I have employee table as the source then I have a subform which its source is a query on vacation dates. So far so good, but on the subform, which is a continuous form, I have a control (text or combo box). The problem is that I cannot make new records and I recieve this error:

"The current field must match the join key '?' in the table that serves as the 'one' side of the one-to-many relatioship. Enter a record in the 'one' side table with the desired key value..."

Any response would be appreciated.
Thank you,
Sam

__________________
Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

Sam. It sounds like you do not have your subform related back to your main form correctly. Is the EmployeeID a part of the selection criteria when you were creating the subform on the main form. It should of been. You must have the same relationship's between your subforms as you do in the relationships part of your database or that error will occur. It basicaly is saying, hey you said we were related by all these fields, but I don't have that with my subform set up.

Let me know if this helps.

RU1

Hey all,

I solved the problem. I must say though, Access is one of the worst applications I've worked with. Aside from all the performance issues, the forms are frustrating to work with.

I didn't know a field would not be available if it is not on the form as a control, even though it is part of the source of the form you are using. I solved my problem by adding the controls of some of the fields from my source query. By cotnrols I mean textboxes.

If anyone had the same problem, let me know.
Sam

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW

I just got your message RU1. Thank you for the quick response.

You are absolutely right. I had the EmployeeID related and also I had it linked as a child/master field. But what I didn't tell you was that I also had another field linked: employeeTypeId. However, this field was not in table relations or joins. But I guess since I added the control to the form, it automatically created a relationship for it. Let me know if I'm getting this right.

Sam

Sam Gharnagh
Jr. Programmer Analyst at MOH
Comp Sci at UofW