Sorry about that, when i cut/copy/pasted the fields into outlook it held the
formatting but when later i read back through to see a mess!. OK so ill
repost with the fields sorted, then i'll investigate your initial answer.
The question was how to eliminate duplicate entries on the Buy or Sell side
of the query where they are not equally matched.
Given that i buy 20,000 units at x price, but Sell them in 5,000 lots. Or I
buy 20,000 units broken into 3 buy orders & then sell them in 1 (or more)
parcels over a length of time; giving different 'contracts' to log.
**the unique thing here is that i can't sell more than i initially buy - if
that means abything, which would suggest a 'Units' based query instead?
Anyway i prepare this query base on two tables Smarket Buy & Smarket Sell
with a normal relationship from tables through SCode.
I have various other grouped queries that sum the COST & PROCEEDS fields to
give a profit/loss & percentage gain/loss fields for stock codes, and they
are grouped by stock code resulting in summed (1) entry
..
But in this query i would like a profit loss 'per trade'; listing 'all the
trades' done on a stock; to measure each trade block. And then Profit/Loss
calcs done. (not a 'grouped sumed total' for that stock code going back to
all past trades).
The problem arises when there is different number of buy & sell orders for
each stock (due to a buy order being broken into seperate buy/sell orders at
the exchange, or I choose to sell in seperate parcells of shares on the sell
end of a trade).
the query fills in the imballance of the Buy or Sell fields with duplicate
entries & then calculates all those bogus entries giving false Profit/Loss &
Percentage P/L fields. Should the query be entering $0.00 or null for
unballanced entries? or am i on the rong track completely.
i hope that clears things up. I'll attempt to copy the fields over to show
where things are duplicating.
Here are the tables that log the buy and sell contracts i recieve:
the standard relationship occures between SCode of Buy & Sell tables.
Table 1: SMarket-Buy
ID|ContractNote|Date|Buy|SCode|Units|Price|Brok|Cos|inMarket|GST|
7 10425577 3/11/2003 B AGX 25000 $0.72 $29.95 $18,029.95 No $2.72
12 9561850 15/08/2003 B AGX 24193 $0.62 $29.95 $15,029.61 No $2.75
20 8781816 6/05/2003 B AGX 45450 $0.44 $31.60 $20,029.60 No $2.87
Table 2: SMarket-Sell
ID|ContractNote|Date|Buy|SCode|Units|Price|Brok|Proceeds|inMarket|GST|
6 10126110 7/10/2003 S AGX 24193 $0.78 $29.95 $18,840.59 $2.72
8 9215778 10/07/2003 S AGX 28746 $0.44 $29.95 $12,618.29 $2.72
10 8882926 21/05/2003 S AGX 16704 $0.38 $16.30 $6,331.22 $1.48
25 11111699 22/01/2004 S AGX 1500 $0.70 $19.95 $1,030.05 $1.81
26 11522550 4/03/2004 S AGX 23500 $0.75 $29.95 $17,595.05 $2.72
...........................................................
I then base 2 queries on those tables - (which are used in other queries
too)
1. Qry-Buy
Date|Buy|Scode|Units|Cost|Brok|Cost|
3/11/2003 B AGX 25000 $0.72 $29.95 $18,029.95
15/08/2003 B AGX 24193 $0.62 $29.95 $15,029.61
6/05/2003 B AGX 45450 $0.44 $31.60 $20,029.60
2. Qry-Sell
Date|Sell|Scode|Units|Price|Brok|Proceeds|
7/10/2003 S AGX 24193 $0.78 $29.95 $18,840.59
10/07/2003 S AGX 28746 $0.44 $29.95 $12,618.29
21/05/2003 S AGX 16704 $0.38 $16.30 $6,331.22
22/01/2004 S AGX 1500 $0.70 $19.95 $1,030.05
4/03/2004 S AGX 23500 $0.75 $29.95 $17,595.05
.............................................................................
Then a 3rd query combing previous 2 qeries with additional Profit/Loss %
calcs fields.
Qry-Profit Loss
Date|Scode|Units|Cost|Date|Scode|Units|Proceeds|Profit Loss:
[Cost]-[Proceeds]|Gain Loss Percent: [Profit Loss]/[Cost]|
Here it is with bloody duplications:
6/05/2003 AGX 45450 $20,029.60 21/05/2003 AGX 16704
$6,331.22 -$13,698.38 -0.683906817909494
6/05/2003 AGX 45450 $20,029.60 10/07/2003 AGX 28746
$12,618.29 -$7,411.31 -0.37001787354715
6/05/2003 AGX 45450 $20,029.60 7/10/2003 AGX 24193
$18,840.59 -$1,189.01 -5.93626432879339E-02
6/05/2003 AGX 45450 $20,029.60 22/01/2004 AGX 1500
$1,030.05 -$18,999.55 -0.948573611055638
6/05/2003 AGX 45450 $20,029.60 4/03/2004 AGX 23500
$17,595.05 -$2,434.55 -0.121547609537884
15/08/2003 AGX 24193 $15,029.61 21/05/2003 AGX 16704
$6,331.22 -$8,698.39 -0.578750213744734
15/08/2003 AGX 24193 $15,029.61 10/07/2003 AGX 28746
$12,618.29 -$2,411.32 -0.160437962129423
15/08/2003 AGX 24193 $15,029.61 7/10/2003 AGX 24193 $18,840.59 $3,810.98
0.25356479642519
15/08/2003 AGX 24193 $15,029.61 22/01/2004 AGX 1500
$1,030.05 -$13,999.56 -0.931465287522431
15/08/2003 AGX 24193 $15,029.61 4/03/2004 AGX 23500 $17,595.05 $2,565.44
0.170692386562259
3/11/2003 AGX 25000 $18,029.95 21/05/2003 AGX 16704
$6,331.22 -$11,698.73 -0.648849830421049
3/11/2003 AGX 25000 $18,029.95 10/07/2003 AGX 28746
$12,618.29 -$5,411.66 -0.300148364249485
3/11/2003 AGX 25000 $18,029.95 7/10/2003 AGX 24193 $18,840.59 $810.64
4.49607458700662E-02
3/11/2003 AGX 25000 $18,029.95 22/01/2004 AGX 1500
$1,030.05 -$16,999.90 -0.942870057875923
3/11/2003 AGX 25000 $18,029.95 4/03/2004 AGX 23500
$17,595.05 -$434.90 -2.41209764863463E-02
...........................................................
Regards,
Postman.
[MVP] S.Clark said:
Unfortunately, I really couldn't see the repetion, but am assuming that you
had 3 purchases and one sell.
To aggregate value, use the Group By and Totals in a query. Click on the
View menu choice and choose Totals(or click on the Sigma icon, it looks like
a funky E)
