Are my relationships correct?

[Dave888]

I've created a database to track payments made to vendors for Purchase Orders.
In the database VPOs are paid by PaymentID and there can be more than 1 VPO in a payment ID.
The problem I'm having now is that when I try to query the total balance on one or multiple VPOs, it's only showing me the balance on VPOs that have had alteast 1 payment made.
Are my relationships correct? I don't know why it won't show me VPOs that haven't had a payment made.

Here is the code for the query:
SELECT VPO.VPONumber, VPO.VendorCode, [VPO Items].TotalCost
FROM (VPO INNER JOIN [VPO Items] ON VPO.VPONumber = [VPO Items].VPONumber) INNER JOIN ([Payment ID] INNER JOIN [VPO Payment] ON [Payment ID].PaymentID = [VPO Payment].PaymentID) ON VPO.VPONumber = [VPO Payment].VPONumber
GROUP BY VPO.VPONumber, VPO.VendorCode, [VPO Items].TotalCost;

 

[speakers_86]

Relationships look fine to me. In the query, go to design mode, double click on the relationship line, and change to option2. See if that works, if not, try option 3. One of those should fix your problem. I cant really read sql, so I dont know if your query is right. If you still have problems after trying this, consider using multiple queries to accommodate your needs.

 

[Rabbie]

Dave your problem may be with the relationship between VPO and VPO Payment. Right click on this link and edit the relationship so that it selects All VPO and VPO Payment where it matches.(type 2 or 3 I think) This will change the SQL INNER Join to a LEFT join so you should get what you are looking for. Sorry I can't give you more detailed info but I am away this week and don't have everything to hand

 

[Rabbie]

Dave a further thought. Don't have spaces in your table names. Use either VPO_Payment or VPOPayment instead

 

[Pat Hartman]

The query will not produce the results you expect. it will produce a Cartesian Product. Items has a 1-m relationship with VPO and payments has a 1-m relationship with VPO. Just because the three tables include a common field does not mean that it makes sense to join them. For example. If you have two payments and three items for a given VPO, your query will result in six rows being returned for that VPO. A Cartesian Product takes every row of 1 table and joins it to every row of the other table so you end up with 2(payments) times 3(items) = 6 rows.

In additon to removing the spaces from your table names, remove the special characters from your column names. Proper names should contain only the letters a-z (upper or lower case), the numbers 0-9, and the underscore. Names should start with a letter and be no longer than 30 characters in order to be compatable with other databases. CamelCase is commonly used but some people prefer to use The_Underscore as a separator.

And finally, neither VPO Items nor VPO payments has a primary key. All tables should have primary keys. If you have no suitable candidate key for a table, use an autonumber.