Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    "Head of Household" seems a rather antiquated concept but topic for another discussion.



    Code behind main form could use DCount() on tblFamilies. Then what follows depends on whether address record has been committed to table or not. Maybe just an Undo command or have to run SQL DELETE action.
    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.

  2. #17
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    Quote Originally Posted by June7 View Post
    Code behind main form could use DCount() on tblFamilies. Then what follows depends on whether address record has been committed to table or not. Maybe just an Undo command or have to run SQL DELETE action.

    I am not yet good with code yet. Can you please help me with it?

  3. #18
    Join Date
    Apr 2017
    Posts
    1,673
    Start with people/persons table.
    tblPersons: PersonID, ForeName, LastName, ... (a field for every separate type of info about person, you want to store).

    As several persons may share same address, you must have some addresses registry.
    tblAddresses: AddressID, ZIP, ... (a separate field for every part of address).

    Now you link any person with his/her current address.
    tblPersons: PersonID, ForeName, LastName, ... , AddressFK.

    When you want to contact a group of persons (a family), then you need a registry of groups/familes.
    tblFamilies: FamilyID, FamilyName, ContactPersonFK, ... (ContactPersonFK is link to tblPersons. At first glance, you may use contact person full name as name of family, but what when you'll have e.g. two John Smith's as contact persons? So my advice is to have separate manually filled field for it instead - this gives you some flexibility.).
    And now you can link every person with his/her current family.
    tblPersons: PersonID, ForeName, LastName, ... , AddressFK, FamilyFK.

    In case your DB contains info about persons attached to several congregations/churches, you need a registry of them too.
    tblChurches: ChurchID, ChurchName, ...
    And you'll need next foreign key in tblPersons.
    tblPersons: PersonID, ForeName, LastName, ... , AddressFK, FamilyFK, ChurchFK.
    In case there may be persons registered in DB, who aren't attached to any church, ChurchFK will be Null. When you have a single church DB, then you can ditch tblChurches at all, and to handle non-members have a field in tblPersons to indicate membership.

    This design allows:
    1. Members of family to be registered at any address (e.g. all family members living at same address, or some members living at different addresses).
    2. Outsiders to share address with some family (e.g. renting a room in family house).
    3. Member(s) of family to leave it and start a new one.
    etc.

  4. #19
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    Quote Originally Posted by ArviLaanemets View Post
    This design allows:
    1. Members of family to be registered at any address (e.g. all family members living at same address, or some members living at different addresses).
    2. Outsiders to share address with some family (e.g. renting a room in family house).
    3. Member(s) of family to leave it and start a new one.
    etc.
    Okay thanks, I understand this and will start fresh in this direction.

  5. #20
    Join Date
    Apr 2017
    Posts
    1,673
    Additional advice.

    Set FamilyName of tblFamilies as unique index - then user can't enter same family name twice accidentally.

    Don't use FamilyName as PK for tblFamilies. All linkings must be done through FamilyID, FamilyName must be used as visible field in combos only. This allows users to edit family names without breaking any links in DB. E.g. you have a family named as "Smiths at River Lane 12". When the family moves, you simply edit the name to e.g. "Smiths at Hill Street 44-2", and it is all you need to update.

  6. #21
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    Quote Originally Posted by ArviLaanemets View Post
    Additional advice.

    Set FamilyName of tblFamilies as unique index - then user can't enter same family name twice accidentally.

    Don't use FamilyName as PK for tblFamilies. All linkings must be done through FamilyID, FamilyName must be used as visible field in combos only. This allows users to edit family names without breaking any links in DB. E.g. you have a family named as "Smiths at River Lane 12". When the family moves, you simply edit the name to e.g. "Smiths at Hill Street 44-2", and it is all you need to update.
    Hello,

    Can you please help look at this and see if I am on the right track? I am still kinda new to Access and learning as I go. One thing I didn't understand is the
    ContactPersonFK
    as to the tables and the relationships.

    I am also not clear about this part with the setup
    E.g. you have a family named as "Smiths at River Lane 12". When the family moves, you simply edit the name to e.g. "Smiths at Hill Street 44-2", and it is all you need to update.
    I like it, just not sure on how it needs to be setup.

    Thanks for the help in advance.
    Clifford86

    TPHAC1.zip

  7. #22
    Join Date
    Apr 2017
    Posts
    1,673
    Don't dublicate info.

    Don't apply default values to table fields. Default values must be used for form controls only!

    In attached example I edited some tables, and added some forms
    Attached Files Attached Files

  8. #23
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    If all possible can I please get some more help? I created a form (screenshot below). The part of the form wrapped in red is to my tblAddress table, the part wrapped in blue is to my tblFamilies table and the part wrapped in Teal is a subform in datasheet view for my tblPersons Table.

    The problem I am having is when adding additional records in the tblPersons subform. The 1st record created in the subform gets a FamiliesFK which is what I want. However any additional records added to the subform does not get the FamiliesFK applied.

    I am also including a sample of the database. The form I am working with is the frmAdd/Edit form. TPHAC2.zip

    Any help to fix this is much appreciated. Thanks

    Sincerely,
    Clifford86

    Click image for larger version. 

Name:	Capture3.PNG 
Views:	15 
Size:	53.2 KB 
ID:	40491

  9. #24
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    I figured out the error that I was having and I have gotten it fixed. I just needed to add the Master/Child links to all are area's instead of just one.

    Master Field: AddressID;FamiliesID;ChurchID
    Child Field: AddressFK;FamiliesFK;ChurchFK

    Before I only had the AddressID and AddressFK linked.

    Sincerely,
    Clifford86

  10. #25
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24
    Quick question please.

    My tblAddress table has City, State, & Zip. I am thinking about creating tables instead for the them.

    tblState
    tblCity
    tblZip
    Then setup the relationships for them. What I am not sure about is what the relationships should be like. My goal is to be able to cut down on errors and time. So on my form, when the zipcode is entered it auto fills State & City.

    Should I say:

    tblZip one-to-one tblState one-to-many tblCity
    or
    tblState one-to-many tblZip one-to-many tblCity
    I am not sure about this. All suggestions much appreciated. Thanks

    Cross Posted: https://www.accessforums.net/showthread.php?t=79227

    ---
    Sincerely,
    Clifford86
    Last edited by Clifford86; 12-26-2019 at 05:47 PM. Reason: Cross Posted / Adding Link

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 01-18-2016, 07:02 PM
  2. SubForm not allowing new records
    By Calego in forum Forms
    Replies: 5
    Last Post: 08-15-2015, 05:35 PM
  3. Replies: 11
    Last Post: 05-13-2015, 11:10 PM
  4. Replies: 2
    Last Post: 06-27-2014, 11:19 AM
  5. subforms allowing additions
    By Csalge in forum Forms
    Replies: 5
    Last Post: 04-05-2013, 09:36 PM

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