Correct, I don't care about job history.
Correct, I don't care about job history.
Nice of Mike to provide you a db but it does not quite represent my suggestion. I think it has 1 table more than needed based on your stated requirements.
Assuming each job can associate with only 1 org unit, it is not necessary to have both tblOrgJobs and tblJobs.
tblEmployees
tblOrganization
EmployeeIDpk EmployeeFName EmployeeLName 1
Jones 2
Albert 3
Herman 4
Davids 5
Bernard 6
Smith
tblJobs
OrgID OrgName Org_Level HigherLevelOrganization_ID 1 Office of the CEO Principal 0 2 Office of the CFO Principal 1 3 Office of the COO Principal 1 4 VP Office Finance Directorate 2 5 VP Office Human Resources Directorate 1 6 VP Office Manufacturing Directorate 3
JobPositionNumberJobTitle OrgIDfk EmpIDfk 781246 Assistant 1 1
Last edited by June7; 07-19-2019 at 07:31 PM.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Mike's db format works great for me. Thank you for the additional comments.
Glad you have something working but why maintain another table if it isn't needed?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
mike60smart: Again, thank you for providing the sample db. It works like a charm.
Quick follow up question... I added a field "LastModified" to the junctiontable. When the main form is updated, the current record receives the most current date. In the subform (where I'm adding employees), I may have several employees... each time one is added or deleted, I want it to be showing date time.
I'm currently using query statement: SELECT Max(T00_JunctionTable.Record_Modified_Date) AS MaxOfRecord_Modified_Date
FROM T00_JunctionTable
GROUP BY T00_JunctionTable.OrganizationIDfk
HAVING (((T00_JunctionTable.OrganizationIDfk)=1));
... but here, the OrgIDfk is currently hard-coded. I need it to be equal to the OrgId of the mainform.
How do I pass the modified date from the junction table (capturing new/deleted employees) to the mainform (where I rarely make changes)? Thank you,
EEH
Hi EEH
Here with your Db Modified.
I added the DateModified field to tblOrgJobs and also to the Subform
I added an AfterUpdate to the Combobox's for selecting Job & Employee ie Me.DateModified = Date
See if this suits
Mike... this works beautifully! Thousand thanks again.
Cheers,
EEH