manipulate data displayed on report

[sangathy]

I have a report which is based on a query. Need to hide some rows based on a criteria.
For eg: the query returns

fld1 fld2 fld3

ABC 3/1/2005 text1
ABC 10/3/2006 text2
DFG 10/10/2005 text3
EDF 3/4/2006 text4

When fld1 has the same value I want to display the latest record( fld2 determines which one is latest) for eg

the report should look like

ABC 10/3/2006 text2
DFG 10/10/2005 text3
EDF 3/4/2006 text4

is there any way to avoid displaying old records ?

thanks,
sangathy

 

[dawnd3]

If I understand your question correctly you should be able to group the records by the first field and have it show the "First" record for the other fields. Just make sure it is sorted the way you want. So, to be more specific, click on the "Totals" button on the toolbar in design view. Then in the "Total" row on the bottom half of the query put "Group" for that first field and then "First" for the other fields. If it doesn't give you the records you want you may have to play around with the sort.

Let me know if this works or if you need more assistance.

Dawn

http://www.calliopeconsulting.net

 

[dhookom]

I don't think "First" will work. Sort your report by Field1 then Field2 using the sorting and grouping dialog. Display the Group Header for Field1. This section can be very small.

Add a text box to the detail section of the report:
Name: txtGroupCount
Control Source: =1
Running Sum: Over Group
Visible: No

Then add code to the On Format of the detail section of report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Cancel = Me.txtGroupCount <> 1
End Sub

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[sangathy]

Thanks for the replies Dawn and Duane. I can not use the sorting and grouping. I am using a form to input the user parameters and use On Format of detail section to set the orderby conditions. If I use the sorting and grouping that will over ride any settings I have. I need to hide the older records in case of multiple records. So I wrote the query to order by fld1, fld2 DESc and Group by FLD1. BUt I am not able to figure out how to hide the older records.

 

[Remou]

It seems to me that it may be best to use a query to get only newer records and build the report on that. Perhaps something like:
[tt]SELECT tblTable.Field1, tblTable.Field2, tblTable.Field3
FROM tblTable
WHERE (((tblTable.Field2)=(Select Max(Field2) From tblTable A Where A.Field1=tblTable.Field1)));[/tt]

 

[dhookom]

If you only want to display one record per group, just place all the fields in the group header and hide the detail section.

You can use code to set the Sorting and Grouping levels rather than modifying the OrderBy property.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]