Results 1 to 10 of 10
  1. #1
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151

    Problem with continuous form, possible problem with underlying query


    The basic structure of this data:

    1. Congregations
    2. Congregations are made up of teams
    3. Teams are made up of individuals with different roles, statuses, begin and end dates


    I want to create a continuous form for adding and updating team members, where the user would select the congregation and the team in the header.
    In the continuous section, would be a list of the members with their associated roles, statuses, etc. I want to be able to add and update the team members.

    I'm not sure if the joins in the query are right. Certainly something isn't. Your help would be greatly appreciated.

    Zipped database attached.CAJM Network Database.zip

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Trying to understand your structure.

    Do teams have a name?
    Typically, how many teams make up a congregation?
    Can an Individual belong to more than one team?

    When you say "Congregations are made up of teams", does this really mean "Teams are groups of Individuals drawn from a Congregation"
    Last edited by davegri; 06-16-2022 at 09:34 PM. Reason: more clarif

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You can use the table, or a query.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As davegri says - you need to clarify what your structure is as it doesn't answer these questions:

    1. can individuals 'exist' who are not part of a team or a congregation?
    2. If not, what comes first? they become a member of a congregation and can then be assigned to a team? or just assigned to a team?
    3. can individuals be in more than one team? either at the same time or consecutively?
    4. can individuals be in more than one congregation? either at the same time or consecutively?
    5. can individuals have more than one role in a team? either at the same time or consecutively?
    6. Do individuals have a specific role and/or status which is used to assign them to a team? Or are they just individuals who are assigned a role/status when they are assigned to a team?
    7. what do the begin and end dates mean? their time in the congregation? the team? in a role? as an individual?

    I've taken a quick look at the relationships you provided and the structure does not look right - but depends on the answers to the above as to what to change, but almost certainly you don't have enough tables

    edit: one other question - can a team be assigned to more than one congregation?

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

    Your Relationships are not set correctly between "Teams" & "Individuals"

    See the attached screenshot of your Relationship Diagram.

    The Join between these tables indicates "Indeterminate" when it should be a "1 to Many"

    What fields should be in your Teams Table? What is the makeup of a Team?
    Attached Thumbnails Attached Thumbnails Relationships.JPG  
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Maybe I was trying to set up the team ID/congregation ID in a way that isn't possible in Access. My thinking was like this: congregation 123 would have teams 1, 2, and 3, with a composite primary key made up of the church ID and team ID. Then church 234 would also have teams 1 and 2, with another composite primary key. Is that possible in Access?

  7. #7
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Quote Originally Posted by davegri View Post
    Trying to understand your structure.

    Do teams have a name?
    Typically, how many teams make up a congregation?
    Can an Individual belong to more than one team?

    When you say "Congregations are made up of teams", does this really mean "Teams are groups of Individuals drawn from a Congregation"
    Yes. teams are individuals drawn from a congregation. Teams don't have a name, I think my problem may be because I was trying to create a composite primary key made up of the congregation ID and team Id. So, for example, congregation 123 could have teams 1, 2, and 3 (primary keys 1231,1232, and 1233). And congregation 125 could have teams 1 and 2 (primary keys 1251,1252).

  8. #8
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Quote Originally Posted by CJ_London View Post
    As davegri says - you need to clarify what your structure is as it doesn't answer these questions:

    1. can individuals 'exist' who are not part of a team or a congregation? yes
    2. If not, what comes first? they become a member of a congregation and can then be assigned to a team? or just assigned to a team? The individual is created, then associated with a congregation.
    3. can individuals be in more than one team? either at the same time or consecutively? As far as I know, and individual is only on one team at a time. There is a team role and begin and end dates.
    4. can individuals be in more than one congregation? either at the same time or consecutively? Not as far as I know.
    5. can individuals have more than one role in a team? either at the same time or consecutively? At the same time, no. Consecutively, yes. That is based on begin and end dates.
    6. Do individuals have a specific role and/or status which is used to assign them to a team? Or are they just individuals who are assigned a role/status when they are assigned to a team? My understanding is that they are assigned a role and status when they are assigned to a team.
    7. what do the begin and end dates mean? their time in the congregation? the team? in a role? as an individual? The begin/end dates are for the role in a team. For example from 1/1/2020 - 9/30/2022, someone could be in the role of "network member" then from 10/1/2022 - present (null end date) be in the role of team leader.


    I've taken a quick look at the relationships you provided and the structure does not look right - but depends on the answers to the above as to what to change, but almost certainly you don't have enough tables

    edit: one other question - can a team be assigned to more than one congregation?
    NO

    This an interfaith cross-denominational ministry. Members of multiple churches are involved.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Slight conflict - you say teams are individuals drawn from a congregation

    And then you say individuals may not be part of a congregation

  10. #10
    msmithtlh is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    151
    Quote Originally Posted by CJ_London View Post
    Slight conflict - you say teams are individuals drawn from a congregation

    And then you say individuals may not be part of a congregation
    Teams are drawn from a congregation, but not everyone in a congregation is necessarily on a team. We will probably have to set up a "dummy" congregation for individuals who aren't in a regular congregation.

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

Similar Threads

  1. Replies: 3
    Last Post: 12-22-2021, 08:38 PM
  2. Problem with continuous form
    By ezybusy in forum Forms
    Replies: 3
    Last Post: 03-30-2019, 04:29 AM
  3. Continuous Form With Checkbox VB Problem
    By WhiskyLima in forum Access
    Replies: 4
    Last Post: 11-25-2013, 08:25 AM
  4. Yet another continuous form problem...
    By DarrenUD in forum Forms
    Replies: 3
    Last Post: 04-02-2013, 11:33 AM
  5. Continuous Form Totals Problem
    By burrina in forum Forms
    Replies: 2
    Last Post: 02-22-2013, 02:35 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