If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Filtering by most recent date in a report
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 |
#2
|
|||
|
|||
Filtering by most recent date in a report
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. "IrenaY" wrote in message ... 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 |
#3
|
|||
|
|||
Filtering by most recent date in a report
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 "David Lloyd" wrote: 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. "IrenaY" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
pass date from calendar to query on a report form | [email protected] | Running & Setting Up Queries | 2 | November 3rd, 2005 03:13 PM |
Reporting subreport total on main report | BobV | Setting Up & Running Reports | 22 | November 1st, 2005 03:19 AM |
Has anyone seen this behaviour? What might it be? | tw | General Discussion | 4 | June 30th, 2005 03:23 PM |
Query for 'confirmation' | rogge | Running & Setting Up Queries | 8 | April 19th, 2005 03:26 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |