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. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    I am having trouble getting the Item Cost to populate on the query pictured. There are item costs listed in the tblInventory table, but the field comes back blank in the query. The relationship between tblInventory and tblPurchases is set to #3 (Join all from tblPurchases (right) and only those records from tblInventory (left) where they are equal. Does anyone have any ideas what I have done wrong?
     

    Attached Files:

  2. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    742
    First Name:
    Morten
    I must be blind or something today. Since I can't see the issue, which there obviously is. Can you zip the DB and upload it, so I can look at it?
     
  3. lunarlander

    lunarlander

    Joined:
    Sep 21, 2007
    Messages:
    9,224
    Is the 'item number' part of the primary key in both tables ?

    Why does the inventory table have 'invoice date' and 'order number' fields ? These 2 fields don't seem to be an inventory thing. You may need to pick up a book on database design, and read about how to design a database into 3rd normal form.
     
    Last edited: Dec 21, 2018
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    I second lunarlander here, there appears to be some inconsistences in the where the fields belong in the tables.
    I am also a little surprised the Query actually works as Access does not normally like unequal joins in queries.
    Does it work if you remove the Customer table?
     
  5. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    Sorry it took so long to post back. I was real busy over the holiday. It is quite likely I may have some fields out of place - I have never attempted any kind of a POS system like this before and there are a whole lot more moving pieces to it then I realized until I got into it. Also, trying to get items purchased to update quantities in the inventory table and not sure how to make it happen (I suspect an update query, but not exactly sure how to go about it). I appreciate any and all suggestions or help received.
     

    Attached Files:

  6. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    lunarlander,

    Invoice Date is the date the inventory was ordered from the supplier and Order Number is the order number of that invoice. I am trying to have a way of tracking particular inventory back to it's original invoice.
     
  7. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    742
    First Name:
    Morten
    I don't know how you managed to do this. But in the "TblCustomer" table you've somehow managed to have the first name of the customer appear in the [CustomerID] field. Also, your relationships are wrong. It'd be easier to upload the "fixed" DB rather than explain what's wrong.

    Unfortunately it means that you have to redo the queries. As an updated Relationship doesn't reflect in existing queries. Not that I can find anyway. So, for what it's worth, here it is.

    There's also some issues with other queries, forms. But I assume they're not finished, so I'll let you do it yourself. One example is the invoice Report/Query. You show all records, where, in my opinion, you should only show unpaid invoices.
     

    Attached Files:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    I do not wish to upset Fireflycph as he responded first to your question.
    However you have now asked a further quesion about inventory so I am simply going to attach a very old database that I created for someone on here which has VBA for updating inventory and costs.

    Note this is not how design databases now, I no longer use menus.
     

    Attached Files:

  9. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    Thanks for the efforts, I'm not all that experienced. I'm not even sure how the changes helped me (although I don't doubt your expertise, I'm just not that experienced). I created a new query and tried to pull the tblInventory cost in with other tblPurchases and got an error about ambiguous joins (or something to that effect).
     
  10. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    Thx OBP. I'll have a look at that
     
  11. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    742
    First Name:
    Morten
    As will I. It's certainly possible that I made a mistake as well. I shall return with more info.

    EDIT: I didn't see the first post from ODB. I'm not upset at all. My expertise in Access is limited. I, as you, just want to assist @DangerBoy32 in solving the problem.
     
    Last edited: Dec 29, 2018
  12. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    742
    First Name:
    Morten
    I DO think I made a mistake. In the Table Relationship I have tables [TblPurchases] and [TblInventory] set as Only where the records are equal. That is wrong. It should be option"3" . "Include all records from [TblPurchases] and only those from [TblInventory] where the joined fields are equal.
     
  13. DangerBoy32

    DangerBoy32 Thread Starter

    Joined:
    Nov 20, 2018
    Messages:
    11
    I still do not get a item cost when I correct the relationship as you suggest in your post. I don't get an error any longer, but the field still comes back blank.
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,694
    Can you post your database as it is now and I will take a look at it.
     
  15. Fireflycph

    Fireflycph

    Joined:
    Apr 1, 2016
    Messages:
    742
    First Name:
    Morten
    Please do as OBP said. Post the db.
    I have a question though. Where did you get the data which are in the tables from. I, once again, may have messed up the relationship. But if there's an error in thee data. SUch as a item existing in one table but not in the other, then you can't enforce data integrity.

    try to reverse the relationship in the query. See if that brings the item cost.

    Edit: I mean in the query only. Not on the relationships
     
  16. 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