Filtering by most recent date in a report

[Guest]

I have a report that tracks different phases of a project and the dates on
which they were signed. The pertinent fields are as follows:

Project_Name
Date_signed
Phase_I_value
Phase_II_value
Phase_III_value etc.

In a datasheet view of the query that runs the report, there are rows that
contain data in Project_Name, Date_signed, Phase_I_Value. The next row
contains data in Project_Name, Date_signed, Phase_II_Value. Through the use
of another crosstab query and the query that runs the report, I have
fashioned a report that is oh-so-close to what I need, where column headings
are Project_Name, Date_signed, Phase_I_Value, Phase_II_value, etc. But, in
the report, I have two identical lines of data (duplicated information) with
different Date_signed dates where Phase I was signed 10-Apr-05 and Phase II
was signed 29-Sep-05.

I have tried to do a Filter in the report properties with
DMax=("[Date_signed]","Query Name"), setting the filter on "ON". The date
that appears in the report is today's date, and not the hard-entered date. I
have attempted to also do this in the query from which this report is
generated, with no success...not sure how the filter-in-the-query would look
like.

What I need help with is to filter the report or the query so that the most
recent date appears in the report, but the data associated with earlier
phases and their values also appears.

Thanks in advance for your suggestions.
Irena

 

[David Lloyd]

Irena:

I don't know all of your specifics, however, when you run your crosstab are
you grouping on the Date_Signed field? If so, you are creating separate
entries for each date. If you were instead to use the MAX aggregate
function for the Date_Signed field (select Max in the Total row in the Query
Designer for this field), it would produce the latest date and consolidate
the entries for a particular project.

If that is not the issue, maybe you can give us your table structure(s) and
also the SQL of your queries.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a report that tracks different phases of a project and the dates on
which they were signed. The pertinent fields are as follows:

Project_Name
Date_signed
Phase_I_value
Phase_II_value
Phase_III_value etc.

In a datasheet view of the query that runs the report, there are rows that
contain data in Project_Name, Date_signed, Phase_I_Value. The next row
contains data in Project_Name, Date_signed, Phase_II_Value. Through the use
of another crosstab query and the query that runs the report, I have
fashioned a report that is oh-so-close to what I need, where column headings
are Project_Name, Date_signed, Phase_I_Value, Phase_II_value, etc. But, in
the report, I have two identical lines of data (duplicated information) with
different Date_signed dates where Phase I was signed 10-Apr-05 and Phase II
was signed 29-Sep-05.

I have tried to do a Filter in the report properties with
DMax=("[Date_signed]","Query Name"), setting the filter on "ON". The date
that appears in the report is today's date, and not the hard-entered date.
I
have attempted to also do this in the query from which this report is
generated, with no success...not sure how the filter-in-the-query would look
like.

What I need help with is to filter the report or the query so that the most
recent date appears in the report, but the data associated with earlier
phases and their values also appears.

Thanks in advance for your suggestions.
Irena

 

[Guest]

Hi David,

The Sign_date was not included in the crosstab query. I fiddled around with
the Max aggregate suggestion in the Query design of the non-crosstab query
you proposed and it worked like a charm. Thank you so much for your help.

All the best,
Irena