Conditionally Supress Lines, or better record selection??? 1

[VBAjedi]

I'm fairly new to CR, so this may be easy, but. . .

I have created a report that may have duplicate values in field "A" (due to multiple matches between my "Customers" and "Sales" tables). How can I supress all lines with a duplicate value in "A" EXCEPT for the line that has the most recent date value in column "B"?

The goal is to have a report with one (and only one) line for each record in my "Customers" table, pulling in the most recent sale to that customer (if there is a sale on record) from my "Sales" table.

The report is actually a lot more complicated than this (six tables involved), but that's the only part I'm having trouble with. Thanks in advance!

VBAjedi

 

[synapsevampire]

I would think that a TOP N report would be the best solution, though you could just insert a Group on the date and display the data in the group footer.

-k

 

[lbass]

You could also go to edit selection formula->group and enter:

{table.date} = maximum({table.date},{table.customerID})

This assumes you are grouping on customerID. Substitute your own field names. If you use a group select, you will need to use running totals for calculations, since underlying records (those resulting from the record select, but not meeting group select criteria) will be counted in the more usual summaries.

-LB

 

[Kebabmeister]

Create a formula that looks for duplicates and set a value where duplictate found and another value where not duplicate. Then use this formula as part of the selection criteria.

Beware however, that this part of the select will not be handled on server so it may slow down the execution of your report

 

[VBAjedi]

lbas,

That sounds like what I would like to do. But I really am new at this, and I can't seem to get your approach to work. Should {table.date} be on both sides of the "=" like that? {table.date} is actually a calculated field (strips off a time stamp), so I'm not sure if I can assign another value to it (like it appears your approach does). What am I missing here?

Thanks!

VBAjedi

 

[lbass]

This group selection formula just states: select those records where the date is the maximum within the group, in my example, the group was on {CustomerID}. Yes, you should be able to use your date formula--let's call it {@date}--as follows:

{@date} = maximum({@date},{table.customerID})

Just make sure you have grouped on customerID or whatever unique identifier you have for each customer. You will have to substitute your own field names, of course. Make sure you place this in the "group" selection, not the "record" selection. If it still does not work, please provide your date formula and a little more info.

-LB

 

[VBAjedi]

lbass,

Sorry it took me a few days to reply. A star for your input!

Thanks!

VBAjedi