Exception Query

[molsen]

Hi All

I have been working on a stock & share reconciliation process, which was originally carried out manually, by looking at three separate reports and ticking them against each other to spot any differences in quantity of shares and their values.

I have imported the three Excel files into Access 2010, and a typical row of information would be:

ISIN (This is a unique code identifying each stock)
Quantity (This should be the same across all three sets of data)
Value (The price of each share multiplied by the Quantity)

So, a typical example would be:

Report One
ISIN: GB0007980591
Quantity: 270,000
Value: £1,341,495

Report Two
ISIN: GB0007980591
Quantity: 270,000
Value: £1,341,495

Report Three
ISIN: GB0007980591
Quantity: 260,000
Value: £1,340,000

You can see from Report three that there is a difference in both the quantity and value. Should all three reports contain the same Quantity in any given row, I do not want to return an error. However if there is a difference, I want the report to return all three data sets so that I can resolve any differences occuring.

With the values, I want to build a tolerance of +/- 3%, so that it will only return the data sets in a report if the values fall outside this tolerance.

Is there a simple equation I can build into a set of queries to do this? Basically I need to compare each set of data against each other, so I thought an individual query would do the job: One for the Quantities, and one for the Values.

Many thanks!

 

[plog]

I would bring all three tables into a query, link them via ISIN, bring down all the fields from all tables, then add a calculated field:

QtyDifference: ((Abs([Report One].[Quantity] - [Report Two].[Quantity]) + Abs([Report One].[Quantity] - [Report Three].[Quantity]))/[Report One].[Quantity]

That will essentially return a percent difference (it will return a decimal equivalent - 3% = .03) between the difference and the value of [Report One].[Quantity]. Underneath that field, put your tolerance and run the query. It will return data with differences in quantity above your threshold.

Actually, there will be a few false positives. If [Report Two].[Quantity] equals [Report Three].[Quantity] and the difference between them and [Report One].[Quantity] is half or more of your threshold, it will show up because it will count that difference twice.



Rewrite that calculated field to use the Value and do it again to find those records.

 

[CJ_London]

Assuming that all 3 reports have the same number of rows/unique ID's then something like this should work

SELECT * 
FROM (rep1 INNER JOIN rep2 ON rep1.ISIN = rep2.ISIN) INNER JOIN rep3 ON rep1.ISIN = rep3.ISIN 
WHERE (rep1.Quantity Not Between rep2.Quantity*0.97 AND rep2.Quantity*1.03) OR (rep1.Quantity Not Between rep3.Quantity*0.97 AND rep3.Quantity*1.03) OR (rep1.Value Not Between rep2.Value*0.97 AND rep2.Value*1.03) OR (rep1.Value Not Between rep3.Value*0.97 AND rep3.Value*1.03)

Note Value is a reserved word, so if you are using it, I would change it