Help in direction, table update from subform (1 Viewer)

[lightray]

Hi, this is more of a planning problem. I'm stuck on a method of attack!.
I have a multiple tab form, with subforms on most tabs. The main form populates from a multiple set of querys, rendering the main form un-update-able. Most the data relates to an Employee.
The subform in question records Job Positions, for which the DivisionID is an indexed key here and in the Employee record.
New job position are entered on the subform; previous one is end dated and new record has no end date. (a basic continious form)
I want to be able to update the Employee table with the latest DivisionID, and really not sure on a road to take. Have thought about an SQL update using the DoCmd RunSQL, but it looks cumbersome.
Any thoughts on a best way through this?

 

[MarkK]

My observation is that DivisionID should not be stored in the Employee table. It's my understanding that a DivisionID for an employee depends on the JobPosition. If so, I would query the JobPositions table for the Null enddated position for the employee in question.

SELECT divID FROM tJobPositions 
WHERE eeID = [I]<id of employee>[/I]
AND IsNull(EndDate)

Storing the same data in more than once place is a tremendous maintenance liability. It's dangerous like running with scissors.
Well, maybe not that bad, but it's an unfindable mistake waiting to happen.
You might display the Division (and its ID) on the JobPostion subform for the employee.

 

[lightray]

Hi Lagbolt, thanks for your thoughts.
There may be mutiple Job Positions per employee. I run most reports by Division and many have nothing to do with Job Positions, while one reports all Job Positions. I thought it would be better to have a tblEmployee.DivisionID If I removed it, my reporting wouldn't work would it?

 

[MarkK]

Your call. But I'll restate that if you maintain a history of changes in a linked table, storing a copy of the lastest change in the parent table is poor design. You then compound the risk of error if you leverage reports off the copy of the data.
But as per your original request, to update data in any table you can execute an SQL update query using sytax like...

CurrentDb.Execute _
  "UPDATE tEmployee " & _
  "SET divID = " & [I]<divID variable here>[/I] & " " & _
  "WHERE eeID = " & [I]<employeeID variable here>[/I]

I commonly write a query like that in the query design grid and copy the SQL generated in that view to code.
Cheers,

 

[lightray]

I'm happy to defer to a higher knowledge as long as I understand all the reasoning. I thought I was on the right track with my way of doing things?? I have recently removed a similar link from the Employee table, so I guess my design is flawed. I will look at reworking it. Thanks for the insight.

 

[MarkK]

1) Imagine you have a report that allocates an employee's payroll to a certain division in your company by consuming the most recent divID in the employee record.
2) Imagine an employee has a certain job position all year until Dec 29.
3) On Dec 30 the employee's position changes, which necessitates a change to that employee's divID.
4) You run your payroll report which allocates the employee's entire year's payroll to the division she only worked in for one day.

That's one risk of storing your most recent dated child item with the parent.
Another risk is that future developers, including you, will forget or have no idea that they must also update the divID in the Employee record when changing JobPositions. It represents an undocumented, unexpected dependency that must constantly be maintained, and whose accuracy is fragile at best.

Hope this fleshes out the reasoning a little better for you and perhaps other readers. Any point of data should have a table to which it distinctly belongs, and any consumer of that data should query it from its source.

Cheers,

 

[lightray]

Regarding your example I don't think you would use the DivID from the employees record anyway, you'd want to use the Job History table. However I do see your point. It's annoying to have so many extra tables involved now in the redesign. Them's the breaks....