Reconciling records in the same table

[Guest]

Hi,
I'm trying to find the best way to reconcile records in a table which
contain values that (should) net out to zero, using the TRADEID. I would like
to:
A) have the query result showing any records that don't net out (I've ben
using the sum function but would like to see if there is a way to match both
the TRADEID and the TOTAL field to match record amount for record amount)
B) using a separate query, return all the records that have the same Total
without having the same TRADEID. - the table below would return only the
last 2 records.
I'm having a hard time trying to compare records within the same table. Is
there anyway to do this without having to create 2 tables from that one
table, and creating a query to compare the 2 fields.
Thanks

TRADEID TOTAL
215690 $5375
215690 ($5375)
217032 ($6,825.00)
217032 $4,825.00
217032 $2,000.00
497541L $80.00
843415L $80.00

 

[Jeff Boyce]

Frank

I may not be understanding what your data is structured like, nor what
you're trying to do, so take this with a grain of salt...

If you create a new query, add the table in question, add the TRADEID and
TOTAL fields, and click on the Totals toolbar button, you'll get a Totals
row in the grid.

Use "GroupBy" for the TRADEID field, and "Sum" for the TOTAL field.

You should get a "net" for each TRADEID. All those that net to "0" are
balanced, and ...


Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,
Thanks for your response. That's how this has been previously done. Now I
need to be able to match records one for one -- that is ID 12243 with a value
of $1000 matches ID 12243 with a value of -$1000, but niether would match or
be lumped into a sum with a record ID 12243 with a sum of $500. -- ie I can
only cancle out records that have an exact match with the value other wise I
have to keep the rest and make a decison and with other queries on what to do
with the data.
Thanks

 

[Guest]

Try this --
SELECT FRANK.TOTAL, Count(FRANK.TOTAL) AS CountOfTOTAL
FROM FRANK
GROUP BY FRANK.TOTAL
HAVING (((Count(FRANK.TOTAL))>1));

Or this --
SELECT FRANK.TOTAL, Count(FRANK.TOTAL) AS CountOfTOTAL, First(FRANK.TRADEID)
AS FirstOfTRADEID, Last(FRANK.TRADEID) AS LastOfTRADEID
FROM FRANK
GROUP BY FRANK.TOTAL
HAVING (((Count(FRANK.TOTAL))>1));

 

[Guest]

Maybe something like this would be geeting there --
SELECT FRANK.TRADEID, Sum(FRANK.TOTAL) AS SumOfTOTAL, Max(FRANK_1.TOTAL) AS
MaxOfTOTAL
FROM FRANK INNER JOIN FRANK AS FRANK_1 ON FRANK.TRADEID = FRANK_1.TRADEID
GROUP BY FRANK.TRADEID;

 

[John Spencer]

The following might work to give you what you want.

SELECT F3.TradeID
FROM Frank as F3
LEFT JOIN (
SELECT DISTINCT F1.TradeID as TID, F1.Total as Ttl
FROM FRANK as F1 INNER JOIN FRANK as F2
ON F1.TradeID = F2.TradeID
AND F1.Total = -F2.Total) as F4
ON F3.TradeId = F4.TID
AND F3.Total = F4.Ttl
WHERE F4.TID is Null and F4.Ttl is Null

 

[Jeff Boyce]

Frank

Perhaps I'm misunderstanding "match". I thought you only needed to know
when the net for ID 12243 is other than 0.

Regards

Jeff Boyce
Microsoft Office/Access MVP