Access

R

Roger

Iam using date fields and corresponding amounts in a form.
I have a commencement date that is the first Sunday in
each year that updates each financial year and then 53
date fields and amount fields that correspond to the
number of weeks in the year. When I type a new year
commencement date the other date fields automatically
calculate with increments of 7.
My problem is trying to obtain monthly totals on a
report. This works for the first year but goes wrong when
the year changes and some months have 5 Sundays.
Can anyone advise how I can tell the system to adjust the
monthly totals when the number of Sundays in a particular
month changes from year to year. Thank you to anyone who
can help. Maybe my design is all wrong I don't know!
 
L

Larry Linson

Why does the number of Sundays enter into the calculation?

Format the dates as Format(yourdate,"Mmm") to get the month and group on
month number (use a join to get/display Monthname from a lookup table), then
sum each group.

Larry Linson
Microsoft Access MVP
 
M

Mike Painter

Roger said:
Iam using date fields and corresponding amounts in a form.
I have a commencement date that is the first Sunday in
each year that updates each financial year and then 53
date fields and amount fields that correspond to the
number of weeks in the year. When I type a new year
commencement date the other date fields automatically
calculate with increments of 7.
My problem is trying to obtain monthly totals on a
report. This works for the first year but goes wrong when
the year changes and some months have 5 Sundays.
Can anyone advise how I can tell the system to adjust the
monthly totals when the number of Sundays in a particular
month changes from year to year. Thank you to anyone who
can help. Maybe my design is all wrong I don't know!
Click to expand...

The design is wrong. Your table needs one date (entered) field and one
amount field.
If these are for different people etc, then you would need the ID of that
person.
Ther should be a key field.

With that you can generate reports that sum by week, quarter, year, date
entered etc.
A query would be used to give calculated fields that format the date to week
number, etrc.
 
Top