select query

[Robinc76]

Hi All,
I've created a database that we use to manage our sales orders. Two of the tables are 'Orders' and 'orderdetails'. The order table stores the order header info, such as customer name, account number, etc and the orderdetails table stores the products, qnty's, price, etc. They are linked with a one-to-many relationship using the orderID field.

I want to run a select query to list the orders1111 that include productID = 57 (thats the easy part) but I also need to list of the other order lines on the selected sales order

Can anyone help?

TIA
Robin

 

[ajetrumpet]

I want to run a select query to list the orders1111 that include productID = 57 (thats the easy part) but I also need to list of the other order lines on the selected sales order

Can you elaborate on this??? What exactly do you mean??? I'm not following you here....

 

[Robinc76]

OK, i need to query the orders and list the orders that include productID 57 but i also need to list all of the other products on those orders. So, for instance:

OrderID 13000 has two products 57 and 58. So the query will pick this up because it includes 57 but i also need to list the other product too, in this case 58 (it will not always be 58, it could be any combination of 1000 prods).

Hope this helps,
Robin

 

[ajetrumpet]

They are linked with a one-to-many relationship using the orderID field.

This causes a problem, I believe, with your current setup structure.

i need to query the orders and list the orders that include productID 57 but i also need to list all of the other products on those orders.

This should be a simple task with the correct kind of structure.

OrderID 13000 has two products 57 and 58. So the query will pick this up because it includes 57 but i also need to list the other product too

I can visualize about half of what you are saying...would you care to provide the following information to clarify...??

* 2 tables and their fields

* Current SQL statement for the query.

 

[Robinc76]

The two tables and there fields are

table: Orders
OrderID (primary key)
Customer
AccountNumber
OrderDate

Table: [Order Details]
OrderID
OrderDetailID (primary key)
ProductID
Description
Quantity
UnitPrice

My SQL is limited so i have only managed to create the query that shows all of the rows with product = 57

SELECT Orders.OrderID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.[OrderID] = [Order Details].[OrderID]
WHERE ((([Order Details].ProductID)=57))
ORDER BY Orders.OrderID;

Now i need to show all of the rows that have the same orderID as the ones returned in the above query.

TIA
Robin

 

[ajetrumpet]

The two tables and there fields are

table: Orders
OrderID (primary key)
Customer
AccountNumber
OrderDate

Table: [Order Details]
OrderID
OrderDetailID (primary key)
ProductID
Description
Quantity
UnitPrice

My SQL is limited so i have only managed to create the query that shows all of the rows with product = 57

SELECT Orders.OrderID, [Order Details].ProductID
FROM Orders INNER JOIN [Order Details] ON Orders.[OrderID] = [Order Details].[OrderID]
WHERE ((([Order Details].ProductID)=57))
ORDER BY Orders.OrderID;

Now i need to show all of the rows that have the sameorderID as the ones returned in the above query.

I wonder if changing the query code to this would work...???

SELECT Orders.OrderID, [Order Details].ProductID

FROM Orders INNER JOIN [Order Details] ON Orders.[OrderID] = [Order Details].[OrderID]

WHERE Orders.OrderID IN (SELECT Orders.OrderID FROM Orders INNER JOIN [Order Details] ON 
  Orders.[OrderID] = [Order Details].[OrderID] WHERE [Order Details].ProductID = 57)

ORDER BY Orders.OrderID;

I should also say too Robin, that I really don't see the need for two tables here, although I have seen this setup many times. You could eliminate a lot of headaches if you just put all of this data in one ORDERS table...

Your OrderID is probably an autonumber, and I'm not a fan of autonumbers. My sample DB has an example of an invoice form that makes this problem of yours completely dissapear, but the only problem with it is that it does not use the autonumber for an OrderID, and one of the big reasons for that is because of THIS problem....

http://www.access-programmers.co.uk/forums/showthread.php?p=636636#post636636

 

[Robinc76]

The code you suggested worked! Many thanks. My table structure was based on the Northwinds database. I'll take a look at your databse to see how it could work for us.

Thanks again.
Robin