Results 1 to 6 of 6
  1. #1
    scootsie00 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2021
    Posts
    1

    Question Editing data across three tables

    Click image for larger version. 

Name:	Capture.PNG 
Views:	12 
Size:	42.9 KB 
ID:	45056

    I have three tables in this database. The Rolodex table includes all information for everyone in the company. The two other tables separate the Rolodex into two different categories, Non Board Members and Board Members. My question is how do I get this to work so if I update the Rolodex it will update the information in the Board Members/Non Board Members as well in order to keep the data accurate?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,314
    What you have is a bad design. You only need one table (Rolodex) with one extra field: MemberType (notice no space in field name) with values of Board and Non-Board.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,758
    You only need one table. It should have a field for MemberType with values of Board and Non Board (or B, NB or whatever). Whatever tblRolodex is, it likely can be replaced with a query that returns either B or NB. Repeating data across tables is a no-no. Maybe research normalization:

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    or find ones you like better.

    EDIT - some of your fields don't belong in the same table at all. "Kids" is a type of 'many' field as a member can have more than one. 'Many' attributes of an entity (Member) belong in their own table. If it is possible that many attributes can belong to many members, you need a junction table for every case. You will forever struggle with this db if you don't normalize it properly.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,688
    Put everybody in one table. Have a True/False field to indicate board member.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,758
    IMO, no to T/F field because someday there might be a 3rd member type. T/F limits you to only 2 possibilities.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,688
    Quote Originally Posted by Micron View Post
    IMO, no to T/F field because someday there might be a 3rd member type. T/F limits you to only 2 possibilities.
    Valid point.

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

Similar Threads

  1. Editing data in 4 tables concurrently
    By ztirffritz in forum Queries
    Replies: 4
    Last Post: 12-03-2018, 04:32 PM
  2. Workaround for editing Linked Tables
    By Stretholox in forum Import/Export Data
    Replies: 3
    Last Post: 12-12-2014, 02:03 PM
  3. Replies: 7
    Last Post: 05-02-2013, 02:10 AM
  4. Editing multiple tables at once
    By Anilusion in forum Access
    Replies: 4
    Last Post: 11-23-2011, 05:28 PM
  5. Replies: 1
    Last Post: 03-13-2010, 12:32 PM

Tags for this Thread

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 - Senior Forums