Query, sum, avg, group by month for 12 months

[AccessExcelHell]

Hello,

I need to create a query that will look at data in a table and perform count, sum and avg functions (which i know how to setup) butthen i need this dat per month. I can only get access to do 1 date range. I need help so that i can get the results for each month in the year.

Ex. Table

ID, name, amount, date
1, paul, 23, 6/1/03
3, bob, 56 6/3/03
7, bob, 4, 6/9/03
99, paul, 11, 7/1/03

E. Output

Name June July
Count Sum Avg Count Sum Avg
Paul 1 23 23 1 11 11
bob 2 60 30 0 0 0


Can anyone help? thanks

 

[DBAMJA]

In your query set a field to this (I am assuming that you are using XP)

Month_Sort:Month_Name(Month(Date))

Be sure that the field is set to group by and you will be all set.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[AccessExcelHell]

Hi,

I tried it but it only created a field witht he month number for me. I am running Windows 2000 Prof, not XP.

 

[MichaelRed]

The following provides the results, not the format

SELECT tblTransact.IdName, Format([IdDt],"mmm") AS Mnth, Count(tblTransact.Amt) AS [Count], Sum(tblTransact.Amt) AS [Sum], Avg(tblTransact.Amt) AS Average
FROM tblTransact
GROUP BY tblTransact.IdName, Format([IdDt],"mmm")
WITH OWNERACCESS OPTION;

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[DBAMJA]

Lets give this a try:

Put the following in a module:

Function Convert_Month(Bill_Month As String)
Dim db As Database
Dim tmpMonth As String

Select Case Bill_Month
Case 1
tmpMonth = "January"
Case 2
tmpMonth = "February"
Case 3
tmpMonth = "March"
Case 4
tmpMonth = "April"
Case 5
tmpMonth = "May"
Case 6
tmpMonth = "June"
Case 7
tmpMonth = "July"
Case 8
tmpMonth = "August"
Case 9
tmpMonth = "September"
Case 10
tmpMonth = "October"
Case 11
tmpMonth = "November"
Case 12
tmpMonth = "December"
Case Else
tmpMonth = "No Transactions"
End Select

Convert_Month = tmpMonth

End Function


Then in your query use the following to call the function:

Month_Name:Convert_Month(Month(Date))

Set this field to group by and you should get your data grouped by the month name.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[AccessExcelHell]

Hi,

Both of your ideas worked to help me get the data, but now my problem is the format. I tried to use the Access report but could not get it to display properly. I tried to use the crosstab query, but that will only let me put 1 field's values. I can get the data by Month, but can only get the sum or the count or avg, but not all.

Any ideas?

Many thanks.

 

[hindsjl]

You can do a "manual crosstab", creating a permanent column for each month using IIF() formulas. In your case, I think each month will need two such columns, one for the count and one for the amount.

ID,
Name,
Jan_Count: sum(iif(month(date)=1,1,0)),
Jan_Amt: sum(iif(month(date)=1,Amount,0)),
...
Dec_Count: sum(iif(month(date)=12,1,0)),
Dec_Amt: sum(iif(month(date)=12,Amount,0))

That's a lot of columns to define, but it has worked. As for the Averages, you might have to call this first query from a second one to divide the Amt columns by the Count columns.


Regards,
John Hinds

 

[AccessExcelHell]

hindsjl,

The iif command i my new best friend.

Many thanks.

 

[JonFer]

You can create a query for each aggregate function you want to perform. Add a field to each query called "AggFx" which will hold values of "Count", "Sum", etc. specific to each query.

Then Union these queries and the result will be a row for each month for each aggregate result.

In a crosstab query based on the Union, make the "Column" field something like:
ColHeading: Format([Datexx],"mmm&quot & "-" & [AggFx]

You should get results like "Jan-Sum", "Jan-Count", etc. If you want the full month name use "mmmm" with the Format function. The field order can be controlled by manually setting the column headings in the crosstab query properies (paste a row of the results to Excel to get the column headings and save yourself some typing).