Tech Support Guy banner
Status
Not open for further replies.

Access Relationship Issue

2K views 16 replies 5 participants last post by  Fireflycph 
#1 ·
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?
 

Attachments

See less See more
1
#3 · (Edited by Moderator)
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.
 
#4 ·
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 ·
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.
 

Attachments

#7 ·
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.
 

Attachments

#8 ·
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.
 

Attachments

#9 ·
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).
 
#12 ·
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.
 
#15 ·
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
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 ·
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.
 
#17 ·
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.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top