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?
Brian SowterTechnical DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
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 SowterTechnical DirectorAuthor Commented:
Could you please get me started with groups.  I already have revenue as a field. I have generated a table of type numbers
Gustav BrockCIOCommented:
That could be a query like:

    [type number],
Group By
    [type number]

Open in new window

5 Ways Acronis Skyrockets Your Data Protection

Risks to data security are risks to business continuity. Businesses need to know what these risks look like – and where they can turn for help.
Check our newest E-Book and learn how you can differentiate your data protection business with advanced cloud solutions Acronis delivers

Brian SowterTechnical DirectorAuthor Commented:
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 SowterTechnical DirectorAuthor Commented:
Hi Gustav
Forgive my ignorance...Does this mean I must write the SQL code rather than use the Query designer?
Gustav BrockCIOCommented:
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 SowterTechnical DirectorAuthor Commented:
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 BrockCIOCommented:
You can only have one Select in a normal query.
See my query above.
If you are not familiar with SQL, it is best to start with the query designer.
Create a new query.
Select the source table or query from the dialog (you can select more than one if you need to join multiple tables/queries)
Once all the tables/queries are on the grid, draw the join lines to connect them.
Select the columns you need from each table.
To convert the select query to a totals query, press the big sigma button.  Access will populate all the selected columns with "Group By"  Leave Type as Group By but change the qty and value to Sum.
Save the query with a meaningful name

PS - The Crosstab wizard will summarize the data for you.  However, you can only pivot ONE column at a time.  So you can make a crosstab that pivots qty and a second one that pivots value.  You would then have to create a third query to join the two crosstabs if you wanted a report that shows both values on the same line.  From what I understand of your problem, I don't think you need a crosstab.  The totals query will return one row for each type with the sum of qty and the sum of value on the same line.
Run it.

If you want to look at the SQL, switch the view to SQL view.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brian SowterTechnical DirectorAuthor Commented:
Thanks to everybody who helped.  It is working well now!
You're welcome.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.