Help for a real rookie!!!

[The Rev]

I am very new to access and am creating a database for our training manager. I have all of my tables and forms set up. I can do everything I need to do except run a particular report. We have 28 people all taking 33 different classes. They all need recertification on different months. I have a table with a column labeled ExpireDate. I need to set up the report to tell me who's date has expired and which classes only. I also need to be able to run this on a monthly basis without any code changes. CAN ANYONE HELP?? I kind of need detailed info, I am a real rookie at this!!! Thanks a Billion!!

 

[pdx_man]

I am assuming that you have a query that lists your people with the classes they are taking. How does the table with the ExpireDate relate? Does it have a PeopleID and ClassID so we know when a Person's certification for a certain class has expired? Again, I will assume so. (Otherwise how are you going to know this?)

This table should basically be your foreign key table. It allows the creation of a many-to-many relationship between People and Classes tables.

So, the criteria on the field ExpireDate is the crux of the problem. How do we get the last month? Well, we can look back one month from today:

Between DateAdd("m",-1,Date()) and Date()

This would give us records where ExpireDate was between 12/15/02 and 01/15/03. Date() is a keyword that returns today's date

Or just get the month. For this, though, we have to create a new column in our query grid:

TheMonth: DatePart("m",[ExpireDate])
with criteria:
DatePart("m",DateAdd("m",-1,Date()))

This would give us all the ExpireDate in December.

HTH

 

[The Rev]

PDX_Man, I apreciate the help. It worked like a charm.