Find First Date Approval

[jereece]

I have a query based on an SQL database that our company uses to document problems and resolutions. I need to run a report that will show all the reports that were completed in the last 30 day. The field that the approval date is documented is called dbo_approved. Sometimes a report is approved and then at a later date reopened to add something then approved again. The design of this database is such that the first approval is one record and the second approval is a different record. So, using a normal query I will find some records that were previously appproved which I don't care about. I only want a list of reports that were approved for the first time within the last 30 days. Is there a criteria I can use in my query to only look for the first approval date?

As always I appreciate the help.

Jim

 

[namliam]

Select *
from table
where approvaldate > date() - 30

?? Would that do ??
If not we need to know more about your table(s) to find the solution

 

[jereece]

Select *
from table
where approvaldate > date() - 30

?? Would that do ??
If not we need to know more about your table(s) to find the solution

Thats what I am doing now. Let me be a little more specific. The data fields of importance in the query are

Report Number
Status
Approved Date

When a report has been approved, the status is changed to Approved and the approval date entered. However at times someone will need to update the report so it is unapproved which sets the status back to InProgress. Then when it gets approved again, the system documents a second date in the Approved Date field. So if I were to query Report Number 08-0001 and that report had been unapproved, edited, then reapproved, I will get 2 records in my results. One record would show the original approval date and the other would show the second approval date.

For my query I am only interested in the original approval date.

Does that give you enough information? I do appreciate the help.

Jim

 

[raskew]

Hi -

Try this, changing table and field names as appropriate

SELECT Orders3.CustomerID, Count(Orders3.OrderDate) AS CountOfOrderDate
FROM Orders3
WHERE (((Orders3.OrderDate)>=Date()-30))
GROUP BY Orders3.CustomerID
HAVING (((Count(Orders3.OrderDate))=1));

HTH - Bob

 

[Rich]

Look up the Min function

 

[namliam]

You speak of 2 records
1) with the old date
2) with the new dat

Yet if you do > date -30 you still get both???
Ah I think I get it...
Create a new query, make it group by on [Report number] max(approved date)
Then create a second query, linking your table with this query on both fields. That should give you the result you want .... I think...