sum a column of data in access by month

I want to sum a column of data (money) which is in a table called "test" and the column heading is "cost".
I want to be able to do this by month using the "date" column in the query as well.  Could you show me how to sum a column and then sum the column by date?
Dier02Asked:
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.

Rey Obrero (Capricorn1)Commented:
select month([date]) as mMonth, sum([cost] from test
group by month([date])


better if you include the year

select year([date]) & "-" & month([date]) as yrMonth, sum([cost] from test
group year([date]) & "-" & month([date])

0
Dier02Author Commented:
comes up with a syntax error on "from"
0
Rey Obrero (Capricorn1)Commented:
post the query you are using.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Dier02Author Commented:
select year([date]) & "-" & month([date]) as yrMonth, sum([cost] from test
group year([date]) & "-" & month([date])
0
Rey Obrero (Capricorn1)Commented:
sorry about that

select year([date]) & "-" & month([date]) as yrMonth, sum([cost] from test
group by year([date]) & "-" & month([date])
0
Dier02Author Commented:
I run a query but still come up with a syntax error on "from" when I try to save it using the above.
0
Dier02Author Commented:
I am using Access 2003
0
Rey Obrero (Capricorn1)Commented:
ok missing ")"  at ([cost]

select year([date]) & "-" & month([date]) as yrMonth, sum([cost]) from test
group by year([date]) & "-" & month([date])
0
Dier02Author Commented:
Thats asking for a date buit then just gives a total but it doesn't make sense in the year mth?
yrMonth       Expr1001
1905-6                      921.3
0
Rey Obrero (Capricorn1)Commented:
you have to check your data from the table.. inspect the  [date] field, is it set as Date/time type of data? or text?

0
Dier02Author Commented:
Date/Time
0
rohitsinhaCommented:
try using dsum function
0
Dier02Author Commented:
how do I do that - my table is Administration and my fields are cost and Date
0
Rey Obrero (Capricorn1)Commented:
are you not getting the result you want from this query?

select year([date]) & "-" & month([date]) as yrMonth, sum([cost]) from test
group by year([date]) & "-" & month([date])



0

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
rohitsinhaCommented:
DSum("[Cost]", "Test",  "[Date] <put condition>)

Also, I like Capricorn's answer, to create a grouping by month you can format your date into mm-yyyy using datepart function DatePart("m",[date]) & "/" & DatePart("yyyy",[date])
If you are trying to create a sub-total (sum by month) field and grand total (grand total) field in your query then you can use datepart for sub total and dsum for grand total


0
Dier02Author Commented:
this works but I want it to link to two comboboxes so that one shows the year/date and the other shows the amount
0
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.