Displaying groups that contain negative detail lines

[Former Member]

I have a report that displays sales transactions. The report is grouped by transaction id. I need to only display the transaction group that contains a negative transaction (neg quantity or neg price); showing both neg and pos transactions for that group. This report is based off Access tables.

[Former Member · Accepted solution]

David,

The easiest way to do this would be to build a query in Access that provides this for you. You can do it easily using a two query approach.

QueryNo1:

SELECT
TransactionTable.TransactionID
FROM TransactionTable
WHERE TransactionTable.Price < 0 OR TransactionTable.Quantity < 0

QueryNo2:

SELECT *
FROM TransactionTable
WHERE TransactionTable.TransactionID IN [QueryNo1.TransactionID]

This will give you all of the TransactionID's that have a negative transaction associated with it.

Once you have the query completed to your satisfaction and saved, you can use it as your data source in CR. CR will see it as a View. If you make it a parameter query, CR will see it as a Stored Procedure.

Hope this helps,

Jason

[Former Member]

Thanks Jason. I was trying to do it in Crystal via record and group selection formula but couldn't get it to work. I had actually thought about the access query solution you suggested. Thanks for confirming.

[Former Member]

Glad it worked