Skip to Content
avatar image
Former Member

Displaying groups that contain negative detail lines

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Sep 04, 2008 at 02:50 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 05, 2008 at 12:23 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded