Query Help, Works in Excel But not Access

[S Begum via AccessMonster.com]

Hi All,

I have the following data:

ID Name DOB Status Status Start Date

01 Andrews 05/08/2001 Active 01/01/2008
01 Andrews 05/08/2001 Not Active 01/03/2008
01 Andrews 05/08/2001 Active 28/06/2008
02 Hill 25/05/2002 Not Active 25/05/2008
03 Woods 27/06/2001 Active 07/02/2002
03 Woods 27/06/2001 Not Active 05/03/2003
04 Green 07/08/1999 Active 07/08/2008

Basically the output i require from an Access query is when there is more
than one ID, the status is Active i want the query to pick the earliest
record and in a seperate column mark as OK and alternatively for all the
records where only one ID present to also be outputted as OK.

The following formula works if applied in Excel:

=IF(D2 ="Active","OK",IF(A2=A1,"NOT APPLICABLE","OK"))

Any feedback would be appreciated.

Regards

S Begum

 

[KARL DEWEY]

Your Excel formula will not give what you said in the narrative. Here it is.
ID Name DOB Status Status Start Date
01 Andrews 05/08/2001 Active 01/01/2008 OK
01 Andrews 05/08/2001 Not Active 01/03/2008 NOT APPLICABLE
01 Andrews 05/08/2001 Active 28/06/2008 OK
02 Hill 25/05/2002 Not Active 25/05/2008 OK
03 Woods 27/06/2001 Active 07/02/2002 OK
03 Woods 27/06/2001 Not Active 05/03/2003 NOT APPLICABLE
04 Green 07/08/1999 Active 07/08/2008 OK

Do tou want it to be like this --
ID Name DOB Status Status Start Date
01 Andrews 05/08/2001 Active 01/01/2008 OK
01 Andrews 05/08/2001 Not Active 01/03/2008 NOT APPLICABLE
01 Andrews 05/08/2001 Active 28/06/2008 NOT APPLICABLE
02 Hill 25/05/2002 Not Active 25/05/2008 OK
03 Woods 27/06/2001 Active 07/02/2002 OK
03 Woods 27/06/2001 Not Active 05/03/2003 NOT APPLICABLE
04 Green 07/08/1999 Active 07/08/2008 OK

 

[shaguftabegum via AccessMonster.com]

Hi

Yes the second example is what i would like the output to be, apologies for
the confusion.

Many thanks

Karl

Your Excel formula will not give what you said in the narrative. Here it is.
ID Name DOB Status Status Start Date
01 Andrews 05/08/2001 Active 01/01/2008 OK
01 Andrews 05/08/2001 Not Active 01/03/2008 NOT APPLICABLE
01 Andrews 05/08/2001 Active 28/06/2008 OK
02 Hill 25/05/2002 Not Active 25/05/2008 OK
03 Woods 27/06/2001 Active 07/02/2002 OK
03 Woods 27/06/2001 Not Active 05/03/2003 NOT APPLICABLE
04 Green 07/08/1999 Active 07/08/2008 OK

Do tou want it to be like this --
ID Name DOB Status Status Start Date
01 Andrews 05/08/2001 Active 01/01/2008 OK
01 Andrews 05/08/2001 Not Active 01/03/2008 NOT APPLICABLE
01 Andrews 05/08/2001 Active 28/06/2008 NOT APPLICABLE
02 Hill 25/05/2002 Not Active 25/05/2008 OK
03 Woods 27/06/2001 Active 07/02/2002 OK
03 Woods 27/06/2001 Not Active 05/03/2003 NOT APPLICABLE
04 Green 07/08/1999 Active 07/08/2008 OK

Hi All,

[quoted text clipped - 24 lines]

S Begum

 

[KARL DEWEY]

Use these two queries --
S_Begum_Active ---
SELECT S_Begum.ID, S_Begum.Name, S_Begum.DOB, Min(S_Begum.[Status Start
Date]) AS [MinOfStatus Start Date]
FROM S_Begum
WHERE (((S_Begum.Status)="Active"))
GROUP BY S_Begum.ID, S_Begum.Name, S_Begum.DOB;

SELECT S_Begum.ID, S_Begum.Name, S_Begum.DOB, S_Begum.Status,
S_Begum.[Status Start Date], IIf([MinOfStatus Start Date]=[Status Start
Date],"Ok","Not Applicable") AS Expr1
FROM S_Begum INNER JOIN S_Begum_Active ON (S_Begum.DOB = S_Begum_Active.DOB)
AND (S_Begum.Name = S_Begum_Active.Name) AND (S_Begum.ID = S_Begum_Active.ID);

--
KARL DEWEY
Build a little - Test a little

Hi

Yes the second example is what i would like the output to be, apologies for
the confusion.

Many thanks

Karl

Your Excel formula will not give what you said in the narrative. Here it is.
ID Name DOB Status Status Start Date
01 Andrews 05/08/2001 Active 01/01/2008 OK
01 Andrews 05/08/2001 Not Active 01/03/2008 NOT APPLICABLE
01 Andrews 05/08/2001 Active 28/06/2008 OK
02 Hill 25/05/2002 Not Active 25/05/2008 OK
03 Woods 27/06/2001 Active 07/02/2002 OK
03 Woods 27/06/2001 Not Active 05/03/2003 NOT APPLICABLE
04 Green 07/08/1999 Active 07/08/2008 OK

Do tou want it to be like this --
ID Name DOB Status Status Start Date
01 Andrews 05/08/2001 Active 01/01/2008 OK
01 Andrews 05/08/2001 Not Active 01/03/2008 NOT APPLICABLE
01 Andrews 05/08/2001 Active 28/06/2008 NOT APPLICABLE
02 Hill 25/05/2002 Not Active 25/05/2008 OK
03 Woods 27/06/2001 Active 07/02/2002 OK
03 Woods 27/06/2001 Not Active 05/03/2003 NOT APPLICABLE
04 Green 07/08/1999 Active 07/08/2008 OK

Hi All,

[quoted text clipped - 24 lines]

S Begum