I am trying to construct a query that will retrieve data from several tables. TO best explain this, I will give an insight into table structure and relationships:
This is a small DB used to track stock at any particular date.
Here are the three tables:
Components: PartNumber, Description, Colour as fields.
ShipReceiveSub: ShipReceiveID, PartNumber, Quantity
ShipReceive: ShipReceiveID, Date
Components table links to ShipReceiveSub via PartNumber.
ShipReceiveSub links to ShipReceive via ShipReceiveID.
I trust this makes sense. This allows me to perform a count of components I receive on a particular date.
(I can then use this for stock control)
Now what I want to do is create a query: One that will show up ALL the PartNumbers and the Quantity from a certain date onwards.
E.g. Show all PartNumbers, Quantity received from 30 March 2005. Therefore if I received 100 units of Part 1 on 30/03/05, and 50 units on 31/03/05, the query would return that I received Part 1: 150.
This is simple enough to do. Though I run into difficulty when I want to expand the query...
What if NO units of stock were received? i.e. I didnt receive any units of Part 2 at all. I want it showing up on the query as having a zero value.
I wrote this query to do so:
SELECT Components.PartNo, IIf([total] Is Null,0,[total]) AS Received, Sum(ShipReceiveSub!Quantity) AS total
FROM Components LEFT JOIN ShipReceiveSub ON Components.PartNo = ShipReceiveSub.PartNo
GROUP BY Components.PartNo;
So I select each PartNumber from the Components table, a Total field that says is there is zero then show zero. I then link to ShipReceiveSub to get quantities of each PartNumber.
Now all I want to do is link to ShipReceive which holds the date field...so that I can specify for which dates I want to retrieve totals for.
i.e. >=#30/03/2005# =
PartNumber: 1 Total: 150
PartNumber: 2 Total: 0
PartNumber: 3 Total: 0
With the SQL shown above, if I add in the ShipReceive Table to the already present Component, ShipReceiveSub tables (which are linked by PartNumber) I get an error message:
"The SQL statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a seperate query that performs the first join and then include this in the SQL statement"
Its really baffling me, for something which I think is pretty trivial. Can someone direct me in the right direction and show me where I was going wrong?
Sorry for the long post, but I hope it aids you all in my dilemma!
Thanks for your help guys, I know someone has the illusive answer!