Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 37
  1. #16
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by skydivetom View Post
    June7 - so, you are referring to a cascading 3-tiered relationship? OrgIDfk to JobIDpk; JobIDfk to EmpIDpk? Basically, similar to my original 3-tiered OrgDesign (Principals to Directorate to Branches, etc.)
    Employees table is main/central one!

    Registries (a single row for entity!):
    tblOrgnizaions: OrgID, ... ; /OrgID is PK, you must have an organization with name e.g. 'Empty'
    tblJobs: JobID, ... ; /JobID is PK, you must have a job with name e.g. 'Empty'


    tblEmployes: EmplID, ... ; /EmplID is PK

    Linking tables:
    tblEmplOrg: EOID, FKEmplD, FKOrgID, ValidFrom; /EOID is PK, an unique index composited from FKEmplD, and ValidFrom must be defined (only one organization in day may be applied for employee)
    tblEmplJob: EJID, FKEmplD, FKJobID, ValidFrom; /EJID is PK, an unique index composited from FKEmplD, and ValidFrom must be defined (only one job in day may be applied for employee)
    When employee is leaving, then in tblEmplOrg and blEmplJob are added rows with FKJobID and FKOrgID for entry 'Empty' used.

    When you need info about how much employees or jobs are allowed/available for organization, then you need a separate tables:
    tblOrgJobPositions: OJPID, FKOrgID, FKJobID, JobPos;
    tblOrgEmplPositions: OEPID, FKOrgID, EmplPos.

  2. #17
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    June7's suggested layout is along these lines.
    Attached Thumbnails Attached Thumbnails er.JPG  

  3. #18
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    What is the ultimate purpose of this proposed database? What is the scope?
    Work assignments; Project status reporting; HR, or HR linked to Finance....?

    Here are some related models that may help put your overall goal into some form of model. Better to get a holistic picture of what you're working with than to approach a project in a piecemeal, incremental fashion ("stumble upon").
    With a big picture model you can use test data, test scenarios and vet the model; adjust as needed, retest ("stump the model") to get a blueprint for your database structure.

    Hierarchical structure:
    Click image for larger version. 

Name:	HierarchyModelDatabaseAnswers.gif 
Views:	20 
Size:	15.8 KB 
ID:	39137

    A Business Architecture model:
    Click image for larger version. 

Name:	BusinessArchDataModel.jpg 
Views:	20 
Size:	11.5 KB 
ID:	39138

    Another structure Organization and Team Projects (from google):
    Click image for larger version. 

Name:	Businessmodel_ASANA.png 
Views:	21 
Size:	5.8 KB 
ID:	39139


    Organization, Roles and Assignment

    Click image for larger version. 

Name:	OrgRolesAndAssignment.PNG 
Views:	20 
Size:	151.9 KB 
ID:	39140

    Employee in Organization:
    Click image for larger version. 

