CODEWITHSUNDEEP
sqlms-access
mmenschig
mmenschig

Reputation: 1128

MS Access SQL Query - Joining 3 Tables to receive Total Order Price

I would like to know the total price for a specific order ID. The information is contained in these tables:

Order_line_t

Order_ID    Product_ID    Quantity
--------    ----------    --------
1006        4             1
1006        5             2
1006        7             2

Uses_t

Product_ID    Material_ID    Footage
----------    -----------    -------
4             16             20
5             15             13
7             10             16

Raw_Materials_t

Material_ID    Unit_Price
-----------    ----------
16             05.70
15             16.72
10             15.55

The total cost is computed by 

Multiplying SUM ( Order_line_t.Quantity * Uses_t.Footage * Raw_Materials_t.Unit_Price) AS Total

My current query only returns an error:

SELECT Order_line_t.Order_ID, Order_line_t.Product_ID, Uses_t.Product_ID   ,Uses_t.Footage , 
SUM ( Order_line_t.Quantity * Uses_t.Footage * Raw_Materials_t.Unit_Price) AS Total
FROM Order_line_t, Uses_t , Raw_Materials_t
WHERE  Order_line_t.Order_ID = 1006;

Help appreciated! How exactly do I JOIN these tables? What am i missing?

Upvotes: 2

Views: 3880

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166486

Firstly you were missing the actual joins between the tables, and secondly you were missing your group by 

SELECT  Order_line_t.Order_ID, 
            Order_line_t.Product_ID, 
            Uses_t.Product_ID   ,
            Uses_t.Footage , 
            SUM ( Order_line_t.Quantity * Uses_t.Footage * Raw_Materials_t.Unit_Price) AS Total
FROM    Order_line_t  INNER JOIN
            Uses_t  ON  Order_line_t.Product_ID = Uses_t.Product_ID INNER JOIN
            Raw_Materials_t     ON  Uses_t.Material_ID = Raw_Materials_t.Material_ID
WHERE   Order_line_t.Order_ID = 1006;
    GROUP BY Order_line_t.Order_ID, 
            Order_line_t.Product_ID, 
            Uses_t.Product_ID   ,
            Uses_t.Footage

EDIT

Try this

SELECT  Order_line_t.Order_ID, 
        Order_line_t.Product_ID, 
        Uses_t.Product_ID, 
        Uses_t.Footage, 
        Sum([Order_line_t].[Quantity]*[Uses_t].[Footage]*[Raw_Materials_t].[Unit_Price]) AS Total
FROM    (Order_line_t INNER JOIN 
        Uses_t ON Order_line_t.Product_ID = Uses_t.Product_ID) INNER JOIN 
        Raw_Materials_t ON Uses_t.Material_ID = Raw_Materials_t.Material_ID
GROUP BY    Order_line_t.Order_ID, 
            Order_line_t.Product_ID, 
            Uses_t.Product_ID, 
            Uses_t.Footage;

EDIT

Total by ORDER ID

SELECT  Order_line_t.Order_ID,
        Sum([Order_line_t].[Quantity]*[Uses_t].[Footage]*[Raw_Materials_t].[Unit_Price]) AS Total
FROM    (Order_line_t INNER JOIN 
        Uses_t ON Order_line_t.Product_ID = Uses_t.Product_ID) INNER JOIN 
        Raw_Materials_t ON Uses_t.Material_ID = Raw_Materials_t.Material_ID
GROUP BY    Order_line_t.Order_ID;

Upvotes: 1

ExactaBox
ExactaBox

Reputation: 3395

I think you need to compute the cost for each row, then sum the entire order. Something like:

select 
    Order_ID, sum(RowCost) as OrderCost

from (

    select
        o.Order_ID, o.Product_ID, o.Quantity,
        u.Material_ID, u.Footage,
        r.Unit_Price,
        (o.Quantity * u.Footage * r.UnitPrice) as RowCost

    from
        order_line_t o INNER JOIN
        uses_t u on o.Product_ID = u.Product_ID INNER JOIN
        Raw_materials_t r on u.Material_ID = r.Material_ID

    where
        o.Order_ID = 1006

    ) z

group by
    Order_ID

Upvotes: 0

Related Questions