Join Query problem for Stock Control

[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!

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Have you tried it this way:

SELECT C.PartNo, IIf([total] Is Null,0,[total]) AS Received,
Sum(SRS.Quantity) AS total
FROM Components As C
LEFT JOIN (ShipReceiveSub As SRS INNER JOIN ShipReceive As SR
ON SRS.ShipReceiveID = SR.ShipReceiveID)
ON C.PartNo = SRS.PartNo
GROUP BY C.PartNo

If that doesn't work, try the following. It uses a undocumented JET SQL
feature: a derived table delimited by square brackets.

PARAMETERS Start_Date? Date, End_Date? Date;
SELECT C.PartNo, IIf([total] Is Null,0,[total]) AS Received,
Sum(S.Quantity) AS total
FROM Components As C
LEFT JOIN [SELECT SRS.PartNo, SRS.Quantity
FROM ShipReceiveSub As SRS INNER JOIN ShipReceive As SR
ON SRS.ShipReceiveID = SR.ShipReceiveID
WHERE SR.[Date] BETWEEN Start_Date? And End_Date?]. As S
ON C.PartNo = S.PartNo
GROUP BY C.PartNo

Have you tried the Nz() function instead of the IIf() in the SELECT
clause?

Nz([total], 0)

I don't know if it will "want" to be included in the GROUP BY clause.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQksTRIechKqOuFEgEQKrcgCg/EfIHJx1Z4IFQwUnnkJfr/lfZ9UAnRzS
cz5wyQU90Ztk4a04LAur3hP8
=Ns67
-----END PGP SIGNATURE-----

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!