Access Relationship Issue

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 

Attachments

Joined
Apr 1, 2016
Messages
1,202
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?
 
Joined
Sep 21, 2007
Messages
11,985
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:

OBP

Joined
Mar 8, 2005
Messages
19,895
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?
 

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.
 

Attachments

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.
 
Joined
Apr 1, 2016
Messages
1,202
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.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
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

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).
 
Joined
Apr 1, 2016
Messages
1,202
First Name
Morten
Thx OBP. I'll have a look at that
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:
Joined
Apr 1, 2016
Messages
1,202
First Name
Morten
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).
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.
 

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.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Can you post your database as it is now and I will take a look at it.
 
Joined
Apr 1, 2016
Messages
1,202
First Name
Morten
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top