summing daily values, Urgent

[mahmad]

Hi,

I want to be able to sum by daily amounts and then sum them up so i get a
total for the month gone. So yesterdays figures will be added to the day
before etc and summed up.

Amount Date Team
------- ------- --------
200 09/03/08 Blue
100 10/03/08 Blue
200 11/03/08 Green

Total 300 for Blue and 200 for Green in March so far then tomorrow it will
add what ever value Amount
is and so on for the whole month. Next month will then start from scratch.

Thanks
MO

 

[Stockwell43]

Hi Mahmad,

If I understand you correctly, you should do this in a report. Create a
report and click on the Group/Sort button and select Team. On the bottom
portion select group header yes and group footer yes.

Move the Team text box to the group header in the details put the date field
and in the Group/Sort box select that as your second sort but don't group it.
Also, place the Amount field next to it.

Now in your footer, make a copy of the amount field and paste it in the
footer you created for Teams. Open the fields properties and in the Control
Source try this:
=Sum([Amount]) this is of course assuming the Amount field is named Amount.
When you run the report, the top left side should show your Team Example
Blue, with all the records for blue and the dates they were inputted and the
amounts by date order. Then at the end of each color you should see your
total.

There may be other options of doing this, I just like using report to show
my data pulled from the queries because it's cleaner and more presentable.

How this helps!!

 

[John W. Vinson]

Hi,

I want to be able to sum by daily amounts and then sum them up so i get a
total for the month gone. So yesterdays figures will be added to the day
before etc and summed up.

Amount Date Team
------- ------- --------
200 09/03/08 Blue
100 10/03/08 Blue
200 11/03/08 Green

Total 300 for Blue and 200 for Green in March so far then tomorrow it will
add what ever value Amount
is and so on for the whole month. Next month will then start from scratch.

Thanks
MO

SELECT Team, Sum([Amount])
FROM yourtable
WHERE [Date] >= DateSerial(Year(Date()), Month(Date()), 1);

will display the sums by team for the current month.

I would really recommend changing the name of the Date field. It's a reserved
word and Access can and will confuse it with the Date() builtin function.

 

[Marshall Barton]

Hi,

I want to be able to sum by daily amounts and then sum them up so i get a
total for the month gone. So yesterdays figures will be added to the day
before etc and summed up.

Amount Date Team
------- ------- --------
200 09/03/08 Blue
100 10/03/08 Blue
200 11/03/08 Green

Total 300 for Blue and 200 for Green in March so far then tomorrow it will
add what ever value Amount
is and so on for the whole month. Next month will then start from scratch.

I think this could be what you want.

SELECT Format(datefield, "m/yy"), Team, Sum(Amount)
FROM thetable
GROUP BY Format(datefield, "m/yy"), Team

 

[mahmad]

Hi,

ive tried adding your select statement and when i run it, i just get the
daily amount rather a commulative figure for the month. My complete query is
below.

SELECT dbo_bsp_view_orders_asmbdbyteam.TEAM,
Sum(dbo_bsp_view_orders_asmbdbyteam.PRICE) AS SumOfPRICE
FROM dbo_bsp_view_orders_asmbdbyteam
WHERE
(((dbo_bsp_view_orders_asmbdbyteam.fpfindat)>=DateSerial(Year(Date()),Month(Date()),1)))
GROUP BY dbo_bsp_view_orders_asmbdbyteam.TEAM;

Hi,

I want to be able to sum by daily amounts and then sum them up so i get a
total for the month gone. So yesterdays figures will be added to the day
before etc and summed up.

Amount Date Team
------- ------- --------
200 09/03/08 Blue
100 10/03/08 Blue
200 11/03/08 Green

Total 300 for Blue and 200 for Green in March so far then tomorrow it will
add what ever value Amount
is and so on for the whole month. Next month will then start from scratch.

Thanks
MO

SELECT Team, Sum([Amount])
FROM yourtable
WHERE [Date] >= DateSerial(Year(Date()), Month(Date()), 1);

will display the sums by team for the current month.

I would really recommend changing the name of the Date field. It's a reserved
word and Access can and will confuse it with the Date() builtin function.