Count records for each month per ID for Access report

After a couple of days of trying to figure this out, I decided to ask the experts here.

I have two tables Customer and Orders. Access 2003 interface with SQL 2008 database.
Customers:
CustomerID (int) Primarykey
CustomerName (string)
FrequentCustomer (bit)

Orders:
OrderID (int) Primarykey
DateSubmited (smalldate)
CustomerID

The two tables are related using the CustomerID field. What I want to do is generate a report that will show a count of orders received per frequent customer per month. I would like to prompt the user to enter the current month and year when the report is started. The output needs to look like this:
Customer    April     March    Feburary  January December  November
John Smith     5           3              10           12            3                 0
Joe someone 4           2                4             0            0                 0

I can't figure out how to build a query that will accomplish this. It seems like I have to build a query and then maybe use some vba to do the counting, I am not sure.

Any help would be appreciated.
ree0082Asked:
Who is Participating?
 
answer_dudeConnect With a Mentor Commented:
Now you get to have fun with Dates... :-)  Try this... it's not exactly what you want but you'll get the idea.  You can play with the date format in the statement: Format([DateSubmitted],"yyyy-mm (mmmm)")

PARAMETERS OrdersFrom DateTime;
TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Customers.CustomerName, Count(Orders.OrderID) AS RowTotal
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.DateSubmitted)>=[OrdersFrom]))
GROUP BY Customers.CustomerName
PIVOT Format([DateSubmitted],"yyyy-mm (mmmm)");
0
 
answer_dudeCommented:
Use a crosstab query with a parameter (Menu - Query - Parameters...) on the date field.
0
 
answer_dudeCommented:
Here's the SQL:

PARAMETERS OrdersFrom DateTime;
TRANSFORM Count(Orders.OrderID) AS CountOfOrderID
SELECT Customers.CustomerName
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.DateSubmitted)>=[OrdersFrom]))
GROUP BY Customers.CustomerName
PIVOT Orders.DateSubmitted;
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ree0082Author Commented:
That is much closer then anything that I have tried. At least it groups them by customer name like I want it to. The problem now is it breaks it down by each date entered.
Example:
Customer     3/1/2010    3/2/2010    3/9/2010    4/1/2010    4/9/2010 8:48am   4/9/2010 9:30am
John Smith         1                1                 0              0                     1                            0
Joe Someone     0                0                 2              1                     0                            2

How would I get it to group the months together in one column, like this:

Customer       March         April
John Smith         2                1
Joe Someone     2                3
0
 
ree0082Author Commented:
That did it. I actually changed the last line to PIVOT Month([DateSubmitted]); and that worked great. I am going to modify this to do a date range instead so I don't have to worry about the year's getting combined.

Thanks so much for your help.
0
 
ree0082Author Commented:
Awesome thanks.
0
 
answer_dudeCommented:
Perfect!  Have fun with it!
0
All Courses

From novice to tech pro — start learning today.