JOIN Query Problem

[wdverner]

Hi All,
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!

 

[traingamer]

One way (tested):

SELECT tblShipReceiveSub.PartNumber, Sum(tblShipReceiveSub.Quantity) AS SumOfQuantity
FROM tblShipReceiveSub INNER JOIN tblShipReceive ON tblShipReceiveSub.ShipReceiveId = tblShipReceive.ShipReceiveId
WHERE (((tblShipReceive.Date)>#1/5/2005#))
GROUP BY tblShipReceiveSub.PartNumber;

Save it as qPartDate

Then join to that query:

SELECT tblComponents.PartNumber, tblComponents.Description, [COLOR=red]nz(qPartDate.SumOfQuantity,0)[/color]
FROM tblComponents LEFT JOIN qPartDate ON tblComponents.PartNumber = qPartDate.PartNumber;

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[traingamer]

Although I am wondering why you even have a 'ShipReceive' table. Since the date and quantity are related why isn't the date in the 'ShipReceiveSub' table, eliminating a table?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[wdverner]

Greg,
Thanks for your prompt reply and appreciated advice. i created qPartDate and qStock:


qPartDate returns only ProductNumber that have a physcial stock

Part number 1: 100
part number 2: 50

qStock incorporates qPartDate. When I run it, it prompts for Components.PartNumber, Components.Description, qPartDate.SumOfQuantity. And then returns double the amount of PartNumbers in stock (at current there is 329, the query returned 658 records)THOUGH it shows zero values for all with NO PartNumbers or Descriptions?

Hope this makes sense? Again thanks for your patience and help.

 

[wdverner]

Greg,
I THINK i may have solved it...testing now!