Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Query the same table twice within one query? Possible?

Discussion in 'Information Technology' started by Richard Horne, Nov 8, 2005.

  1. Richard Horne

    Richard Horne
    Expand Collapse
    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...
     
  2. Loading...

    Similar Threads Forum Date
    Query about Jhatka Meat by Shooting in Head Sikh Sikhi Sikhism Aug 26, 2011
    Who is a sikh? A non sikh friend's query!! Sikh Sikhi Sikhism Apr 30, 2010
    General Query Hard Talk Sep 4, 2008
    Power of pauri's in Japji Sahib query Sikh Sikhi Sikhism Aug 17, 2006
    Sikhism a query Book Reviews & Editorials Aug 2, 2005

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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 Horne wrote:
    > 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...
    >
     
  4. Richard Horne

    Richard Horne
    Expand Collapse
    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" wrote:

    > 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 Horne wrote:
    > > 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...
    > >

    >
     

Share This Page