Some more assistance please..

[joebloggs2004]

I'm having trouble with these 2 queries:

A sorted list showing each product requested on each client stock request. Show client name, product number and quantity requested in name and then product order.

SELECT C.clientName, RL.productNum, RL.qtyRequested
FROM CLIENT AS C, REQUEST_LIST AS RL
WHERE C.clientNum IN (SELECT clientNum FROM STOCK_REQUEST)
ORDER BY C.clientName, RL.productNum;

With this one (above) I just get a cartesian product. It doesn't only show clients that have ordered. Those who haven't it puts the product with 1 requested, same with clients who HAVE ordered - it puts a 1 next to products they haven't ordered.

9. A list showing product number, the quantity requested, the quantity picked and the difference between the two. For products stored in more than one location within a warehouse the quantities should be added together.

SELECT RL.productNum, RL.qtyRequested, COUNT(PL.quantityPicked) AS [Quantity_Picked], ( RL.qtyRequested - PL.quantityPicked ) AS [Difference]
FROM REQUEST_LIST AS RL, PICKING_LIST AS PL
WHERE RL.productNum IN (SELECT productNum FROM PICKING_LIST);
GROUP BY productNum;

I've tried so many variations for both queries I'm just confusing myself more and more. Any help (in the next hour or so, preferably) would be appreciated.

The other query I'm having trouble even starting is
10. A list of supervisors (staffid, surname and first name) and all of their subordinates (staffid, surname and first name).
It just doesn't make a lot of sense. The supervisors in the Employee table have nothing specified in the supervisor field. Their staffIDs are also available in the Warehouse table as a foreign key (managerID).

 

[WayneRyan]

Joe,

You're not joining the tables on anything! The nested Selects limit the
domain of the data, but you will still get a Cartesian product.

The CLIENT and REQUEST_LIST tables have ClientNum in common.

You need to join where Client.ClientNum = Request_List.ClientNum

Experiment in the Query Design grid, then right-click and have a look
at the SQL view.

Wayne

 

[joebloggs2004]

Ok, I've done that, but it uses inner joins which i've been told not to use. I've been told to use SQL joins instead. How do I make a SQL join between the 2 tables? Its actually Stock_Request that has a common attribute "clientNum" with Client and Stock_Request shares a common attribute with Request_List "requestID".

 

[joebloggs2004]

I've come up with

SELECT C.clientName, RL.productNum, RL.qtyRequested
FROM CLIENT AS C, REQUEST_LIST AS RL
WHERE C.clientNum = (SELECT clientNum FROM STOCK_REQUEST) AND  RL.requestID = (SELECT requestID FROM STOCK_REQUEST)
ORDER BY C.clientName, RL.productNum;

But I keep getting the message "At most one record can be returned by this subquery".

 

[EMP]

I don't think you need the subqueries in the Where Clause.

Try rebuilding the query in query Design View. After adding the three tables in Design View, properly link the tables by dragging the common fields between the tables.

Drag the fields that you want the query to display to the columns in the query grid. Switch the query to SQL View to see how Access has joined the tables for you in the SQL statement.

 

[joebloggs2004]

I managed to figure them out. Well, I think so. Thanks anyway