CODEWITHSUNDEEP
sqlms-access
Access-Newbie
Access-Newbie

Reputation: 11

Need SQL query in Access to Show Total Sales by Catalog#

enter image description here

I have a table named Sales_Line_Items. In it I have the following fields; order#, Catalog#, Whole_Sale_Price, Qty_Ordered. I have created a query using this table that sums up the Qty_Ordered field and returns a Revenue field with a total per order line. What I need is a query that shows me each the quantity of each item sold and the total sales by item. This is the query I wrote for the total revenue:

    SELECT 
        Sales_Line_Items.[Order#], 
        Sales_Line_Items.[Catalog#], 
        Sales_Line_Items.Wholesale_Price, 
        Sum(Sales_Line_Items.Qty_Ordered) AS SumOfQty_Ordered, 
        Sum(Sales_Line_Items.[Qty_Ordered]*Sales_Line_Items.[Wholesale_Price]) AS Revenue
    FROM Sales_Line_Items
    GROUP BY 
        Sales_Line_Items.[Order#], 
        Sales_Line_Items.[Catalog#], 
        Sales_Line_Items.Wholesale_Price;

The result was a new field with header ' Revenue'. The other fieds listed are Order#, Catalog#, Wholesale_Price, and SumofQty_Ordered fields. This is fine, if I want to know what every sales line totals up to within each order. But I want to show the total sold by Catalog# so that I can view each product sales total.

enter image description here

Upvotes: 0

Views: 350

Answers (1)

Access-Newbie
Access-Newbie

Reputation: 11

I was able to figure this out by adding the Totals line in Design View, and grouping by Expression in the query field and leaving the Catalog# field alone. The query I used looks like this;

Total: Sum([Sales_Line_Items]![Wholesale_Price]*[Sales_Line_Items]![Qty_Ordered])

Upvotes: 1

Related Questions