Results 1 to 8 of 8
  1. #1
    Dona is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    2

    Relations and referential integrity

    Hi
    I am an account student and part of my assignment is to build access database for a bogus bike shop.

    I have created the following tables
    Suppliers, Customers, Products, Purchase Orders and Sales Orders
    The way tutor said is that the relationship should be in the following way

    Supplier to products (one to many) - products to purchase orders (many to one)
    I have used Suplier Id (PK primary key) in suppliers and made a foreign key in products table (FK foreign key) but it seems that I get referential integrity error and I am unable to do it.

    Then Customers to products (one to many) - products to sales orders (many to one)
    Here I am
    Facing a different issue
    If I use customer ID as PK then I will have many of those customer IDs next to one product which won’t work as it won’t be unique and I will have to duplicate product data so that each customer transaction will have their customerID next to it.

    I have ended up doing
    Supplier - purchase order one to many and purchase order to product many to one, using a logic that one supplier can have many purchase orders and one product can be on many purchase orders
    (I have used same logic on customers and sales orders)



    But I was told it’s wrong

    How can I correct the error Please?
    Any help appreciated as I am stuck

    I know this forum is for advanced questions and not student assignments but the tutor is refusing to tell the solution and I have exhausted tutorials and websites
    Thank you in advance and also I am happy to share the file via one drive
    I appreciate this is at very basic level and frankly useless but for some reason university decided that access is necessary for accountants lol

  2. #2
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Maybe it's better if you attach the file to see what you've actually achieved and thus help you more easily.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    purchase orders should have two tables, the order header and the order line

    > shows the direction of one to many

    suppliers>order headers>order lines>products

  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
    I agree that a picture of your tables and relationships would be helpful.

    Here is a sample data model from Barry Williams' site(now defunct) that may offer some insight.

    Click image for larger version. 

Name:	CustomersAndInvoicesNov22.jpg 
Views:	27 
Size:	111.1 KB 
ID:	49175

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    only time for a quick comment
    Supplier to products (one to many)

    That is fine if only one supplier will ever provide a product. If not true, then you'd have to repeat all the fields in a product record just so you could relate it to another supplier. Plus if you just edit the supplier field to perform a supplier change, you'd lose all the historical supplier/product data if you were using autonumber pk values to relate the two. I'd say that in the real world, a business would like to have the capability to order from more than one supplier.
    In other words, you need a junction table for product/suppliers?
    Last edited by Micron; 11-22-2022 at 10:21 AM. Reason: added comment
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Dona is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    2
    Click image for larger version. 

Name:	3907F1BB-6670-4583-A701-5CBD4A8B28B9.jpg 
Views:	16 
Size:	77.0 KB 
ID:	49191

    hi and thank you everyone for replying
    I have attached a picture of relationships

    looking forward to your reply
    and again this is meant to me within basic knowledge of access I am an accounting student, this is a part of a module (6weeks or so, not very much time to get abundance of knowledge)

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that only works if you only ever order one product from a supplier at any one time and customers only ever order one product at any one time - see post #3

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Might as well expand the width and height of tables so that all, if not most, can be seen? Use an image clip app to cut out all the extraneous detail? The details are small when viewed on a laptop.

    Suggest no spaces in names (as stated, can't see the rest so I don't know). Tables like PO should be headers with related line item tables as CJ is saying. Headers are for PO data only - POIDpk, PONo, CreateDate, CreateBy, SupplierID), etc.
    Last edited by Micron; 11-24-2022 at 09:13 AM. Reason: added comment
    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. Referential integrity
    By Lou_Reed in forum Access
    Replies: 14
    Last Post: 12-17-2015, 03:08 PM
  2. Referential Integrity
    By lefty2cox in forum Forms
    Replies: 2
    Last Post: 11-30-2015, 07:26 AM
  3. Referential Integrity
    By YunqHero in forum Forms
    Replies: 4
    Last Post: 12-17-2012, 05:05 PM
  4. Referential Integrity
    By Paul H in forum Database Design
    Replies: 3
    Last Post: 11-14-2011, 03:07 PM
  5. Referential Integrity
    By Desstro in forum Database Design
    Replies: 4
    Last Post: 06-13-2010, 01:29 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