how to take monthly and yearly report (1 Viewer)
- Thread starter Mark1982
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 18:40
- Joined
- May 7, 2009
- Messages
- 19,233
you can create a query and based on that query create a report.
for monthly you need to include the Month and Year to output.
for yearly just the Year.
your table should have a date/time field in order to do this.
say your table (table1) has a date/time field (transactDate), then your query will look
like:
select * from table1 Where Month(transactDate)=8 And Year(transactDate)=2018;
if you need totals (sum or count):
select field1, field2, sum(amountField) As TotalAmount from table1 group by field1, field2;
there are plenty of example, but you have to tell us what you need report, your table name, structure, etc.
for monthly you need to include the Month and Year to output.
for yearly just the Year.
your table should have a date/time field in order to do this.
say your table (table1) has a date/time field (transactDate), then your query will look
like:
select * from table1 Where Month(transactDate)=8 And Year(transactDate)=2018;
if you need totals (sum or count):
select field1, field2, sum(amountField) As TotalAmount from table1 group by field1, field2;
there are plenty of example, but you have to tell us what you need report, your table name, structure, etc.
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 18:40
- Joined
- May 7, 2009
- Messages
- 19,233
you add Sort on your report. sort by [date]
then add Group.
Group By (expression): Year([Date] & Format(Month([Date],"mm")
on the Group Header, drag a TextBox.
on its control source: =Format([date],"mmmm") & " " & Year([Date])
on this Group Footer for this Group
drag a textbox to the Group Footer (sum for all invoice for the month).
on its controlsource: = sum([invoice amount])
on its Property Sheet->Data->Running Sum = No
on the Report Footer, drag a textbox (for overall total)
on its controlsource: = sum([invoice amount)
if you are unable or having a hard time you can always upload a zip copy of your db.
then add Group.
Group By (expression): Year([Date] & Format(Month([Date],"mm")
on the Group Header, drag a TextBox.
on its control source: =Format([date],"mmmm") & " " & Year([Date])
on this Group Footer for this Group
drag a textbox to the Group Footer (sum for all invoice for the month).
on its controlsource: = sum([invoice amount])
on its Property Sheet->Data->Running Sum = No
on the Report Footer, drag a textbox (for overall total)
on its controlsource: = sum([invoice amount)
if you are unable or having a hard time you can always upload a zip copy of your db.
Similar threads
