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

[Drummermoose]

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.

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Drummermoose]

here's the code for the query:

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;

 

[PHV]

What about this ?

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

 

[Drummermoose]

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

 

[lespaul]

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?

 

[PHV]

Use the DISTINCT predicate:
...
INNER JOIN (SELECT [!]DISTINCT[/!] OrderID FROM dbrderLines WHERE SupplierCode='REG'
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Drummermoose]

Excellent

Thank you for your help!

 

[Drummermoose]

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.

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?

 

[PHV]

Create a query named, say, qryREGorders:

SELECT DISTINCT OrderID FROM dbo_OrderLines WHERE SupplierCode='REG'

And now your actual query:

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