I have a simple table with projects, some dates and a column "status" showing if the project has finished or not (yes/no type). The projects are identified with nummbers (Project-Nr). in the table it ist possible that one and the same project number has not been finished and is marked with no, but at a later time it has finished and is marked with yes in the column status.
for the report I need to extract projects that are finished and the ones that are not. but I cannot simply choose the ones that are marked with yes or no, because some projects with the same number are simply both status yes and no.
the parts of the table :
Date-at a certain time a project was not finished at a later time point it is finished, that's why I have duplicate entries in Project-Nr
Project-Nr ist numerical, Status is Yes/No (True/False)
I need to extract Projects for the report, the user can choose if he wants to see the finished or unfinished projects. The problem is for example with the project number 403, because it was first set to status NOT FINISHED and later the same number is finished, otherwise I could simply just check the status column and extract the finished and the unfinished ones. I cannot do that, because then project number 403 would appear as unfinished and it is finished in the end.
this ist my code:
stSQL = "SELECT [Pr-Nr], Status, Max (Termin) FROM Termine GROUP BY [Pr-Nr], Status"
rsPSB.CursorLocation = adUseClient
rsPSB.Open stSQL, conCurrent, adOpenStatic, adLockOptimistic, adCmdText
and later I want to open a report with this data and several conditions
strConditionLatestDate= "Termine.Termin ORDER BY Termin DESC LIMIT 1"
DoCmd.OpenReport "internes Audit", acViewPreview, "query", strConditionDatumAb & " And " & strConditionStatus & " And " & strConditionLatestDate
but the condition for the latest date doesn't work, do you have any idea how to do this..