CODEWITHSUNDEEP
sqlms-access
ogbainzy
ogbainzy

Reputation: 11

Access shows duplicates in query

I have a query that calculates a total price and then determines whether a gift card should be given or not. The table shows as:

Query Results

Design View

SELECT
    Customer.First_Name,
    Customer.Last_Name,
    Order.Order_ID,
    Sum([Order Items].Quantity) AS [Total Items],
    Sum(([Quantity]*[Price])) AS [Total Price],
    IIf([Quantity]*[Price]>150,"Yes","No") AS [Gift Card]
FROM (Customer INNER JOIN [Order]
    ON Customer.Customer_ID = Order.Customer_ID)
INNER JOIN (Item INNER JOIN [Order Items]
    ON Item.Item_ID = [Order Items].Item_ID)
    ON Order.Order_ID = [Order Items].Order_ID
GROUP BY
    Customer.First_Name,
    Customer.Last_Name,
    Order.Order_ID,
    IIf([Quantity]*[Price]>150,"Yes","No");

It shows a duplicate field, how can I get rid of the duplicate field?

Upvotes: 1

Views: 100

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520988

Try removing the yes/no label from your grouping. This would guarantee that a given first/last name and order would always appear a single record in your result set.

SELECT
    Customer.First_Name,
    Customer.Last_Name,
    Order.Order_ID,
    SUM([Order Items].Quantity) AS [Total Items],
    SUM([Quantity]*[Price]) AS [Total Price],
    IIF(SUM([Quantity]*[Price]) > 150, "Yes", "No") AS [Gift Card]
FROM (Customer INNER JOIN [Order]
    ON Customer.Customer_ID = Order.Customer_ID)
INNER JOIN (Item INNER JOIN [Order Items]
    ON Item.Item_ID = [Order Items].Item_ID)
    ON Order.Order_ID = [Order Items].Order_ID
GROUP BY
    Customer.First_Name,
    Customer.Last_Name,
    Order.Order_ID;

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

As per data model you could do group by by the customerid and orderid

select c.customer_id, oi.order_id, SUM(oi.Qty) as TotalQty, SUM(oi.Qty*i.Price) as TotalPrice,
       IIF(SUM(oi.Qty*i.Price) > 150, 'Yes', 'No') as GiftApplicable
from customer c
inner join order o in o.customer_id = c.customer_id
inner join order_item oi on oi.order_id = o.order_id
inner join item i on i.item_id = oi.item_id
group by c.customer_id, oi.order_id

Upvotes: 0

Related Questions