Results 1 to 13 of 13
  1. #1
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7

    Design help


    I'm stuck! This should be a common situation, but not straightforward. Tables would seem to be People, agencies, facilities, roles. Of course there's people's cell phones and emails, and agency and facility phones addresses and emails. An agency can have more than one facility. A person can be a director of more than one agency, a director of one and a supervisor of another of it's facilities, a director and a supervisor of all or some of it's facilities, or just a supervisor of a facility. So I have the table data I arranged in access from an Excel spreadsheet. I know I link the PK from agencies to a foreign key on facilities. But then what's the junction table or tables, and how do I get roles in there? And then, how do I populate and set up the queries or tables so I can see a person and the contact info and agency and facility their associated with, or an agency and the director, facilities and their supervisors? Thanks for the help!

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In 4-5 lines, what is the purpose of this proposed database? Who will use it and for what specifically(reports, queries??)? How many users at the same time?

  3. #3
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7

    Purpose and use

    One user, purpose is to easily update information, and to track and take notes of contact interactions over time. Used to make logistical and strategic decisions based on contact exchanges.
    Last edited by Qwild1; 02-28-2018 at 09:32 PM. Reason: Change

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You could look at the m$oft Contact template as a starting point.
    There are other templates, or you could identify your requirements and build your own database.

    Here is a link to database design info that may be useful.
    Good luck.

  5. #5
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7

    Seeking more specific design help

    Quote Originally Posted by Qwild1 View Post
    One user, purpose is to easily update information, and to track and take notes of contact interactions over time. Used to make logistical and strategic decisions based on contact exchanges.
    Thanks for the links orange, but I'm finding that beyond basic relationship types, I can't find an example that is similar to my case, with all of the many to many relationships that seem to be involved. Could anyone suggest how I should link my tables?

    This is for tracking and updating contact, interaction, and location info as well as frequently changing job titles to either an AgencyRep or a FacilityManager. An Agency may have one or more Facilities, but a Facility can only have one Agency. A Facility may have the same or a different address than its Agency.

    But a person can belong to one or more Agencies. A person can be an AgencyRep for Agency#1 and an Agency Rep AND a FacilityManager for Agency#2 at one of Agency#2's Facilities. A person can be a Facility Manager at an Agency's Facility and not an AgencyRep.

    I think I should do a one to many relationship between AgencyID and FacilityID. But which tables should I join? Let's say its People and Facilities. Then, in order to get people's titles right, how should I link titles? Add a lookup field to the People-Facilities junction table?

    Thanks for the help!
    Quinn

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I would stay way from lookup fields in tables.
    What is Location?
    Person =Contact, right?
    And Contact is always associated with a Facility and/or FacilityAgency
    A Contact can hold title FacilityManager and/or AgencyRep

    You want the current/latest info.
    You do not need to record the history of Contact (eg X was FacilityManager of BBB from date1 to Date2)

  7. #7
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7
    Yes. A facility can have an address that's the same or different than it's agency. This is a statewide group home project.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You said there was only 1 user. That doesn't seem to match a "state wide group home project".
    Do you have samples of the data you want to report/extract from this database?

  9. #9
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7
    Yes, just me using it. A sample would be an agency and its 3 facilities. One facility is at the same address as the agency. The agency rep is also the facility manager at one of the facilities. The other 2 facilities have their own facility manager. I might extract email addresses of just facility managers, or of those who are both. Or I could extract the facilities that are in certain counties. Does that help? Thanks, by the way!

    I had a thought that since an agency can only have one agency rep, and a facility can only have one manager, part of the design solution I'm looking for could involve adding a fk to the agency and facility tables that links to people?

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can an Agency exist without a Facility?
    Perhaps 3 tables -
    Person that contains all Contacts and is used as a reference table.
    Agency that contains all Agency attributes including AgencyRep and AgencyRepTiltle
    Facility that contains all Facility attributes including FacilityMgr and FacilityMgrTitle

    Agency to Facility is 1 to Many (AgencyId is a FK in Facility)
    Person is used in joins to populate AgencyRep, and a second copy of
    Person is used to populate FacilityMgr.
    AgencyRep contains the PersonId of Person who is current AgencyRep
    FacilityMgr contains the PersonId of Person who is current FacilityMgr

    A Person could be AgencyRep and FacilityMgr.

    You would use Forms to populate Person, Agency and Facility.
    You would use queries to identify things such as:
    -Contacts who are AgencyRep and also FacilityMgr
    -Contacts who are AgencyRep
    -Agency or Facility by Location

    Just some things to consider.
    Good luck.

  11. #11
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7
    Thanks orange I think I can make this work!

  12. #12
    Qwild1 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    7
    Tried what you recommended Orange, and I think I'm close. the query isn't returning all records, and the forms aren't returning all facilities for each agency. Can you recommend design tweaks I'm missing?Click image for larger version. 

Name:	rel.jpg 
Views:	12 
Size:	85.3 KB 
ID:	32885Click image for larger version. 

Name:	queryattempt.jpg 
Views:	12 
Size:	126.1 KB 
ID:	32886Click image for larger version. 

Name:	formattempt.jpg 
Views:	12 
Size:	209.2 KB 
ID:	32887

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The PERSON table is a reference table. It supplies values for AgencyContact and for FacilityContact.

    Try using 2 copies of Person (Person and Person_1). It's the same list of Persons. It just identifies which Person serves in which position for which organization.

    Good luck.

    Click image for larger version. 

Name:	AgencyFacility.jpg 
Views:	11 
Size:	74.8 KB 
ID:	32888
    Last edited by orange; 03-05-2018 at 07:50 AM.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need help with design
    By Steelersfan26 in forum Database Design
    Replies: 1
    Last Post: 08-14-2017, 05:32 PM
  2. Design vs Article (=Design+Materials) problem
    By emihir0 in forum Database Design
    Replies: 14
    Last Post: 04-20-2015, 03:03 PM
  3. Help with DB design
    By pbowler in forum Access
    Replies: 3
    Last Post: 01-24-2013, 03:13 PM
  4. Table Design -- want to avoid a design that creates blank fields
    By snowboarder234 in forum Database Design
    Replies: 4
    Last Post: 10-08-2012, 10:13 AM
  5. Design Help
    By jbevans in forum Database Design
    Replies: 3
    Last Post: 11-29-2011, 08:01 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