Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Display all lines relating to an order if one line has certain critera 2

Status
Not open for further replies.

Drummermoose

Technical User
Jan 22, 2002
48
0
0
GB
Hi

I'm trying to do a query in access from data on SQL server.

I want to be able to display all the lines which have the same order number if one of those lines has a certain critera.

At the moment I can get it to bring back only the certain line that has the critera(Suppliercode="REG"), not all the lines of the order which it is on.

I tried to do a seperate query to get the order no's where suppliercode = "REG" and then linked it to the 1st query to display all lines relating to the generated order no's, but it took about 10 mins to run and slows the system to a halt.

I hope this makes sense!
Thanks for any help you can give.
 
What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
here's the code for the query:

Code:
SELECT dbo_Customers.Surname, dbo_OrderLines.desciption1, dbo_OrderLines.description2, dbo_OrderLines.Description3, dbo_OrderLines.Qty, dbo_OrderLines.[Despach Date], dbo_Employees.Name, dbo_tblStores.StoreName, dbo_Orders.ID
FROM dbo_OrderLines INNER JOIN (((dbo_Customers INNER JOIN dbo_Orders ON dbo_Customers.ID = dbo_Orders.CustomerID) INNER JOIN dbo_Employees ON dbo_Orders.EmployeeID = dbo_Employees.ID) INNER JOIN dbo_tblStores ON dbo_Orders.StoreCode = dbo_tblStores.StoreCode) ON dbo_OrderLines.OrderID = dbo_Orders.ID
WHERE (([dbo_OrderLines]![SupplierCode]="REG"))
GROUP BY dbo_Customers.Surname, dbo_OrderLines.desciption1, dbo_OrderLines.description2, dbo_OrderLines.Description3, dbo_OrderLines.Qty, dbo_OrderLines.[Despach Date], dbo_Employees.Name, dbo_tblStores.StoreName, dbo_Orders.ID;
 
What about this ?
Code:
SELECT C.Surname, L.desciption1, L.description2, L.Description3, L.Qty, L.[Despach Date], E.Name, S.StoreName, O.ID
FROM ((((dbo_Customers C 
INNER JOIN dbo_Orders O ON C.ID = O.CustomerID)
INNER JOIN dbo_OrderLines L ON O.ID = L.OrderID)
INNER JOIN dbo_Employees E ON O.EmployeeID = E.ID)
INNER JOIN dbo_tblStores S ON O.StoreCode = S.StoreCode)
INNER JOIN (SELECT OrderID FROM dbo_OrderLines WHERE SupplierCode='REG'
) R ON O.ID = R.OrderID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Looks good!

I just discovered that a few orders have two occurances of 'REG' lines therefore the orderlines are showing twice.

Is there anyway to rectify this?

Thanks for your help it's much CLOSER now
 
Code:
SELECT [b]Distinct[/b] C.Surname, L.desciption1, L.description2, L.Description3, L.Qty, L.[Despach Date], E.Name, S.StoreName, O.ID
FROM ((((dbo_Customers C 
INNER JOIN dbo_Orders O ON C.ID = O.CustomerID)
INNER JOIN dbo_OrderLines L ON O.ID = L.OrderID)
INNER JOIN dbo_Employees E ON O.EmployeeID = E.ID)
INNER JOIN dbo_tblStores S ON O.StoreCode = S.StoreCode)
INNER JOIN (SELECT OrderID FROM dbo_OrderLines WHERE SupplierCode='REG'
) R ON O.ID = R.OrderID

Leslie

Have you met Hardy Heron?
 
Use the DISTINCT predicate:
...
INNER JOIN (SELECT [!]DISTINCT[/!] OrderID FROM dbo_OrderLines WHERE SupplierCode='REG'
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
I've come into a slight problem. The code works great, but when I save and close it, the code stops working. I look at it and it looks as if Access has changed part of the code.

Code:
SELECT DISTINCT C.Surname, L.desciption1, L.description2, L.Description3, L.Qty, L.[Despach Date], E.Name, S.StoreName, O.ID, L.ID
FROM ((((dbo_Customers AS C INNER JOIN dbo_Orders AS O ON C.ID=O.CustomerID) INNER JOIN dbo_OrderLines AS L ON O.ID=L.OrderID) INNER JOIN dbo_Employees AS E ON O.EmployeeID=E.ID) INNER JOIN dbo_tblStores AS S ON O.StoreCode=S.StoreCode) INNER JOIN [SELECT OrderID FROM dbo_OrderLines WHERE SupplierCode='REG'; ] AS R ON O.ID=R.OrderID;

Any ideas how to stop it from meddling?
 
Create a query named, say, qryREGorders:
Code:
SELECT DISTINCT OrderID FROM dbo_OrderLines WHERE SupplierCode='REG'

And now your actual query:
Code:
SELECT C.Surname, L.desciption1, L.description2, L.Description3, L.Qty, L.[Despach Date], E.Name, S.StoreName, O.ID, L.ID
FROM ((((dbo_Customers AS C
INNER JOIN dbo_Orders AS O ON C.ID=O.CustomerID)
INNER JOIN dbo_OrderLines AS L ON O.ID=L.OrderID)
INNER JOIN dbo_Employees AS E ON O.EmployeeID=E.ID)
INNER JOIN dbo_tblStores AS S ON O.StoreCode=S.StoreCode)
INNER JOIN qryREGorders AS R ON O.ID=R.OrderID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top