LEFT JOIN Query involving 3 Tables

[mickyblue]

HI Guys

Its been many years since ive had to develop an access database and i have been asked to create a basic sales entry DB using Access 2007.

I have 3 Tables that this problem involves

Items
Part No (PrimaryKey)
Product Description
Inventory Type
Card Size
Unit Price
Unit Cost

SalesLedger
EntryNo (PrimaryKey)
Order No
Part No
Quantity
UnitPrice
CostPrice

PurchaseLedger
EntryNo (PrimaryKey)
InvoiceNo
Part No
Quantity
UnitCost


Now what im trying to do is create a query to calculate current "Stock on Hand" so i need to be able to create a field for each Item.[Part No] called "TotalPurchase" and "TotalSales" which i can then subtract the sales from the purchase to get the Stock on hand at any give point in time.

no matter what i do though when trying LEFT JOINS i wind up getting double entrys and its over calculating either the sales or purchases.

any help you guys could provide me would be greatly appreciated


Regards

Rhys

 

[georgedwilkinson]

I couldn't help but notice that you didn't include the SQL that is giving you the inaccurate results. Not sure how we can help troubleshoot without that.