Count number records by year

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to count the number of records in a report by the year. For
example I have an applicant and date field. Now that is 2006 I want to count
the number of applicants received by the year 2005. Also, is there a way of
removing the 2005 records out of the tables but still saved in the database?

thank you!
 
In the query behind the report, create a calculated field like... (use
your names)
AppYear : Year(ApplicationDate)
That will yield a 4 digit year for each ApplicationDate, and you can
easily "break" your report on that value by placing AppYear on the report,
and in the Sorting and Grouping dialog box.

Use the same Year function to develop a Delete query that deletes any
record for 2005
 
Thanks Al, the query worked to create the calculated field. I now have the
report sorted ..with all 2005 records first and then all 2006 records after
it. How do I calculate the number of records for each year?
Also, when I create the delete query I want the report with all 2005
records to remain in the database for future viewing...how would I do this?

thanks again!
 
First, an unbound text control in the Year footer with
= Count(ApplicationDate)
should yield the count of records in each Year group

Secondly, in the
AppYear : Year(ApplicationDate)
column of your query, place a criteria of...
<>2005
If you have just 2005 and 2006 records... only 2006 records will display.
I'm assuming no 2004, 2003 etc...

But...
Here's a little trick to make your report more flexible... so you won't have
to update that AppYear criteria as the years go by.
Instead of <>2005 as a criteria, try this...
= [Enter 4 digit Year]
Now when the query runs, you'll be asked to enter 4 digit year in an
InputBox. Enter "2005" and you'll get all the 2005 records. Enter "2006"
and you'll get all the 2006 records... etc.. etc...
This is called a "Parameter" query. Check out Help on Parameter queries...
they are very useful for giving reports flexibility.
 
Data is saved in records in tables. So as far as I know there is no way to
remove the records and yet still have them in the database.

If you don't want them visible, then use queries to limit which records are
displayed on your reports and forms.
 
Back
Top