Alternating Purchase and Sale Records

[Rod]

I have a table with basically the following data:

Date Security Purchase/Sales Qty Price

Jan 1 IBM Purchase 2 10
Jan 1 IBM Purchase 4 11
Jan 1 IBM Purchase 1 14
Jan 1 IBM Purchase 2 15
Jan 1 IBM Sale 2 10
Jan 1 IBM Sale 4 12

I am looking to have the data presented on a daily basis with a purchse and
then an offsetting sale such as:

Date Security Purchase/Sales Qty Price

Jan 1 IBM Purchase 2 10
Jan 1 IBM Sale 2 10
Jan 1 IBM Purchase 4 11
Jan 1 IBM Sale 4 12
Jan 1 IBM Purchase 1 14
Jan 1 IBM Purchase 2 15


The only other criteria is that the purchase and offsetting sale has to net
to zero and the order has to be lowest to highest.

Rod

 

[Michel Walsh]

I don't get the 'priority' part, is it the Price?

If so, right now, it seems that



SELECT *
FROM tableName
ORDER BY price ASC, [Purchase/Sales] ASC


would produce the desired output, on the supplied data sample, but no check
is made for the 'offsetting sale has to net to zero' (which I also fail to
get)



Vanderghast, Access MVP