TABLE1 VS TABLE2...Are all ID's from Table1 in Table2???

[smoothlarryhughes]

Hello all,

I am wondering if anyone can help me out. I've attached a doc that has my first table on the first sheet and my second table on the second sheet.

tblAAM

PORTFOLIO_AAM

PRICE_AAM

NetAMT_AAM

TRADEID_AAM

CANCELLED_AAM

tblHP

PORTFOLIO_HP

PRICE_HP

NetAMT_HP

TRADEID_HP

CANCELLED_HP

i want to create a query that will let me know which tradeid's from tblAAM are not in tblHP. Also...if the tradeid's from tblAAM are in tblHP, are there any differences? example (portfolio.aam=portfolio.hp, price.aam=price.hp(with a penny tolerance), netamt.aam=netamt.hp (with a penny tolerance) cancelled.aam=cancelled.hp)

I know a union is not needed since i am only interested in confirming everything from tblaam is on tblhp, but not vice versa.

recap.zip

[smoothlarryhughes]

One other thing...I guess I would want a "status" column to let me know what things are different...example...if the trade id is missing "trade id missing" or if it is there and something is different "price difference, net amount difference"

i appreciate any help anyone can give on this!

[recyan]

i want to create a query that will let me know which tradeid's from tblAAM are not in tblHP.

Check out Unmatched Query.

One other thing...I guess I would want a "status" column to let me know what things are different...example...if the trade id is missing "trade id missing" or if it is there and something is different "price difference, net amount difference"

Check out, doing an inner join between the two tables & then using IIf to compare across the fields of the 2 tables.
Is there not a possibility of 2 fields from the 2 tables being different at the same time for the same tradeID ?

Thanks

[smoothlarryhughes]

Check out Unmatched Query.


Check out, doing an inner join between the two tables & then using IIf to compare across the fields of the 2 tables.
Is there not a possibility of 2 fields from the 2 tables being different at the same time for the same tradeID ?

Thanks

There is the possibility that more than 1 field is different. I have currently done the inner join between the 2 tables and used alot of iif statements to calculate differences, etc. I just thought maybe there is an easier or more efficient way of coming to the end result. If anyone wants to take a stab at how they would tackle this I would appreciate it. The goal of comparing these 2 tables is that, we have a client that does trades daily, and we have to make sure they are populated in our accounting system. So we compare their end of day trade recap with a report that is run from our accounting system. Anything that isn't in accounting we need to know about and figure out why, which is why I am trying to compare the 2 reports.

[rpeare]

so what does your query currently NOT do that you want it to? Are you trying to filter out all the records that everything matches ok?

What's your current SQL statement?

You have multiple records for both PORTFOLIO_AAM and PORTFOLIO_HP so there's got to be another link you're using or are you summarizing then seeing if there's a difference?

[smoothlarryhughes]

I want to filter out anything that matches, and spit out anything that doesn't match.

Example...if the price doesn't match, show the difference and have a note that says something like "price doesn't match" and if net amt doesn't match say "net amount doesn't match. If both don't match say "price and net amt do not match"...something along these lines. the main link should be trade_ID...this is unique on the tblaam.

[rpeare]

So, again, what is your current SQL statement?

[smoothlarryhughes]

SELECT qryFORMAT_AAM.PORTFOLIO_AAM, qryFORMAT_HP.Portfolio_HP, IIf([PORTFOLIO_AAM]=[PORTFOLIO_HP],"PORT MATCH","PORT MISMATCH") AS PF_MATCH, qryFORMAT_HP.TradeID_HP, qryFORMAT_AAM.TRADEID_AAM, IIf([tradeid_hp]=[tradeid_aam],"ID MATCH","ID MISMATCH") AS ID_Match, IIf(IsNull([NetAMT_AAM]),0,[NetAMT_AAM]) AS Net_AAM, IIf(IsNull([NetAmt_HP]),0,[NetAmt_HP]) AS Net_HP, IIf(IsNull([NetAMT_AAM]),0,[NetAMT_AAM])-IIf(IsNull([NetAmt_HP]),0,[NetAmt_HP]) AS NETDIFF, IIf(Abs([Netdiff])<0.0111,"NET MATCH","NET DIFFERENCE") AS NetCheck, qryFORMAT_AAM.PRICE_AAM, qryFORMAT_HP.Price_HP, IIf(IsNull([price_aam]),0,[price_aam])-IIf(IsNull([price_hp]),0,[price_hp]) AS PriceDiff, IIf(Abs([PriceDiff])<0.01111,"PRICE MATCH","PRICE DIFFERENCE") AS PriceCheck
FROM qryFORMAT_AAM LEFT JOIN qryFORMAT_HP ON qryFORMAT_AAM.TRADEID_AAM = qryFORMAT_HP.TradeID_HP;

[rpeare]

You're referencing two sub queries, I need the SQL code for those as well, please modify them if they include fields that are NOT in your example data.

[smoothlarryhughes]

I'm just using the sub queries to put the two tables into the same format...you can just treat them as if they are 2 tables.

I attached the sample data in case someone wanted to try to throw a quick query together based on that sample data. Then I was going to compare what someone else came up with vs what I have to see if mine is designed properly.

[rpeare]

In your excel file you have two sheets

RECAP = tblAAM = qryFORMAT_AAM
INACCOUNTING = tblHP = qryFORMAT_HP

in other words if I make a query based on the INACCOUNTING sheet of your excel file and name it qryFROMAT_HP I'll be getting everything I need to rebuild your query?

Hope that's the case looking at it now

[rpeare]

use this query

SELECT IIf(IsNull([tradeid_hp]),"TradeID missing from tblHP",(IIf(IsNull(IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Null,Left((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Len((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")))-1)))) AS FinalNotification, IIf(IsNull([tradeid_hp]),"TradeID missing from tblHP",[finalexpr]) AS FinalBase, IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[net_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,") AS BaseExpr, IIf(IsNull([baseexpr]),Null,Left([baseexpr],Len([baseexpr])-1)) AS BuildExpr, IIf(IsNull(IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Null,Left((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Len((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")))-1)) AS FinalExpr, qryFORMAT_AAM.PORTFOLIO_AAM, qryFORMAT_HP.Portfolio_HP, qryFORMAT_HP.TradeID_HP, qryFORMAT_AAM.TRADEID_AAM, IIf(IsNull([NetAMT_AAM]),0,[NetAMT_AAM]) AS Net_AAM, IIf(IsNull([NetAmt_HP]),0,[NetAmt_HP]) AS Net_HP, qryFORMAT_AAM.PRICE_AAM, qryFORMAT_HP.Price_HP, Abs([price_aam]-[price_hp]) AS Expr1
FROM qryFORMAT_AAM LEFT JOIN qryFORMAT_HP ON qryFORMAT_AAM.TRADEID_AAM = qryFORMAT_HP.TradeID_HP
WHERE (((IIf(IsNull([tradeid_hp]),"TradeID missing from tblHP",(IIf(IsNull(IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Null,Left((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")),Len((IIf([portfolio_aam]=[portfolio_hp],Null,"Port,") & IIf(Abs([netamt_aam]-[netamt_hp])<=0.01,Null,"Net,") & IIf(Abs([price_aam]-[price_Hp])<=0.000001,Null,"Price,")))-1))))) Is Not Null));

I left several calculation fields in there so you can see how I built the expressions

EDIT: I set the threshhold for price difference pretty low (.000001) to make sure it worked you'd have to change that to match whatever you want.