Results 1 to 2 of 2
  1. #1
    agreen is offline Novice
    Windows 10 Access 2007
    Join Date
    Jan 2023
    Posts
    1

    relationship for sub-table


    Hi all I am trying to create a database for my sister, who is doing B2B hemming. I am having issues figuring out how to get multiple services for a single item, the relationship keeps connecting the one-to-many in the wrong direction (see image) can anyone tell me what I'm doing wrong? The basics are, an order with the items (one to many), and the services for those items (many to one).
    Attached Thumbnails Attached Thumbnails dbCapture.JPG  

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you had 5 records in service, each and everyone of them can appear in the item record for one item. However, that would mean repeating all the fields 5 times for one item. You need what's known as a junction table instead of service table. I'd call it ItemServices. Sticking with the 5 services example, there would be 5 records with the same ItemID and 5 different servicetypeID. If the table has a PK autonumber id, I don't see it being joined to anything.

    Your pk fields should reflect the table they belong to, so itemTypeID (or itemTypeIDfk and itemTypeIDpk), not itemID as that implies it relates to the item table.
    ID makes for a poor table name IMO. Maybe clear today, but wait until you have to tweak things 6 months from now.
    Last edited by Micron; 01-19-2023 at 12:48 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 7
    Last Post: 10-29-2021, 09:12 AM
  2. Replies: 5
    Last Post: 09-04-2017, 09:22 PM
  3. table relationship
    By mnjohn in forum Access
    Replies: 5
    Last Post: 07-17-2017, 08:40 AM
  4. Table Relationship Help
    By JoshLewis in forum Database Design
    Replies: 1
    Last Post: 04-08-2013, 10:55 AM
  5. Replies: 2
    Last Post: 04-08-2012, 03:04 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