Table relationship many to one showing all records. (1 Viewer)

[Kill_Switch]

Correct path I'm going?

 

[jdraw]

You will want to Enforce Referential Integrity on your relationships.
In Relationship window, right click on Relationship line, select Enforce Referential Integrity.

For consistency, you could adjust your ID field names to be more explicit as you have with Area_ID.

eg MemberID or EmployeeID
You could drop the ID on tbl_Employed_Area and use both EMPLOYEE_ID and AREA_ID as compound PrimaryKey.

 

[Kill_Switch]

You will want to Enforce Referential Integrity on your relationships.
In Relationship window, right click on Relationship line, select Enforce Referential Integrity.

For consistency, you could adjust your ID field names to be more explicit as you have with Area_ID.

eg MemberID or EmployeeID
You could drop the ID on tbl_Employed_Area and use both EMPLOYEE_ID and AREA_ID as compound PrimaryKey.

Thank you. I will give this a go when I get back. Much appreciated.

 

[GaP42]

Duration is a calculated value and should be dropped - you have the start and end dates to calculate this value in any query where needed for reports or forms.

 

[Pat Hartman]

1. You do yourself and other developers no favors when you name every single PK of your schema "ID". I have no idea who ever thought that was "cool". It isn't. Most people use the same name for the PK and FK wherever possible. Some prefer to suffix the FK with "_FK". This allows you to look at a table and have a good shot at figuring out what the relationships are. You don't have to open the relationships window to see which "ID" this FK points to.
2. When you create relationships, you need to check the enforce RI checkbox.
3. Creating default joins in your relationship window isn't necessary. I don't waste my time. If you use the QBE to build queries, Access will "sometimes" create joins based on the default you defined in the Relationship window. If you don't specify a join type, the QBE will assume an inner join which is correct most of the time.
4. Do not use all upper case. It is like shouting and makes queries harder to read.

Correct path I'm going?

Your naming is inconsistent. Consistency is your friend. Get in the habit of doing things the same way all of the time. Use your naming standard consistently.

I know some people like to use the underscore to separate the parts of a name. I prefer CamelCase. It is shorter and you don't have to ever use the shift key to type names EXCEPT when you define them. If your definition is EmpID, then when you type empid, Access will fix the case. I find it useful as a sanity check. If Access doesn't fix my case, I probably have a typo.

 

[Kill_Switch]

1. You do yourself and other developers no favors when you name every single PK of your schema "ID". I have no idea who ever thought that was "cool". It isn't. Most people use the same name for the PK and FK wherever possible. Some prefer to suffix the FK with "_FK". This allows you to look at a table and have a good shot at figuring out what the relationships are. You don't have to open the relationships window to see which "ID" this FK points to.
2. When you create relationships, you need to check the enforce RI checkbox.
3. Creating default joins in your relationship window isn't necessary. I don't waste my time. If you use the QBE to build queries, Access will "sometimes" create joins based on the default you defined in the Relationship window. If you don't specify a join type, the QBE will assume an inner join which is correct most of the time.
4. Do not use all upper case. It is like shouting and makes queries harder to read.

Your naming is inconsistent. Consistency is your friend. Get in the habit of doing things the same way all of the time. Use your naming standard consistently.

I know some people like to use the underscore to separate the parts of a name. I prefer CamelCase. It is shorter and you don't have to ever use the shift key to type names EXCEPT when you define them. If your definition is EmpID, then when you type empid, Access will fix the case. I find it useful as a sanity check. If Access doesn't fix my case, I probably have a typo.

Thank you for taking the time to respond. I did in an effort to make it sane, renamed the tables, queries, and forms with suffices to make it easier. I've also redid a bunch of fields for the same reasons. Wish I had a copy of the before. Fields named like Exp1, Exp2, Exp18.

Should also mention, as a tiny excuse, inherited this DB. I've added alot to it and redid the UX. It's alot easier to input/ navigate.

Sorry, got off track.

Small attempt was made by using APP_ID to ease the matching, and I take the advice to heart and will move forward with your advice. I must admit, EmpID does look alot cleaner.