Reports In Access 97
by
staplek2
·
about 22 years, 1 month ago
In reply to Reports In Access 97
Two excellent answers, but if you want to report ALL of your data and want Access to separate it into months for you, then you need one of two things; VB coding that converts your dates OR a date conversion table that defines them.
I can’t help with the VB but I use the following successfully.
Create a new table, three fields;[Date][Month] and [Year]
[Date] field: enter dates for every day of the year (cur yr, prev yrs, future yrs..whatever affects you)
[Month] field: type literal month value for the assoc date
[Year] field: assign a literal value for the assoc date’s year
(Do this in a spreadsheet, it’s easier. mine has 5 yrs)
Create a new query. Add your Huge table and the date conv table. Create a join btwn the two using date fields. (join from the conv table’s date field to the Huge table’s date field.)
Drag Month and Year fields from the conv table into the query. Then drag all fields from Huge table into it.
Under Month field, in criteria row, type [Enter Month]. In the Or row, under the Year field, type [Enter Year]
When the query runs you’re prompted to enter either a month or a year. Default through the one you don’t want to use.
Now you need to make this query the Record Source for the report. Add the fields Year and Month as grouping levels in your report. Adding Headers and/or Footers will allow you complete control of how Access groups your data.
You can build smaller tables to define the months as needed. (i.e. Jan thru Mar could = First quarter.)
Hope this helps. Questions? let me know.