Report to Filter on Next Date Per File

[LDMueller]

I have a docketing report which reports any action due for a particular file.
Theses actions are future dates. A file can have multiple actions due.

For example:
File ABC12345
Action 6/1/2010 Letter to Client
Action 6/9/2010 Payment Due
Action 12/10/2010 Status Check
File CYO4457
Action 3/1/2011 Letter re Exam
Action 6/4/2011 Annuity Due

I need to write a report which will only give me the next action due. Using
my example the output would be like the following:
File ABC12345
Action 6/1/2010 Letter to Client
File CYO4457
Action 3/1/2011 Letter re Exam

My fields are as follows:
FILE (Text Field)
ACTION (Text Field)
DUE (Date Field)

Can anyone help me with the code for this.

Thank you in advance.

LDMueller

 

[Marshall Barton]

I have a docketing report which reports any action due for a particular file.
Theses actions are future dates. A file can have multiple actions due.

For example:
File ABC12345
Action 6/1/2010 Letter to Client
Action 6/9/2010 Payment Due
Action 12/10/2010 Status Check
File CYO4457
Action 3/1/2011 Letter re Exam
Action 6/4/2011 Annuity Due

I need to write a report which will only give me the next action due. Using
my example the output would be like the following:
File ABC12345
Action 6/1/2010 Letter to Client
File CYO4457
Action 3/1/2011 Letter re Exam

My fields are as follows:
FILE (Text Field)
ACTION (Text Field)
DUE (Date Field)

Try this kind of query as the report's record source:

SELECT T.*
FROM actionstable As T
WHERE Due = (SELECT Min(X.Due)
FROM actionstable As X
WHERE X.File = T,File
And X.Due >= Date())

or probably faster:

SELECT T.*
FROM actionstable As T
INNER JOIN (SELECT X.File, Min(X.Due)
FROM actionstable As X
WHERE X.Due >= Date())
GROUP BY X.File) As Y
ON T.File = Y.File And T.Due = Y.Due

 

[John Spencer]

A query like the following should return what you want.

SELECT File, Due, Action
FROM SomeTable
WHERE Due = (SELECT Min(Due) FROM SomeTable as Temp WHERE Temp.File =
SomeTable.File AND Due>=Date())

To build it in query design view
== Add your table
== Add the field you want to see
== Under DUE you need to add criteria that looks like the following.
Replace SomeTable with the actual name of your table.
(SELECT Min(Due)
FROM [SomeTable] as Temp
WHERE Temp.File = [SomeTable].File
AND Temp.Due>=Date())

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County