Criteria for Current Status Date in Query 1

[tinkertoy]

I have a query with the person and that person has a subform with their multiple status, active and/or inactive. The query is for the person ID number, active, and based on their most recent/current status date. I only want to show the most recent/current status, not older. The date of course varies, it can be as old as a few months, or today. I know that I could put in equal and greater than, but how would I indicate the most recent one in the status? The latest one is of course at the "top" of the subform (datasheet view). I need suggestions as to the correct syntax for the criteria. Thank you!

 

[BlackKnight]

Hi,

Try something like this. Obviously modify as needbe. <g>

SELECT IDNumber, Active, MAX(StatusDate) as CurrentStatus FROM table1

Have a good one!
BK

 

[tinkertoy]

Thank you for your help! I changed it to &quot;fit&quot; my fields, etc. But, I now get data type mismatch. I am not sure where or what field its referring to. As to &quot;CurrentStatus&quot;, is this a new field or a type of function? Sorry, I sound like I don't know what I am doing, but, my projects seem to be needing more complicated functions. Thanks!

 

[tinkertoy]

I retried - with a parens instead of &quot;&quot; (my fault it is a query criteria) It worked, but, only showed one person, not all the people. The date it shows was today's date, and not the dates that were older. Is this where I could use the equal to and less than function? If so, how would it look with your suggestion?

 

[BlackKnight]

Hi,

Perhaps, I misunderstood you in what you wanted I thought you wanted to get ONLY the most recent date for each user?

So you have sample data like this in your table?:

ID# Status StatusDate
10002 Active 12/31/00
10000 Active 01/01/00
10000 Active 01/01/01
10000 Inactive 01/01/02
10001 Inactive 01/01/99
10001 Active 12/11/00
10001 Inactive 08/01/01
10001 Active 11/11/02

And in your subform after running a query you want to display this right?:

ID# Status StatusDate
10000 Inactive 01/01/02
10001 Active 11/11/02
10002 Active 12/31/00



You want to run a query that can show all of the people's information, in descending order, according to their StatusDate right? Exampl:

10000

 

[BlackKnight]

Hi,

Perhaps, I misunderstood you in what you wanted I thought you wanted to get ONLY the most recent date for each user?
If not please give me some sampel data and what you expect to see in the subform after the query is run.

So you have sample data like this in your table?:

ID# Status StatusDate
10002 Active 12/31/00
10000 Active 01/01/00
10000 Active 01/01/01
10000 Inactive 01/01/02
10001 Inactive 01/01/99
10001 Active 12/11/00
10001 Inactive 08/01/01
10001 Active 11/11/02

And in your subform after running a query you want to display this right?:

ID# Status StatusDate
10000 Inactive 01/01/02
10001 Active 11/11/02
10002 Active 12/31/00


This query will display the above results.
=================
SELECT tblPerson.IDNumber, Max(tblPerson.StatusDate) AS LastDate
FROM tblPerson
GROUP BY tblPerson.IDNumber;
=========================

Have a good one!
BK

 

[tinkertoy]

Thank you! It works fine. I see where I was going wrong now. I will keep this for my information.

tinkertoy