Page 3 of 3 FirstFirst 123
Results 31 to 37 of 37
  1. #31
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Correct, I don't care about job history.

  2. #32
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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
    EmployeeIDpk EmployeeFName EmployeeLName
    1
    Jones
    2
    Albert
    3
    Herman
    4
    Davids
    5
    Bernard
    6
    Smith
    tblOrganization
    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
    tblJobs
    JobPositionNumber
    JobTitle 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.

  3. #33
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Mike's db format works great for me. Thank you for the additional comments.

  4. #34
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  5. #35
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    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

  6. #36
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    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
    Attached Files Attached Files

  7. #37
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Mike... this works beautifully! Thousand thanks again.

    Cheers,
    EEH

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 14
    Last Post: 08-29-2019, 04:52 PM
  2. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums