Greetings, I am trying to reconcile two tables in Access. Each table contains AccountNames, Trade Prices, Amounts, and Reconciled.
What I would like to do is be able to identify in both tables if there is a match, multi, single or no match. I have the single done with no problems however, The multi and no match is a problem.
What I need to do is be able to match trade to trade if the AccountNames are equal, the Trade Prices are equal and the SUM of the Amounts are equal and if so mark each one of the trades in t1.Reconciled as multi and then do the reverse and mark the multi netting trades in t2 as multi.
I can handle the updates from multiple lines from t1 to a single line in t2 and vice versa but I can't handle updates from multiple lines from t1 to multiple lines in t2.
I know i'm confusing.
Any help is greatly appreciated.

Thanks

t1
AcctName TradePrice Amount Reconciled
101 100 57000 Single
101 101 60000 Single
101 102 20000 Single
102 101.5 20000 Multi these are a multi as the Acct, Trade Price and Sum of amount net out to the same account, price and sum of amounts in t2
102 101.5 5000 Multi I can't figure out how to handle these
102 100 65000 Single
102 103 70000 Single
103 99 2000 Single
104 100 1000 Single
104 101 2000 Single
104 102 3000 Single
104 103 4000 Single
104 104 5000 Single
104 105 6000 Single
104 106 7000 Single
105 101 10000 No Match No match as there is no 105 account on t2
106 100 10000 Multi
106 100 10000 Multi I can handle this type of match as there is only 1 account for 20k on t2

t2
AcctName TradePrice Amount Reconciled
101 100 57000 Single
101 101 60000 Single
101 102 20000 Single
102 101.5 20000 Multi
102 101.5 5000 Multi
102 100 65000 Single
102 103 70000 Single
103 99 2000 Single
104 100 1000 Single
104 101 2000 Single
104 102 3000 Single
104 103 4000 Single
104 104 5000 Single
104 105 6000 Single
104 106 7000 Single
106 100 20000 Multi I can handle this

Write 2 queries that summarize each table by AcctName and TradePrice and include a record count and a sum of the Amount.

Write a 3rd query that joins the 1st two on AcctName and TradePrice and include both record counts and add a flag if the Amounts are equal. Run this as a Make Table and add a primary index to the new table on AcctName+TradePrice.

Run an update against each table which has a join to the new table on AcctName and TradePrice. Make sure your t1 and t2 tables have a primary/unique index as well. Access has odd rules about when updates are allowed with joined tables. Your Where clause should check the amounts are equal and you can use an IIf to determine Single versus Multi based on the record counts.