Pair or Match off negatives and positives (1 Viewer)

[Candyman_mm]

Is there a way I can run a query that will pair off items with similar values. For eg; row one has four fields, one of the four feilds has a dollar value of (12,500.89)in row two, exactly underneath is a value for 12,500.89. How can I only pull the values from rows in that one field with similar pair offs and run a report with only those values.

 

[Fornatian]

Include your table twice in the QBE grid and draw a line between Fld1(MatchWhat) and Fld2(AgainstWhat).

This will only show records with matching values in each field.

Ian

 

[Candyman_mm]

The query only matched the same signs....in my case I want to pair off the positives against the negatives.

 

[raskew]

Lookup the ABS() function in the help file.

 

[Candyman_mm]

In my instance, I have one currency field hosting debits and credits. Credits are displayed as absolutes whereas the debits are described as bracketed numbers. There are liked dollar amounts in the one field, the brackets being the only difference. How can I only identify those items that are matching, absolutes and brackets, and place them in one unique table.

 

[Fornatian]

What format is your data held in?
If it is stored as currency, long or integer values then you can use the ABS function to extrapolate the figure.

If you've stored both values as text then you are in need of more help because you will need to employ fire-fighting string manipulation techniques to find your matches.
Ian

 

[raskew]

Two queries:

The first returns the recordID and the absolute value of your currency field, e.g.

Expr1: abs([myAmt])

The second query uses Ian's logic by including the first query twice, e.g.

SELECT qryAbsValue.Expr2 AS Expr1, qryAbsValue_1.ID AS Expr2
FROM qryAbsValue INNER JOIN qryAbsValue AS qryAbsValue_1 ON qryAbsValue.Expr2 = qryAbsValue_1.Expr2
GROUP BY qryAbsValue.Expr2, qryAbsValue_1.ID
HAVING (((Count(*))>1));

Not too elegant, but it works.

 

[Candyman_mm]

Thanx guys, I have an almost perfect dtatbase now.