RE: Query Criteria

Ok, I am so sorry...I am just not following.
Here is the code for the query that I have now:
SELECT tbl_status_of_lif.number AS [Lifecycle #], tbl_status_of_lif.status_dt
AS [Status Date], tbl_status_of_lif.status AS Status, tbl_status_of_lif.
sub_by AS [Submitted by]
FROM tbl_status_of_lif
WHERE (((tbl_status_of_lif.status)<>"lc closed") AND ((tbl_status_of_lif.
status_id) In (SELECT Max(Status_ID) as MaxID
FROM tbl_status_of_lif INNER JOIN
(SELECT number, Max(Status_Dt) as LastDate
FROM tbl_status_of_lif
GROUP BY number) as Tmp
ON tbl_status_of_lif.number = Tmp.number
and tbl_status_of_lif.Status_dt = Tmp.LastDate
GROUP BY tbl_status_of_lif.number, tbl_status_of_lif.Status_dt)))
ORDER BY tbl_status_of_lif.status_dt DESC;

I built the table you told me to. Now where do I put what you told me? Do I
add it to this query or do I create a new query. Sorry that I am not getting
it, but I really do appreciate your help!

KARL DEWEY wrote:
After I posted I realized there needed to be one more query step. Use the
posted query in a second query --
SELECT Min([Dates-5_Bus_Days].Dates) AS MinOfDates
FROM [Dates-5_Bus_Days];

This results in the earlist date in the output. Use it in criteria
<[MinOfDates]
Here is one way. If you know subqueries then this can be done as a sub.
Create a table named CountNumber with integer field named CountNUM
[quoted text clipped - 10 lines]

Thanks!

--
Message posted via http://www.accessmonster.com

.

Relevant Pages

  • Re: update 4 columns in one query?
    ... Post the other queries. ... By the way the set line of your posted query doesn't read correctly. ... If the criteria is different then by combining the query criteria you ...
    (microsoft.public.access.queries)
  • Re: Calculate yield
    ... you posted table structure and your posted query don't seem to have ... FROM INNER JOIN tblPArts ON tblBatch.Traveler_ID = tblPArts.TravelerID ... but now I'm at a loss for how to calculate the Yield. ...
    (microsoft.public.access.queries)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)