How hide value on report if certain criteria exists

[SteveL]

I have a report which lists records sorted by part number and then by date. Also on the report for each record is an inventory qty. So the first record may show part #1 due 1/31/2010 and display an inventory qty of "5". If the next record on the report is for the same part number, regardless of the due date, I want the inventory qty to display "0". I realize this is a little strange but it has to be displayed this way. I can't just hide dupes because the next record for an entirely different part number may need to show the accurate inventory qty.

 

[SteveL]

To further explain...

The report now looks like:

Part # Due Date FG Qty
12345 1/31/2010 1
12345 2/10/2010 1

Any I want it to look like:

Part # Due Date FG Qty
12345 1/31/2010 1
12345 2/10/2010 0

Note the "0" because the FG Qty is already shown in the first record which is for the same part number. In other words, if the part number is the same, replace whatever qty is in FG Qty with a "0".

 

[AlienRay]

I think the work needs to be done in the underlying query, rather than the report. You need to count how many orders there are for each part, as well as determine which order is the earliest, and calculate the inventory field based on that data.

Here's an example of how that might work. The results are in the query named "ReportQuery".