Help with Query and dates

[hawg1]

Hello,

I've created a query that finds all of the dates for specific types (monthly, quarterly, semiannual, annual) of evaluations. It works great, shows all evaluation dates for each person.

What I really need it to do is just show the most recent (i.e. latest) evaluation date for each person. Each person could have 10 or more records; just need the latest one. Eventually want to update a table that has a last evaluation field on it for every person.

I have set the top values to 1, but that just showed the latest of all the evaluations (i.e.one person). I couldn't seem to find any clues in the forums. Anyone have some suggestions/pointers?

I've attached the query and main tables.

Thanks

 

[Ian Mac]

I'm little reluctant to offer a solution to this, I think you may need to re-design you tables a bit before you run into big problems later.

You have a HUGE amount of fields in the Personnel (53!!!) table, I don't know what all the fields mean but I suspect things Like Rank will change and therefore should be a different table. You also have a lot Date type field spread across different fields/columns, you should consider moving to a table with something like:

SSN | Activity | ActivityDate

And data might look like:

000-00-0002 BIC Grad 30-May-06
000-00-0003 BIC Grad 01-Apr-05
000-00-0002 ISD Comp 29-Jun-06
000-00-0003 ISD Comp 20-May-05
000-00-0002 Obj & Test 21-Jun-06
000-00-0003 Obj & Test 25-May-05
000-00-0003 Internship 28-Feb-06
000-00-0007 BIC Grad 15-Apr-05
000-00-0007 ISD Comp 20-May-05
000-00-0007 Obj & Test 25-May-05
000-00-0007 Internship 14-Apr-06

Also avoid using Date as a Field Name and try not to use spaces in field names either.

Search/Read and Understand Normalization of data.

If you need an answer, as I say I'm reluctant:

SELECT SSN, Last, Date, (SELECT Result From EvalsComp Where SSN=Q1.SSN And Date=Q1.Date;) AS Res
FROM EvalsComp AS Q1
WHERE Q1.Date In (SELECT Max(Date) FROM EvalsComp Where SSN=Q1.SSN);

Is one way to achieve what you want. Another would be to use 2 queries joined together.

 

[hawg1]

Ian,

Thanks for the suggestions and info. I inherited the database from a predecessor. I too, did a doubletake at the size of the personnel table and have though of breaking it down into smaller tables. as for the dates, they are critical as that is how we track events. Most of these are training issues and the dates trigger a due date for refresher courses etc.

 

[Ian Mac]

Sorry, I think you miss-read me.

Dates are good, but use the word Date as a field name is not a good idea as Date is a reserved word in Access. Re-name the fields to something like AssessmentDate.

Hope that clearer,

 

[hawg1]

IAN,

Thanks for the clarification. I have gone in an removed the word "DATE" from all field names. I'm also in the process of breaking the table up into more mangeable pieces. That large table just didn't sit well with me.

Hawg1

 

[WayneRyan]

hawg1,

Make a 1st query whose only objective is to give you the Max date
for each employee:

Select SSN, Max(Date)
From   EvalsComp
Group by SSN

Then, you can join that query with the Personnel table by the SSN, or
retrieve info from the EvalsComp table by joining with the SSN and
the maximum date.

And Ian is right about the spaces spaces in your names (and definitely
the reserved word Date).

hth,
Wayne