Name:	EmployeeInOrganization_Orig.png 
Views:	20 
Size:	196.8 KB 
ID:	39141

    Good luck with your project.

  4. #19
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Response

    ArviLaanemets:

    Thank you for chiming in... although this may be a straight-forward approach, I'm not 100% clear how to interpret/link your provided recommendations. Is there any way you could utilize the attached, conceptual db (records are dummy ones) and add the linkages between the tables as you suggested. This would go a long way vs. my having to ask multiple follow-up questions. Again, the records can be deleted/modified/added... I only used them as a place holder for testing purposes.

    I appreciate your assistance in advance.
    Attached Files Attached Files

  5. #20
    Join Date
    Apr 2017
    Posts
    1,673
    It is almost weekend for me now, and I don't have access to Access until Monday And then I will be very busy for couple of days before taking a couple of weeks of my annual leave, so I don't promise I can make something on Monday either.

    Generally I don't bother defining links at table level. I feel the only useful feature they have is referential integrity, and I can get same results (and with more finery) using BeforeWhatever Events at form level.

  6. #21
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Response

    mark60smart:

    Thank you for the feedback. W/ the exception of a few fields, I attempted to replicate the ERD you illustrated (ref "June7). Please see the relationship diagram in the attached DB.

    The changes are as follows:
    - As I have all org levels (division, branches, etc.) in one and the same table (i.e., tblOrganization), I did not include table "tblOrgLevels). Thus the field "OrgLevelID" is not included in tblOrganization.
    - tblOrganization has an additional field "HigherLevelOrganization_ID" which cross-references the org level's next higher tier/entity.
    - tblEmployee includes the field "ReportToEmployeeIDpk"... again, it cross-references the employee supervisor's autoID.

    Assuming you agree w/ the setup, would you please "walk me" through the concept? So, basically, we're using a junction table (tblOrgJobs) between organization and jobs. Doesn't this mean that the organization can have a) multiple and b) a job can have multiple organizations? Not clear on the latter? Also, why is this a right (or left) join between tblOrgJobs and tblJobs? Not tracking on that part.

    Lastly, given that employee are also connected to the OrgJobs junction table, what's the meaning behind that? Our employees only have one primary function. Even if they were on a "loaner" by another department, their job code remains unchanged... until a temporary position would become permanent, but then the Job ID would change. So, again, an employee can have only one (official role).

    All that said, I need to get further clarification on this process. I appreciate any additional feedback you can offer.

    Thank you,
    EEH
    Attached Files Attached Files

  7. #22
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    I totally understand... I responded to mark60smart and posted an updated database to replicate what he suggested. Not sure if that goes against your recommendations.

    Any additional help (if your schedule permits on Monday) would be greatly appreciated. Until then. Thanks and have a great weekend.

  8. #23
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by ArviLaanemets View Post
    Generally I don't bother defining links at table level. I feel the only useful feature they have is referential integrity, and I can get same results (and with more finery) using BeforeWhatever Events at form level.
    Relationships are not the same as query links
    The only purpose of table relationships is to impose referential integrity but that is a significant reason for creating relationships.
    If interested, see my extended article on Relationships and Referential Integrity http://www.mendipdatasystems.co.uk/r...ps1/4594533224
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #24
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not sure tblJobs is needed. Depends if a job can associate with more than one org unit. Don't confuse job positions with job descriptions. A job position (chair) can exist in only one org unit, whereas a job description could be associated with multiple job positions. Clerk-typist could be same description for multiple positions in same or multiple org units.
    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.

  10. #25
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    All -- again, I want to thank everyone for providing feedback on this question. My challenge is that most (if not all) contributors have a different take on solving this and, sometimes, throw in additional scenarios/reqs that I never stated in the original post (e.g., different org levels such as branches or divisions). I uploaded several times a database example and kindly requested experts tweaking it where necessary. Unfortunately, these requests have gone unanswered thus far. So, I'm still struggling w/ interpreting the various views on how to best set up the tables.

    So, allow me to recap on the process that I'm trying to capture:
    1. The organization has multiple offices (CEO office, CFO office, HR, Budgeting, etc., etc.).
    2. Each office has 1 or more positions to fill (e.g. CEO's office has CEO itself , Administrator for CEO, Secretary for CEO, etc.).
    3. While most jobs are filled, some jobs may be vacant (until employee is eventually hired).
    4. Once an employee joins the organization, he/she has "one" and only one role. There are **no** dual jobs (working in personnel AND working finance). Instead, employee works either in HR or works in finance. If HR employee temporarily works in finance, their job is still listed as "HR". If change becomes permanent, employee's job ID would change, too.

    That's really it... if someone could open my db and modify the relationships and then provide an updated Access db would be ideal. Until then, I'm not making much progress here.

    Again, I appreciate everyone's feedback but I merely want to determine which setup of relationship would match those mentioned in 1-4.

    Thank you,
    EEH

  11. #26
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi EEH

    I have created the Data Input Forms based on your ER Diagram

    Test to see if it fits your requirements.
    Attached Files Attached Files

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. you said you don't care about job history

    2. assume not concerned about job description used for multiple positions

    Based on those 2 points I suggest a simple structure with 3 tables which I have already described. If you want to take design beyond that per other suggestions that is your prerogative. Note that all suggestions have these base tables in common.

    tblOrgUnits

    tblEmployees

    tblJobs
    JobTitle
    JobPositionNumber (this can be autonumber or something you construct and save in text field or not used at all but every org I worked for has assigned position numbers)
    OrgUnitIDfk
    EmpIDfk

    Oh, and tblLevels for lookup source would be fourth table.
    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.

  13. #28
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    A typical approach is to identify the "business" and the "things/entities/subjects" involved in that business. Through analysis of requirements and some "what iffing" with those requesting the project, create a data model with some test data and test scenarios and try the "stump the model". Include a few errors to get those participating in the "stump the model" to clarify (*and point out what you "missed"). Gets people on board when they correct the model.
    Start the model at the 30,000 ft level and add details as determined through analysis and clarification.

    However, I did post several models of different aspects of Org/Employee to assist or consider.

    Does your set up envisage Teams, such as Project Team? Do you break Projects into Tasks?
    Are you using job, position, role and function somewhat interchangeably? If not, can you clarify the differences?

    Which database are you asking readers to review/critique?

    ERD zip in post 19
    ERD modifiedzip in post 21

    What exactly is the ultimate usage of this proposed database? Just trying to understand the scope of this.

  14. #29
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    mike60smart:

    That is simply perfect!!!! I removed all ID in the junction table and entered some data. At first glance, I believe everything works at anticipated. I will continue to test and follow up if necessary.

    In the meanwhile, please allow me to ask a follow-up question:
    - What is the meaning of the arrow-pointed joint (left join)? Show all orgs with either a) no jobs associated and/or b) no employees associated with job?

    Again, THOUSAND THANKS FOR POSTING THE DATABASE WITH SAMPLE!!! I very much appreciate it!!

    EEH

  15. #30
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    mike60smart:

    That is simply perfect!!!! I removed all ID in the junction table and entered some data. At first glance, I believe everything works at anticipated. I will continue to test and follow up if necessary.

    In the meanwhile, please allow me to ask a follow-up question:
    - What is the meaning of the arrow-pointed joint (left join)? Show all orgs with either a) no jobs associated and/or b) no employees associated with job?

    Again, THOUSAND THANKS FOR POSTING THE DATABASE WITH SAMPLE!!! I very much appreciate it!!

    EEH

Page 2 of 3 FirstFirst 123 LastLast
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