Query the same table twice within one query? Possible?

G

Guest

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...
 
S

Steve Schapel

Richard,

Yes, there is no problem about doing this. Do it in the query design
view. Just add the Company_Details table a second time to the query,
and drag/drop to join it to the Purchase_Orders table on the relevant
fields. Access will automatically alias the second instance of the
table as Company_Details_1, but it might be good to change this. If you
right-click on the table in the upper panel of the query designer, and
then select Properties, you will see an Alias property. You could
change this to Delivery or whatever you feel appropriate. Then have a
look at the SQL to see how it works if you like.

--
Steve Schapel, Microsoft Access MVP


Richard said:
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...
Click to expand...
 
G

Guest

Steve, it's amazing how easy something is when you know how isn't it?

Can't believe it was so straight forward.

Thanks a lot mate. It's much appreciated.

Steve Schapel said:
Richard,

Yes, there is no problem about doing this. Do it in the query design
view. Just add the Company_Details table a second time to the query,
and drag/drop to join it to the Purchase_Orders table on the relevant
fields. Access will automatically alias the second instance of the
table as Company_Details_1, but it might be good to change this. If you
right-click on the table in the upper panel of the query designer, and
then select Properties, you will see an Alias property. You could
change this to Delivery or whatever you feel appropriate. Then have a
look at the SQL to see how it works if you like.

--
Steve Schapel, Microsoft Access MVP


Richard said:
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...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

My query only shows results where all fields are have data entered 1
Why can't I update this query 18
Update query. Within the same table: populating field 2 from field 6
query with twice the same table, and the trouble! 3
Help with WHERE clause in query selecting from queries... 2

Top