Date Splitting for Other calculations

  • Thread starter Thread starter Roger
  • Start date Start date
R

Roger

Hi all,

I need help;
I have a field with a date entered in format dd/mm/yyyy, I
need to do caculations (sum) on the sales of a product
within each month in each year, eg (sumofsales)march 2003,
(sumofsales)april 2002 etc....
How do I do this, do I need to split the date into 3
separate pieces of information, group then sum?, or is
there another way.
I was thinking about pivot tables but there are other
calculations necessary on this info (eg: last 3 month
average, yearly average).
IThanks For any help provided.

Roger
Please provide answer by post or at email below
(e-mail address removed)
 
Create a report into this table.

In report design view, open the Sorting And Grouping dialog (View menu).

Enter the name of the date field. In the lower pane of the dialog, you can
specify the interval of Month, and say Yes to the Group Footer. Then in the
group footer you can sum the Sales field by placing a text box and setting
its ControlSource to:
=Sum([Sales])

(If you want to hide all the details for the month, you can set the Visible
property of the Detail section to No.)
 
Thanks, but I need all the months of last year and this
year to appear on the same page, is there something which
I can use eg: formula, to automatically get this months
sales, next column get last months, next column previous
month etc back 24 months.

Report will not do here unless can be done done by
unbound text box....

Thanks

Roger
-----Original Message-----
Create a report into this table.

In report design view, open the Sorting And Grouping dialog (View menu).

Enter the name of the date field. In the lower pane of the dialog, you can
specify the interval of Month, and say Yes to the Group Footer. Then in the
group footer you can sum the Sales field by placing a text box and setting
its ControlSource to:
=Sum([Sales])

(If you want to hide all the details for the month, you can set the Visible
property of the Detail section to No.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger said:
I need help;
I have a field with a date entered in format dd/mm/yyyy, I
need to do caculations (sum) on the sales of a product
within each month in each year, eg (sumofsales)march 2003,
(sumofsales)april 2002 etc....
How do I do this, do I need to split the date into 3
separate pieces of information, group then sum?, or is
there another way.
I was thinking about pivot tables but there are other
calculations necessary on this info (eg: last 3 month
average, yearly average).
IThanks For any help provided.

Roger
Please provide answer by post or at email below
(e-mail address removed)
Click to expand...


.
Click to expand...
 
It can be done at the query level as well

Type this into the Field row of your query:
TheYear: Year([SaleDate])
where "SaleDate" represents the name of your date field.

In the next column:
TheMonth: Month([SaleDate])

Depress the Total button on the toolbar (upper sigma icon).
Access adds a Total row to the grid, with "Group By" under the fields above.

Add your SaleAmount field to the grid.
In the Total row under this field, choose "Sum".

This gives you the sum or amount for each year + month.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Roger said:
Thanks, but I need all the months of last year and this
year to appear on the same page, is there something which
I can use eg: formula, to automatically get this months
sales, next column get last months, next column previous
month etc back 24 months.

Report will not do here unless can be done done by
unbound text box....

Thanks

Roger
-----Original Message-----
Create a report into this table.

In report design view, open the Sorting And Grouping dialog (View menu).

Enter the name of the date field. In the lower pane of the dialog, you can
specify the interval of Month, and say Yes to the Group Footer. Then in the
group footer you can sum the Sales field by placing a text box and setting
its ControlSource to:
=Sum([Sales])

(If you want to hide all the details for the month, you can set the Visible
property of the Detail section to No.)

Roger said:
I need help;
I have a field with a date entered in format dd/mm/yyyy, I
need to do caculations (sum) on the sales of a product
within each month in each year, eg (sumofsales)march 2003,
(sumofsales)april 2002 etc....
How do I do this, do I need to split the date into 3
separate pieces of information, group then sum?, or is
there another way.
I was thinking about pivot tables but there are other
calculations necessary on this info (eg: last 3 month
average, yearly average).
IThanks For any help provided.

Roger
Please provide answer by post or at email below
(e-mail address removed)
Click to expand...
Click to expand...
Click to expand...
 
Back
Top