?
Solved

sum a column of data in access by month

Posted on 2008-02-09
16
Medium Priority
?
708 Views
Last Modified: 2010-04-21
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?
0
Comment
Question by:Dier02
  • 8
  • 6
  • 2
16 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20857060
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
 

Author Comment

by:Dier02
ID: 20859794
comes up with a syntax error on "from"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20860226
post the query you are using.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Dier02
ID: 20860267
select year([date]) & "-" & month([date]) as yrMonth, sum([cost] from test
group year([date]) & "-" & month([date])
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20861109
sorry about that

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

Author Comment

by:Dier02
ID: 20863419
I run a query but still come up with a syntax error on "from" when I try to save it using the above.
0
 

Author Comment

by:Dier02
ID: 20863421
I am using Access 2003
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20863432
ok missing ")"  at ([cost]

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

Author Comment

by:Dier02
ID: 20864425
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 20865819
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
 

Author Comment

by:Dier02
ID: 20881852
Date/Time
0
 

Expert Comment

by:rohitsinha
ID: 20895246
try using dsum function
0
 

Author Comment

by:Dier02
ID: 20897001
how do I do that - my table is Administration and my fields are cost and Date
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 20897043
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
 

Expert Comment

by:rohitsinha
ID: 20897125
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
 

Author Closing Comment

by:Dier02
ID: 31429440
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question