Query name and latest date

[pd06498]

I have looked at similar problems on this forum, but cannot make sense from any of it, so am looking for a simple solution that even I can follow.

In my database, I have a form which shows an employees details, including their Employee Number, name etc.

In this form I want to put a subform which will list some critical training that each employee must do (obviously, the link is the employee number). The training list will contain the name of the training (Equal Opportunity, First Aid & Tactical), the date it was done, and then a calculated field which will display the expiry date of the training (training is only good for a set period of time before it has to be refreshed). Each employee must remain qualified in each of the 3 fields at all times, and each area has a different length of qualifying time.

I want to maintain a record of each employees training history, but on the subform I just want to display the latest history for each particular critical training area for the employee on the main form.

I have been playing around with queries for a while now and trying some suggestions from this forum, but don't seem to be making much headway. Could someone explain what I have to do, so that even I can understand it.

Thanks

 

[Len Boorman]

Will try to explain things for you.

Use a query to select the fields you want on your subform. I will deal with the expiry date later)

Create your subform in exactly the way you would a normal form except that Report Header and Page Header (and footers) will not actually be shown when this form becomes a sub form. Okay so far


Open the Main form in Design view, drag the new form you have just created onto the Main form where you want it to appear.

Now sometimes you get a box pop up for you to make the Child...Parent links.

Otherwise do it this way.

The sub form appears as a white area. Click on it once so that it is selected then right click and select properties. You then get the properties dialogue box and can set up the parent child fields. That's about it really

Now about your expirey dates.

If they all have different expirey periods (90, 365 days etc) then you will need to have this info as part of the Course table. I would suggest that you keep it in days (integer field) You will see why in a moment.

Your sub form query can select the date the person qualified on a certain course and then also select the valid days. The expiry date can be calculated in the query as

Expire_Date:[Qualification_Date]+[Valid_Days]

The resultant value in a Date/Time field should get you where you want to be.

HTH

Len B

 

[pd06498]

Len

Thanks for your reply. I am progressing with help from your suggestion. I have found though that some employees have multiple listings against individual skills. I want the subform just to list the latest date against each individual skill for each employee. Hence, for each employee, there should be a list containing 3 skills, one for each skill, which will be the last time each skill was updated and the expiry date. Currently, I have a list of the lot.

There must be a way to set a query so that I can get the latest record for 3 different skills?

Once again, thanks for your help, and would appreciate another pointer re above if okay.

Also, I have another DB I am working on that I would like some help with if you feel up to it...

Regards

Dave

 

[rockman]

This may get you started:

SELECT [fldEmployeeID], [fldTrainingTasksID], Max([fldDate]) AS fldCompletionDate
FROM tblEmployeesTraining
GROUP BY [fldEmployeeID], [TrainingTasksID]

Assign the (sub) Form source to the query above and then link child/parent on fldEmployeeID.

HTH,
Jeff

PS To be clear, you should have the following tables:
tblEmployees - ID, Name, Address, Phone, etc.
tblTrainingTasks - ID, Training Name, Certification Duration, etc.
tblEmployeesTraining - Joins tblEmployees & tblTrainingTasks with ID, Employee ID, Training ID, Date (Completed)

 

[Len Boorman]

It seems that you are getting all the training records for each skill

The answer is to Group the results. With the query in design mode you should see on the top toolbar the Summation symbil. (Greek Sigma) Click this and you will get grouping option on the query. Select Max for expiry Date and with a bit of luck that should be exactly what you want.

Re another database. Just post the questions (After checking with a search) and it may be me but there are also a lot of people out there always willing to assist

Len

 

[pd06498]

Sorry Len, I still get the same results (i.e. all records).

Maybe I am missing something?

 

[Len Boorman]

Can you post a screen shot of the query results and/or the SQL statement of the query

len

 

[pd06498]

Len

I don't know how I did it, but I managed to stumble over the answer and it works fine now.

Thanks very much for your assistance.

If you know anything about conditional formating, I could use some help with that. I have formatted a field in a record (out of the above lot) so that if the expiry date is within one month of the current date, the field is orange, and if the expiry date is passed the current date, the field is red.

This works fine for the Expiry Date field, but I want the same formatting to occur in all of the related fields as well...

Any ideas?

 

[Len Boorman]

Stumbling is just an alternative explanation for

exploring new avenues and alternative processes

Which if the truth be known is what most of us do at least some of the time.

Generally I used A97 at the mopment so conditional formatting is not something I use. I have to use a variety of work arounds at times but there are plenty on this forum that will help

If you have succeeded for one field then can it not be repeated for another field


Len