Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

    multiple tables and relationships

    I have a database that I have to design. And it's not what I specialize in, so I was hoping to do it in a flat, one table database. That I could handle. But the limitation of 255 fields in a table prevent me from having that as an option. So I'm going to have to create multiple tables and build relationships in them. I'd like to see if someone could clue me in on whatever it is that prevents me from being able to understand how to work with multiple tables and relationships? I just can't understand how to link them in a way that leads to a working system. To explain the parts as simply as possible, The database would have one table for customers and the PO's that they've submitted, another table with materials received, supplier, receive date, details of the material shipments received, another table with quantity ordered, specs of the order, machine used to do the order, shipment details of order being shipped out, operator signature, another table for final inspection length measurement taken, accept or reject, straightness measurement, accept or reject, surface condition record, accept of reject, inspector signature, inspected date


    I know it seems like a lot of details for something that is supposed to be "as simple as possible", but I'm leaving a lot out.
    How do tables like these somehow get connected with relationships?
    I've done a bunch of research, and created a bunch of different databases in an effort to figure it out, but I always run into a dead end.
    Thanks in advance.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'd like to see if someone could clue me in on whatever it is that prevents me from being able to understand how to work with multiple tables and relationships?
    Probably not. Likely could only try to explain it, but it's been extensively documented so there's lots for you to read. I think fundamental is to think in terms of entities (tables) and attributes (fields). This is just an example for illustration:

    A PO is an entity with attributes like number id, date created, status, who created, etc. A line item ordered on that PO is not an attribute of the PO, it is an attribute of the entity POLine. One way to tell is that the PO can exist and have no line items yet. Another is that you'd need a field for every line item (repeating fields) and that is a huge no-no. You link the line items to the PO by including the primary key field from PO table in the line items table as a foreign key and the items table has its own primary key.

    That's somewhat simplistic but I'll stop there since the subject is too big for covering in a forum thread. Deciding what is an entity and what is an attribute is often the hardest part of designing, and it really depends on the business that a db is going to support. A flat file is mainly for data storage and makes for a very poor relational db design. If you do that, I'm confident that you'll regret it - and we'll see a lot of your posts here and there.

    I have links for other important topics but they're much use if you follow the flat file approach. See if these help with 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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Relationships between linked tables are important to enforce referential integrity
    For a better understanding of the topic, see my 3 part article starting with Relationships1 (isladogs.co.uk)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here's a sample data model of Customer and Orders from Barry Williams' site.
    It may give you an idea of the multiple tables and relationships involved.

    Click image for larger version. 

Name:	CustomersAndOrders.png 
Views:	36 
Size:	161.0 KB 
ID:	47756
    Last edited by orange; 05-04-2022 at 04:55 PM. Reason: changed the png

  5. #5
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

  6. #6
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    I had to post my screen shots separately..
    Thank you for the info. I've been doing a lot of work and research. My efforts have created a system that only gets so far. I must not have the right design. Could someone look at the relationships to tell me what's wrong?

    The idea was to create tickets stemming from a customerID field in the customers table. That primary key is linked to a foreign key in the incoming (new order) table, and the primary key for that table (incomingID) is linked to the foreign key in the table for the next step, the ProcessControl table, the primary key for that table (ProcessControlID) is linked to the foreign key (PCS-ID-FK) in the table for the next step, the Final Inspection table. Also for the incoming table there's a table (shipmentsReceived) that connects with shipments of materials that are received for the order. And for the process control table, there's a similar table for shipments going out (Shipout). They're connected in the same primary key/foreign key method.

    Am I on the right track?
    One thing that I think may connect the dots here would be if I could figure out how to populate the foreign key values with the values of the primary keys that are in the corresponding parent tables. Is there a way to have that done while records are being created? For example as I'm filling out the incoming form, when I click on the shipping received form, the "incomingID-fk" field would receive the value that's already in the "incomingID" primary key of the incoming table. If I was able to follow that pattern from table to table, through the process, that would be how I could query orders that have data that span separate tables.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You had a moderated post that had a corrupt image so I deleted it. The other image doesn't appear to have worked either, so you might try again.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

  9. #9
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    The images should be attached to this post.
    Attached Thumbnails Attached Thumbnails download.png   relations.jpg  

  10. #10
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    And in terms of the main forms that I'm working with: the customerFRM is based on the customersTBL and has a subform of incomingTBL. The incomingFRM is based on the incomingTBL and has a subform of shippingreceivedTBL. The processcontrolFRM is based on the processcontrolTBL and has a subform of shipoutTBL. Then there's the final inspection form.

  11. #11
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

    New Design, one that now may make sense

    and not require creative compensation. What do you think?
    Attached Thumbnails Attached Thumbnails new.jpg  

  12. #12
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should be using the underscore in field names "Actual_MM_Diam" NOT "Actual-MM-Diam" (dashes)


    Any chance you would post your dB?

  13. #13
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    Hi Steve. Yes, I've got to get better at naming things. I'd post the DB, but it's in a constant state of flux. I thought I had it working so that I had a foundation to build off of. And I might be there still. But I've got to figure out how I can be on one form, let's say the 1st, fill out the data within it, save it to populate a primary key, and have the value of that primary key go into the foreign key of the subsequent table (as I click the command button to create a new record in the subsequent form. That's my way of connecting the records from the different table. At least that's the game plan.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    sheckay,

    You may find this short youtube video Designing Table Relationships by Lisa Friedrichsen.
    She has other videos that may be helpful as well.

  15. #15
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    Thank you for the video. I set things up differently now. I've created a job_ID table that is the centerpoint of the various other tables related to the different processes. And it works so far, but there's one part of it that needs to be automatic. Matching the JOB_ID primary key with the fk in each of the other tables. Is there a VB command that I can use to create a new record in each table (without opening up each table), and upon doing this populating the fk's with the value of the JOB_ID primary key?

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

Similar Threads

  1. Replies: 12
    Last Post: 07-24-2018, 06:01 AM
  2. Replies: 4
    Last Post: 05-30-2018, 11:54 AM
  3. Tables with multiple relationships
    By computergirl in forum Access
    Replies: 9
    Last Post: 03-26-2015, 03:25 PM
  4. Replies: 4
    Last Post: 09-15-2014, 05:39 AM
  5. Many to Many Relationships for Multiple Tables?
    By RichNCSU in forum Database Design
    Replies: 13
    Last Post: 05-03-2012, 03:07 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