filter the by date, last one entered prior date()

[AndreasO]

Subrecords contain a date and location field, (dates are entered in the
future, projected), meaning that the location can change many times for main
record items.
I try to filter the mainrecords by the current location, the last one
entered prior date().
When I sort them by date first and use <date() it will not only return the
main record in the current locations but I also find the main record again in
all prior locations, which are not current since they moved.
I can't find the correct expression.
Try run a macro to do this.
Thanks for your help.
Andreas

 

[KARL DEWEY]

Post examples of your data with field names. Then show what you want results
to show.

 

[AndreasO]

Proj ID Task# Task Date Task Loc
212 1 12/04/07 TBD
212 2 12/05/07 PATH
212 3 12/06/07 TBD
212 4 12/06/07 G2 X
212 5 01/14/08 B5
213 1 12/04/07 G1
213 2 12/13/07 G1
213 3 12/26/07 C1
213 4 01/02/08 H4 X
213 5 01/21/08 D1
214 1 12/05/07 G1
214 2 01/09/08 B2 X
214 3 01/14/08 B1
214 4 02/06/08 B1
215 1 12/12/07 G2
215 2 01/02/08 B1 X
215 3 01/22/08 B3

Thanks for your help! Here is some sample data.
The records with a X I would like returned if date() is 01/09/08.
These are all sub records on or one sub before today.
I could also use the task# since it is entered together with the date.

Thanks
Andreas

 

[KARL DEWEY]

Try these two queries ---
AndreasO_A ---
SELECT AndreasO.[Proj ID], AndreasO.[Task#], AndreasO.[Task Date],
AndreasO.[Task Loc]
FROM AndreasO
WHERE (((AndreasO.[Task Date])<Date()));

SELECT Q.[Proj ID], Q.[Task#], Q.[Task Date], Q.[Task Loc], (SELECT COUNT(*)
FROM AndreasO_A Q1
WHERE Q1.[Proj ID] = Q.[Proj ID]
AND Q1.[Task#] > Q.[Task#]
AND Q1.[Task Date] > Q.[Task Date])+1 AS Rank
FROM AndreasO_A AS Q
WHERE ((((SELECT COUNT(*) FROM AndreasO_A Q1
WHERE Q1.[Proj ID] = Q.[Proj ID]
AND Q1.[Task#] > Q.[Task#]
AND Q1.[Task Date] > Q.[Task Date])+1)=1))
ORDER BY Q.[Proj ID], Q.[Task#], Q.[Task Date];

 

[AndreasO]

Thank you!!! It is working nicely......

Andreas

Try these two queries ---
AndreasO_A ---
SELECT AndreasO.[Proj ID], AndreasO.[Task#], AndreasO.[Task Date],
AndreasO.[Task Loc]
FROM AndreasO
WHERE (((AndreasO.[Task Date])<Date()));

SELECT Q.[Proj ID], Q.[Task#], Q.[Task Date], Q.[Task Loc], (SELECT COUNT(*)
FROM AndreasO_A Q1
WHERE Q1.[Proj ID] = Q.[Proj ID]
AND Q1.[Task#] > Q.[Task#]
AND Q1.[Task Date] > Q.[Task Date])+1 AS Rank
FROM AndreasO_A AS Q
WHERE ((((SELECT COUNT(*) FROM AndreasO_A Q1
WHERE Q1.[Proj ID] = Q.[Proj ID]
AND Q1.[Task#] > Q.[Task#]
AND Q1.[Task Date] > Q.[Task Date])+1)=1))
ORDER BY Q.[Proj ID], Q.[Task#], Q.[Task Date];

--
KARL DEWEY
Build a little - Test a little

Proj ID Task# Task Date Task Loc
212 1 12/04/07 TBD
212 2 12/05/07 PATH
212 3 12/06/07 TBD
212 4 12/06/07 G2 X
212 5 01/14/08 B5
213 1 12/04/07 G1
213 2 12/13/07 G1
213 3 12/26/07 C1
213 4 01/02/08 H4 X
213 5 01/21/08 D1
214 1 12/05/07 G1
214 2 01/09/08 B2 X
214 3 01/14/08 B1
214 4 02/06/08 B1
215 1 12/12/07 G2
215 2 01/02/08 B1 X
215 3 01/22/08 B3

Thanks for your help! Here is some sample data.
The records with a X I would like returned if date() is 01/09/08.
These are all sub records on or one sub before today.
I could also use the task# since it is entered together with the date.

Thanks
Andreas