Software·43,749 discussions

Reports In Access 97

Locked

I have created a huge table in Access 97. This table has information sorted through date. I need to create a report that will list ALL the records entered for one particular month. I can create a report with all the records in it. But how can I create a report that ONLY looks at the month of January. So basically, I need to cut the whole table down into 12 different reports for each month. They are all sorted by the date. I am guessing it has something to do with the grouping, but I don’t know how to use it effectively for this problem.

Topic

Reports In Access 97

In reply to Reports In Access 97

Create a report based on a query. The query will contain all the fields for the report. Then in the Criteria line put the entry [Month?]. When you open the report it will ask for and then produce a report based on that month. The criteria might be Between 1/1/00 And 1/31/00 if you don’t have an entry for “January”

Reports In Access 97

In reply to Reports In Access 97

The question was auto-closed by TechRepublic

Reports In Access 97

In reply to Reports In Access 97

Base your report on a query and in the “Criteria:” line of the query under the date field enter “Between #07/01/97# and #07/31/97#”. Once your query works properly save is. This will produce a recordset for all July 1997 records ONLY, and then change the report’s “Record Source” property to the name of the query you just saved.

This should be easy – good lick.

Reports In Access 97

In reply to Reports In Access 97

I MUST learn to type better – save “it” and good “luck”. I need spellcheck added to TechRepublic I guess???

Reports In Access 97

In reply to Reports In Access 97

If the month varies each time you generate a new report enter “Between [Beginning] and [Ending]”. When this query runs it will request a Beginning and Ending date, and you can print any month, week, day, year, quarter, or whatever. (you don’t needthe quotes in your entry – I just put them there to show you exactly what to enter. Also your beginning and ending date entries must be “mm/dd/yy” or “mm/dd/yyyy”.

Reports In Access 97

In reply to Reports In Access 97

The question was auto-closed by TechRepublic

Reports In Access 97

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.

Reports In Access 97

In reply to Reports In Access 97

The question was auto-closed by TechRepublic

Reports In Access 97

In reply to Reports In Access 97

This question was auto closed due to inactivity

[Author]

Replies