Results 1 to 10 of 10
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    Appending a record manually to a table among 5 linked tables

    I need to append another record (about 10 fields out of 200 fields) to a table (Table 1) which is one of 5 linked tables of duplicate starting data. Each table is used to store unique values acquired by field technicians. So, each table holds exactly the same number of records (909) and they are correctly identified by the Access ID which links them.

    When I attempted to simply add the 10 fields manually to Table 1, an error popped up when I tried to save that this will require adding the same record to the other linked tables (or something to that effect).

    I considered de-linking Table 1 but I suspect that this would ruin all my current queries and forms which rely on the current Table 1 link.

    So, my question is: how do I safely append the extra record?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Why do you have 5 table of duplicate data? Why is it not just 1 table?
    you can add fields in a table with out destroying relation joins.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    From the description you gave, it sounds to me like there is a probably design error.
    Could you/would you post a picture of the relationship window or the dB?

  4. #4
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    We import pics into each table (in the future this method will change where the pics will be held outside of the DB). Each Table holds pics only for that function. The import process is such that we allow only 10 pics per function(per Table) and the sequence always starts at 1. So, for that reason I can't have only one Table.

  5. #5
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Click image for larger version. 

Name:	5 Joined Tables.JPG 
Views:	18 
Size:	126.3 KB 
ID:	34787

    The image shows the 5 joined tables.

  6. #6
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    This is an awful design. You look to have taken 5 spreadsheet worksheets and replicated them in Access.

    You can restrict input to a table using validation rules, and you can easily identify a records status with a simple numeric flag.
    You are duplicating a massive amount of information that should be in other tables, your Customers details should be in another table and simply linked by the customer account number.

    Even without the issues above, you are suggesting that when technician 6 arrives you will add another table and redesign your whole database to work with the other table...

    The very fact that you have 200 fields per table is a strong indication that things are very wrong.

    You need to have a read up on normalisation.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    I appreciate your comments. But, I inherited this project and my immediate issue is how to append one more record to Table 1. Any thoughts?

  8. #8
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I'm not sure you can easily, as the linking will prevent anything that isn't in all 5 tables simultaneously.
    The more I look at it, I can't really see how any new data is ever added to those tables...
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    That is what I thought the relationships would be.


    I am 100% in agreement with Minty's comments.



    Other things that are a problem is that reserved words are used as object names ("Type" is a reserved word) and special characters (#) are used in the name as are spaces. And every table with the PK field of "ID"...

    I know you said you inherited this project, but.... I'm just sayin........ lots of things need to be fixed.

  10. #10
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    OK, after scratching my head for a while, I figured out the core issue related to my immediate problem. I have a "circular" join between Tables 1, 2 and 3. After deleting the join between Tables 2 and 3, I can now append the additional record in Table 1. Hooray!

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

Similar Threads

  1. Replies: 15
    Last Post: 09-01-2016, 04:39 PM
  2. Replies: 1
    Last Post: 02-07-2016, 02:41 PM
  3. Replies: 9
    Last Post: 10-24-2014, 11:34 AM
  4. Replies: 3
    Last Post: 05-13-2013, 10:16 PM
  5. Replies: 5
    Last Post: 12-09-2012, 02:29 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