You can relate the tables as Table1::account = Table2::Account
Then in table1 perform a script of:
Perform Find [ Table2::account ... and just type * ... meaning find all Table1 records with a matching account in table 2. ]
Show Omitted Only ... meaning show those without match
... will give you records in table 1 which aren't in Table2. Reverse to table2 layout, rinse and repeat in table 2.
Depending upon how you use the information and whether this is a one-time thing or on-going process, you can colorized accounts without a match also. The best approach would depend upon your next steps with the information. :-)
Thank you very much!!!
I assume this is a one-time data migration or cleanup project? In general, accounts should be in same table using year field or other field such as category to segregate them. Let us know if there are other questions. There are many great folks here ready to assist. :-)
Thanks...if they are in the same field (one table as suggested), how would I modify your solution to solve the same problem...a "double find" looking for account number AND fiscal year, omitting found set also?
I am not sure I understand, Doug. I am suggesting that, in general, all accounts should be in the same table. But if you have different years (which also contains information specific to the year) then you should have a related table for the year data (which is multiple to the one account). If you have accounts in table 2 with Account Number 1002 and Year 2011 and another record with Account Number 1002 and Year 2012 and you want to know if table 1 has Account Number 1002 with Year 2011, then include the year in the relationship to restrict the matches to also matching on year.
Can you explain more what you are attempting to achieve and your current situation and what the tables represent? Is one the General Ledger and the other LineItems for example or are they both an Accounts table that should be merged or ?? Hang in there ... we'll walk you through it.
BTW, you can join any two table occurrences - even join a table occurrence to itself by simply duplicating it in the graph (known as a self-join). So if all the records were in the same table, you can still create the relationship to 'filter' your results.
Message was edited by: LaRetta
I think that I'm on the right track now...I was messing around with the "self join" and, along with your first suggestion (use of find omitted) I can get where I need to be.
I was over-complicating things and you got me on the right track. I continue this post if I get "locked up" again.
Thanks again for your help!!!