Working on simple set of data.. Query Help

[CodyMonster]

I was wondering if someone could help me with a query problem.
Its been a few years since I had to work in Access so my problem solving skills are very rusty.


I'm looking to find deals in a table of data that offset each (final deal amount negative and positive). I created the absolute value column, and ran a query looking for duplicates. This is working out fine. However, I'm trying to exclude the ones that have three instances, for example Abs Volume 5077. I was thinking of doing a count expression, but when I try to do it, it still has each line of data as independent due to the strategy number being different. But I need the strategy number, so I can not remove it.


If someone could throw me some ideas to get me started that would be great..

Thanks

Strategy Number	Type of Trade	Contract Price	Market Price	FOB Price	Final Deal Amount	Abs Volume
2012/695	US DOM	91.6600	86.1700	91.6600	(183,320.00)	2000
2012/9	US DOM	91.6600	86.1700	91.6600	183,320.00	2000
2012/597	US DOM	106.5352	95.7800	106.5352	(377,978.44)	3547.92
2012/548	US DOM	106.5352	95.7800	106.5352	377,978.44	3547.92
2012/508	US DOM	121.6190	118.8300	121.6190	(602,786.33)	4956.35
2012/13	US DOM	121.6190	118.8300	121.6190	602,786.33	4956.35
2012/271	PHYS-(1232)-	120.4975	113.8704	120.4975	(611,765.81)	5077
2012/181	PHYS-(1233)-	120.4975	113.8704	120.4975	611,765.81	5077
2012/271	PHYS-DEC Slate DCR (948)-	120.4975	113.8704	120.4975	611,765.81	5077

<CAPTION>Find duplicates for Daily Profit & Loss</CAPTION> <THEAD>
</THEAD><TBODY>
</TBODY><TFOOT></TFOOT>

 

[Corticus]

You're on the right track.

Create another query that calculates the count of abs volume grouping on strategy number.
Something like
SELECT [strategy number], count([abs volume]) as absCount FROM tableName GROUP BY [strategy number]
This is just an example to illustrate the tactic, your SQL will vary.

Then, join that new query to this query on strategy number, add in the count of abs volume, and add your criteria to the new field.

edit:
for the new query, I got it backward, you want every field but strategy number. I think you'll get the point.

 

[CodyMonster]

So, I'm actually getting this to work, however I'm still going through some issues.


I'm able to use the absolute volume to get the duplicates.
But, I need the volume to equal zero. When you look at the row in red, you can see the volume is still showing a dup, as it should since it is the absolute value. But since the numbers are positve they do not equal to zero.

I need to exclude the volumes where the duplicates do not add to zero.

Any help would be great.. Thanks everyone for the help.

ID	Strategy Number	Delievery Period	Market Price	Contract Price	Type of Trade	Other P&L	Deal Amount	Final Deal Amount	Volume	Abs Volume
1055	2012/296	27 DEC 2011	94.495	112.954	PHYS-(1081) D134-	0	67114928.65	67114928.65	-594179.3	594179.3
2739	2012/214	09 - 12 DEC 2011	94.495	112.954	PHYS-(1080)-	-124777.65	-67239706.3	-67114928.65	594179.3	594179.3
2740	2012/214	09 - 12 DEC 2011	94.495	113.234	PHYS-(1082)-	0	67281298.86	67281298.86	-594179.3	594179.3
3338	2012/215	09 - 12 DEC 2011	94.495	113.234	PHYS-(1081)-	0	-67281298.86	-67281298.86	594179.3	594179.3
2816	2012/411	04 - 06 FEB 2012	84.96	118.1338	PHYS-March Slate - PBR (408)-	0	70726820.65	70726820.65	-598701	598700.97
3565	2012/408	04 - 06 FEB 2012	123.15	118.1338	PHYS-A Perseus - ARL (411) #190-	0	-70726820.65	-70726820.65	598700.97	598700.97
1047	2012/858	19 - 19 JUN 2012	92	89.8305	PHYS-AEGEAN BLUE-	-2022000	-55920300	-53898300	600000	600000
1049	2012/866	28 - 28 JUN 2012	92	89.8305	PHYSICAL	-2022000	-55920300	-53898300	600000	600000
1057	2012/480	20 - 25 MAR 2012	94.495	121.195	PHYS-303	0	72843368.81	72843368.81	-601042.7	601042.69
2871	2012/481	20 - 25 MAR 2012	94.495	121.195	PHYS-(480)-	42072.9883	-72801295.83	-72843368.81	601042.69	601042.69

<TBODY>
</TBODY>