R
Richard Horne
Hi guys, got a bit of a complicated on here.
I have two tables, one called Purchase_Orders and one called Company_Details.
Company_Details contains all our customer's and supplier's addresses and
telephone numbers. Purchase_Orders allows stores the details of a purchase
order.
Within this purchase order, an address is looked up from Company_Details for
the address of who the purchase order is for. But I need to query
Company_Details for another address, the address the goods will be delivered
to.
At present my query is:
SELECT Purchase_Orders.[PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers, Purchase_Orders.[Delivery Address],
Purchase_Orders.[Job No], Purchase_Orders.PaymentTerms, Purchase_Orders.Dept,
Purchase_Orders.Carriage, Purchase_Orders.[P/O Special Instructions],
Purchase_Orders.[Order placed by], Purchase_Orders.[Order Type?],
Purchase_Orders.[Green Copy Printed?], Purchase_Orders_Details.[Detail iD],
Purchase_Orders_Details.[PO Number] AS [Purchase_Orders_Details_PO Number],
Purchase_Orders_Details.[Item No], Purchase_Orders_Details.Quantity,
Purchase_Orders_Details.Denom, Purchase_Orders_Details.Description,
Purchase_Orders_Details.Price, Purchase_Orders_Details.[Price Per],
Purchase_Orders_Details.[Delivery Required], Purchase_Orders_Details.[Item
Complete], Company_Details.[Address Line 1], Company_Details.[Address Line
2], Company_Details.[Address Line 3], Company_Details.[Address Line 4],
Company_Details.[Address Line 5], Purchase_Orders.Supplier_Contact,
Orders.[Customer Name], Purchase_Orders.[Delivery Address], Products.Product,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.NSN,
Purchase_Orders_Details.Pattern
FROM Products INNER JOIN ((Company_Details INNER JOIN (Purchase_Orders INNER
JOIN Purchase_Orders_Details ON Purchase_Orders.[PO Number] =
Purchase_Orders_Details.[PO Number]) ON Company_Details.ID =
Purchase_Orders.Suppliers) INNER JOIN Orders ON Purchase_Orders.[Job No] =
Orders.OrderNumber) ON Products.Id = Purchase_Orders_Details.Product;
But I also need to get
Company_Details.[Address Line 1], Company_Details.[Address Line 2],
Company_Details.[Address Line 3], Company_Details.[Address Line 4],
Company_Details.[Address Line 5]
from Company_Details where the field Customer in Company_Details equals the
field Delivery_Address in Purchase_Orders.
Can the table Company_Details be queried twice like that? And if so, can
someone modify my SQL query to include the above.
/pulls hair out...
I have two tables, one called Purchase_Orders and one called Company_Details.
Company_Details contains all our customer's and supplier's addresses and
telephone numbers. Purchase_Orders allows stores the details of a purchase
order.
Within this purchase order, an address is looked up from Company_Details for
the address of who the purchase order is for. But I need to query
Company_Details for another address, the address the goods will be delivered
to.
At present my query is:
SELECT Purchase_Orders.[PO Number], Purchase_Orders.Date,
Purchase_Orders.Suppliers, Purchase_Orders.[Delivery Address],
Purchase_Orders.[Job No], Purchase_Orders.PaymentTerms, Purchase_Orders.Dept,
Purchase_Orders.Carriage, Purchase_Orders.[P/O Special Instructions],
Purchase_Orders.[Order placed by], Purchase_Orders.[Order Type?],
Purchase_Orders.[Green Copy Printed?], Purchase_Orders_Details.[Detail iD],
Purchase_Orders_Details.[PO Number] AS [Purchase_Orders_Details_PO Number],
Purchase_Orders_Details.[Item No], Purchase_Orders_Details.Quantity,
Purchase_Orders_Details.Denom, Purchase_Orders_Details.Description,
Purchase_Orders_Details.Price, Purchase_Orders_Details.[Price Per],
Purchase_Orders_Details.[Delivery Required], Purchase_Orders_Details.[Item
Complete], Company_Details.[Address Line 1], Company_Details.[Address Line
2], Company_Details.[Address Line 3], Company_Details.[Address Line 4],
Company_Details.[Address Line 5], Purchase_Orders.Supplier_Contact,
Orders.[Customer Name], Purchase_Orders.[Delivery Address], Products.Product,
Purchase_Orders_Details.TypeColourSize, Purchase_Orders_Details.NSN,
Purchase_Orders_Details.Pattern
FROM Products INNER JOIN ((Company_Details INNER JOIN (Purchase_Orders INNER
JOIN Purchase_Orders_Details ON Purchase_Orders.[PO Number] =
Purchase_Orders_Details.[PO Number]) ON Company_Details.ID =
Purchase_Orders.Suppliers) INNER JOIN Orders ON Purchase_Orders.[Job No] =
Orders.OrderNumber) ON Products.Id = Purchase_Orders_Details.Product;
But I also need to get
Company_Details.[Address Line 1], Company_Details.[Address Line 2],
Company_Details.[Address Line 3], Company_Details.[Address Line 4],
Company_Details.[Address Line 5]
from Company_Details where the field Customer in Company_Details equals the
field Delivery_Address in Purchase_Orders.
Can the table Company_Details be queried twice like that? And if so, can
someone modify my SQL query to include the above.
/pulls hair out...