query multiple date in a table

[briandavis]

I have several fields in a table that show the dates of expiration for training for all employees. Training is always on going as these dates are different for all employees. For example: in this table there is an employee "John" John must be recertified in 7 areas each year. In this table there are fields labled Area1expirationdate.....to Area7. I want my query to show only employees whos training expiration date will occur in the next thirty days. I do not want to see anything beyond that. So for example as a result of this query it would show.... Joe has a area1 and 5 expiration date iwithin the next 30 days. Mary has area 7 in the next thirty days. John has expirations in areas 2, 4 and 7 in the next 30 days etc. I can do individual queries but I was hoping for an all in one solution as oppased to making seven queries.

Any ideas?

Thanks

 

[dcx693]

If you add all 7 expiration date fields to the query, then you can put this criteria into each column:
Expiration-Date()<=30

Just make sure to put the critieria for each column into it's own line, otherwise, the query will only return records where all 7 dates are within 30 days.

 

[briandavis]

If you add all 7 expiration date fields to the query, then you can put this criteria into each column:
Expiration-Date()<=30

I added all the date fields and put Date()<=30 into the criteria and nothing happened. Then I put -Date()<=30 in and it worked but it is showing me every employee even if they do not have data in this field. Lastly, I tried my fieldname then the expression CPRExpirationDate-Date()<=30 and I got an error message.

What could I be doing wrong?

P.S. I'm late for work and have to leave I'll check back on this board tomorrow to look for any replies.

Thanks everyone!

 

[dcx693]

Try it like this...

You have fields called Area1expirationdate, Area2expirationdate, etc... right? Create a calculated field from them by changing the entry in the "Field:" line from saying Area1expirationdate to Area1expirationdate-Date()<=30 then in the "Criteria:" line enter True. When you go to do the entry for Area2expirationdate, do the same, but put the criteria onto the next line down from the criteria for Area1expirationdate.

 

[briandavis]

Still no go
When I changed the field line to what you said, it changed the line to read Expr1:[area1expirationdate]-Date()<=30

It did this for all of them but it gave each one a different Expr number.
Also when I looked at the query it returned Only Expr and the number for the heading and all the results were either 0 or -1.

My ultimate goal is to make a report that I can run at the begining of the month and have it list for me just the employees who will need training that month and show me whch areas they need the training in.

Am I even going about this the correct way.

Don't give up on me the third times the charm

Thanks

 

[The_Doc_Man]

Your problem is made more difficult because (at least it sounds like) your table is not normalized.

You need something like this:

tblEmpl - the employees
fldEmplName
fldEmplID - autonumber (?), PK (!)
etc. etc.

tblSubj - the training subjects
fldSubjName
fldSubjID - autonumber (?), PK (!)
fldSubjFreq - the frequency of training required for this subject, pick a unit (AND KEEP THE SAME UNITS ACROSS ALL TRAINING!)
etc

tblEmplTrng - the list of employees and their training
fldEmplID - FK
fldSubjID - FK
fldLastTrngDate - date of the last time this employee received required training IN THIS SUBJECT.

OK, if you do this, your list of employees needing training is now driven by all records where their last training date is older than the corresponding subject's required frequency.

So build the join of tblSubj and tblEmplTrng where the employee training date is before (older than) the DateAdd of the current date and the negative of the required training frequency. Be sure that the employee ID is in this join. You could also write this query as computing the difference between the date the training was due and the current date, and tossing records where that date is in the future. Lots of ways to skin the cat here.

Now build the join of the result of the above that adds in the employees' names.

The result can be sorted by subject, by employee, by due date, etc.

 

[briandavis]

Wow that's a little too much like reinventing the wheel. I just made the last tip you gave me work. In my report I just changed the name that appears from Expr.. to the actual label. I used conditional formating to hide all the "0"'s. So basically I get what I wanted. But instead of an "X" under the area label I get a "-1". Is there a way either in the query or report to tell it where ever it sees a -1 to replacle it with an X.

Thanks for your Help. I have one last issue and my program is complete. I will paste it as a new thread.