1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Relationship Issue

Discussion in 'Business Applications' started by DangerBoy32, Dec 20, 2018.

Advertisement
  1. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    235
    First Name:
    Jack
    Hello DangerBoy. I took a look under that hood of your database and determined that the underlying problem here is two-fold.

    1. The overall table relationships are incorrect. The relationships should be Many-to-One (with Referential Integrity enforced) from:

    tblInventory-to- tblPurchases
    AND from​
    tblCustomers-to-tblPurchases​
    This is why you cannot pull up the Item Cost on your query correctly.
    2. For the "tblPruchases.Item Number" field, the Combo Box, Row Source should read:

    "SELECT tblInventory.[Item Number] FROM tblInventory;"​
    BUT - Changing this entry will not fix the problem; because, of how the Table/Field was originally created, with the Combo Box, Row Source reading:

    "SELECT tblInventory.ID, tblInventory.[Item Number] FROM tblInventory ORDER BY tblInventory.[Item Number];"
    Both issues are interrelated, so.... you have to break them both, fix them independently, and then put them back together to correct the underlying problems. However, doing so will also require you to re-select all of the Item Numbers on the Purchases table AND you will possibly have to make modifications to all of your existing forms, reports, and queries. If you want to give it a try, I will try to help you through the process.
     
  2. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    744
    First Name:
    Morten
    I concur with @Chawbacon . This is the reason I asked where the data came from. Again, my relationship was wrong. But with the data the way it is you can't create a one to many relationship with Referential integrity.

    THe easiest way to explain it is to say that you can have only one record per item in the inventory table, but that item can appear many times in the purchases table.

    If I were you I'd delete all the data and then create the one to many relationships with, as @Chawbacon says, enforcing Referential relationships. I did it it with the Customer one, I believe, but wasn't able to do it to the inventory/purchases, due to the existing data.

    But now where there's a new person whom, it seems, knows much more that I, I'll leave you in his/her capable hands.

    Good luck.
     
  3. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1220759

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice