# Revenue and volume by type number

I have an access 360 data base of about 10000 orders.  Each record (order) shows the type number ordered, the quantity and total price. Only one type number per order.   How can I make a query which shows the total revenue and quantity by type number?
Microsoft Access

Last Comment
PatHartman

8/22/2022 - Mon
Shaun Kline

Is revenue something other than Quantity * Price?

If you want to run this across all orders, you would use the above calculation and group by Type Number, excluding all other columns.
Brian Sowter

Could you please get me started with groups.  I already have revenue as a field. I have generated a table of type numbers
Gustav Brock

That could be a query like:

``````Select
[type number],
Sum([quantity]),
Sum([revenue])
From
YourTable
Group By
[type number]
``````
Brian Sowter

CAN I DO THIS WITH CROSSTAB?
Scott McDaniel (EE MVE )

You can use GROUP BY and various aggregate functions to create a query. For example, to SUM by Quantity over a specific Group:

SELECT SUM(SomeField) FROM YourTable GROUP BY YourGroupField

In your case, if you want to sum the quantity:

SELECT [Type Number], SUM(Quantity) FROM YourOrdersTable GROUP BY [Type Number]

To get the Revenue:

SELECT [Type Number],  SUM(Quantity * UnitPrice) FROM YourOrdersTable GROUP BY [Type Number]

Of course you'd have to change Table and Column names to match your project.
Brian Sowter

Hi Gustav
Forgive my ignorance...Does this mean I must write the SQL code rather than use the Query designer?
Gustav Brock

That's up to you.
Open a new query, go to SQL view, paste the code, and edit it to match you table and field names.
Then you can switch to the design view to study.
Brian Sowter

This is my code:

SELECT SUM([Qty])  FROM Query1 GROUP BY [Type]

SELECT [Type], SUM([Qty]) FROM Query1 GROUP BY[Type]

SELECT [Type],  SUM([Value] FROM Query1 GROUP BY [Type]

I get  "Syntax error in query expression '[Type] SELECT [Type]" after saving
Gustav Brock

You can only have one Select in a normal query.
See my query above.