I need to get a desired result from a query

[PJHAction]

I have some knowledge on VB, just need help solving a routine, I cannot get the desired results in Access query:

Scenario: I have two tables in Access that I joined, Left outer join to select all names and then my sub table all of the evaluations (1 per quarter). I need an access rpt to state if individual A has had an evaluation this quarter.

Query (sample)

Lastname Evaldate

Smith 1-Feb-08
Smith 1-Apr-08
Doe 8-Apr-08
Frank 11-Dec-07
Frank 20-Jan-08
Johnson

Here is my output
- Do they have an eval this quarter, this quarter starts 1-apr-08

Lastname Evaluation Status

Smith Completed
Doe Completed
Frank Due
Johnson Due

Before I get started, I was thinking of doing this with IF, THEN, ELSE

What do you think?

Peter

 

[PWise]

try

Select lastname ,iif(datepart("q",date())=datepart("q",lasteval()),"Completed","due")Status
from maintable
left join(
select lastname, max(evaldate)lasteval
from Subtablename
group by lastnmae)sub
on maintable.lastname=Sub.lastname

 

[PWise]

sorry

Should be

Select lastname ,iif(datepart("q",date())=datepart("q",lasteval),"Completed","due")Status
from maintable
left join(
select lastname, max(evaldate)lasteval
from Subtablename
group by lastname)sub
on maintable.lastname=Sub.lastname

 

[fneily]

Add a new field to your query that looks like:
Status: IIf([Hired] Between #4/1/2008# And #7/1/2008#,"completed","due")

 

[PJHAction]

I will look over this on Tuesday. BTW, I have always been impressed on the help I receive, thanks.

Peter

 

[PJHAction]

I tried what fneily stated and it was a quick fix but I still had duplicates.

I am close on the pwise option, the select worked great but my join has issues

The first part worked:

SELECT tblPPersonnel.SSN, tblPEvals.EvalDate, IIf(DatePart("q",Date())=DatePart("q",tblpevals.evaldate),"Completed","Due") AS Status
FROM tblPPersonnel LEFT JOIN tblPEvals ON tblPPersonnel.SSN = tblPEvals.SSN;

Putting my join together gives 'max' error

FROM tblPPersonnel LEFT JOIN(select tblpersonnel.ssn, max(tblpevals.evaldate)tblpevals.evaldate from tblpevals group by tblppersonnel.ssn)sub on tblPPersonnel.ssn=sub.ssn

 

[PWise]

try

Select lastname ,iif(datepart("q",date())=datepart("q",[COLOR=red]Sub.[/color]lasteval),"Completed","due")Status
from maintable

LEFT JOIN(select tblpersonnel.ssn,  max(tblpevals.evaldate)  [COLOR=red] as lasteval [/color]
[s] tblpevals.evaldate[/s] from tblpevals group by tblppersonnel.ssn)sub on  tblPPersonnel.ssn=sub.ssn

 

[PJHAction]

Greetings, this is where I am at

SELECT tblPPersonnel.SSN, tblPEvals.EvalDate, IIf(DatePart("q",Date())=DatePart("q",tblpevals.evaldate),"Completed","Due") AS Status
FROM tblPPersonnel LEFT JOIN tblPEvals ON tblPPersonnel.SSN = tblPEvals.SSN;

**** Make a query showing the SSNs, Evaldates, Status

The second part is causing problems after the LEFT JOIN I Pasted as instructed and it prompts me for input on fields

Here is everything

SELECT tblPPersonnel.SSN, tblPEvals.EvalDate, IIf(DatePart("q",Date())=DatePart("q",tblpevals.evaldate),"Completed","Due") AS Status
FROM tblPPersonnel LEFT JOIN (select tblppersonnel.ssn, max(tblpevals.evaldate)as lasteval from tblpevals group by tblppersonnel.ssn)sub on tblPPersonnel.ssn=sub.ssn

 

[PWise]

SELECT tblPPersonnel.SSN, tblPEvals.EvalDate, IIf(DatePart("q",Date())=DatePart("q",tblpevals.evaldate),"Completed","Due") AS Status
FROM tblPPersonnel LEFT JOIN (select tblpevals.ssn, max(tblpevals.evaldate)as lasteval from tblpevals group by tblpevals.ssn)sub on tblPPersonnel.ssn=sub.ssn

 

[PJHAction]

OMG! That is sooo close, the results are grouped perfect, status (completed or due is perfect) just getting one error msgbox (Enter Paramenter Value, tblPEvals.Evaldate)and results have Evaldate as Expr1 as blank.

SELECT tblPPersonnel.SSN, tblPEvals.EvalDate, IIf(DatePart("q",Date())=DatePart("q",sub.lasteval),"Completed","Due") AS Status
FROM tblPPersonnel LEFT JOIN (select tblpevals.ssn, max(tblpevals.evaldate)as lasteval from tblpevals group by tblpevals.ssn) AS sub ON tblPPersonnel.SSN = sub.ssn;

 

[PWise]

change to

SELECT tblPPersonnel.SSN, Sub.lasteval [last eval], IIf(DatePart("q",Date())=DatePart("q",sub.lasteval),"Completed","Due") AS Status
FROM tblPPersonnel LEFT JOIN (select tblpevals.ssn, max(tblpevals.evaldate)as lasteval from tblpevals group by tblpevals.ssn) AS sub ON tblPPersonnel.SSN = sub.ssn;

 

[PJHAction]

That did it, Thank you very much. Does this site have a feedback section? I try reading the code but I guess experience in using it will help.

Peter

 

[ProgramError]

You can give the helper a star by clicking on the link below each reply

Thank pwise
for this valuable post!

Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!