Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 30th, 2005, 01:56 PM
Registered User
Join Date: Mar 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default JOIN query

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!
Old March 31st, 2005, 08:18 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

You need a primary key in the Components table, and in the ShipReceiveSub table. Are there any indexed fields in these two tables? Is PartNumber your PK field in the Components table? It shouldn't be unless it is an autonumber field.

Old April 5th, 2005, 01:44 PM
Friend of Wrox
Join Date: Aug 2004
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts

According to MsAccess help u would need to queries. 1# Query u would select
the ShipReceiveID that match ur data criteria. 2# Query would use qry #1
instead of ur SQL above: open the design grid having the SQL above as the
SQL view. Add qry#1 and delete the relation between the ShipReceiveID and
Components tables on the grid. Run the qry u have now on design grid.
rgds Penta.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help using join in SQL query yogeshyl SQL Language 1 July 30th, 2008 04:12 AM
Nested Query or Join? rlull SQL Server 2000 4 November 10th, 2005 05:23 PM
Opposite to INNER JOIN query crisan Access 1 April 29th, 2005 06:50 AM
Join Query tsimsha Classic ASP Databases 2 December 2nd, 2004 09:31 AM
Strange join query nikosdra SQL Language 1 August 10th, 2003 06:10 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.