Counting unique records by month

Question

I'm a newbie to Access 2010. I have a table:

ID  Mth OrderID Net Sales
1   1   3   36
2   1   2   12
3   1   2   20
4   2   1   10

I'd like to get a summary by Mth of the OrderID count, Quantity of those orders, and Net Sales of the those orders:

Mth Ordercount  Quantity    Net Sales
1   2   7   68
2   1   1   10

Is there a way to do this?

I'd also like to convert Mth = 1 into Month = Jan 2013 but have it list in date order, rather than alphabetically.

Mth
Jan 2013
Feb 2013

How do I do that?

So far, I've only been working with the design view and have not using an SQL code.

Welcome to SO! Please take a minute and learn how to format your questions (and hopefully answers, too) to make them easier to read. I’ve done that here, please check that I did not break anything. Also, tagging is meant to help others quickly find questions they can help with, which is why it’s a good idea to always include the programming language or tool you are using. — Christopher Creutzig, Feb 12 '14 at 19:58

score 0 · Answer 1 · edited May 23 '17 at 11:49

This can be done mostly in the design viewer of access although it would require creating more than one query and using those as a source instead of a table or you could write a sub select in sql code.

For your first question you will need to perform a distinct count on order id's based on month. This question answers the same problem and will provide the output you need.

Once you have a query that provides the number of orders per month you can create a new query that joins the table and your query on month with Net Sales as a total field. Where is quantity coming from in your source data?

To display the month number as month access has a MonthName function you can use. You can add 2013 to this by adding & " 2013" to the end of the expression.

You can sort on month by adding your month field a second time for the sorting but uncheck show box.

Counting unique records by month

1 Answers1