Maybe this would be easier...any idea how most systems work where you pull in two sets of data and compare the two? Example:
I want to compare these two sets of data:
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 58px"><COL style="WIDTH: 59px"><COL style="WIDTH: 79px"><COL style="WIDTH: 64px"><COL style="WIDTH: 95px"><COL style="WIDTH: 96px"><COL style="WIDTH: 90px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="FONT-WEIGHT: bold">Security</TD><TD style="FONT-WEIGHT: bold">Shares</TD><TD></TD><TD style="FONT-WEIGHT: bold">Bank Account</TD><TD style="FONT-WEIGHT: bold">Security Bank</TD><TD style="FONT-WEIGHT: bold">Shares Bank</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">123</TD><TD>aa</TD><TD style="TEXT-ALIGN: right">500,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>aa</TD><TD style="TEXT-ALIGN: right">500,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">123</TD><TD>bb</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>bb</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">123</TD><TD>cc</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>cc</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">123</TD><TD>dd</TD><TD style="TEXT-ALIGN: right">200,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>dd</TD><TD style="TEXT-ALIGN: right">300,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">123</TD><TD>ee</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>ee</TD><TD style="TEXT-ALIGN: right">200,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">123</TD><TD>ff</TD><TD style="TEXT-ALIGN: right">999,999.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>ff</TD><TD style="TEXT-ALIGN: right">999,999.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">123</TD><TD>zz</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>gg</TD><TD style="TEXT-ALIGN: right">1,234,567.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">123</TD><TD>yy</TD><TD style="TEXT-ALIGN: right">200,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">2232</TD><TD>hh</TD><TD style="TEXT-ALIGN: right">666,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">234</TD><TD>aa</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">7787</TD><TD>aa</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">234</TD><TD>bb</TD><TD style="TEXT-ALIGN: right">700,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">7787</TD><TD>bb</TD><TD style="TEXT-ALIGN: right">600,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">234</TD><TD>yy</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD></TD><TD style="TEXT-ALIGN: right">7787</TD><TD>zz</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD></TR></TBODY></TABLE>
Also would have a table showing which accounts are alike (different account numbers from my system to the bank)
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 90px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold">Table for similar accounts</TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="FONT-WEIGHT: bold">Bank Account</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">234</TD><TD style="TEXT-ALIGN: right">7787</TD></TR></TBODY></TABLE>
This is the result I would like:
I know a union query would probably work for this but how can I have it compare both the account number and the security (because sometimes more than one account holds the same security and only want to compare like accounts) I also would like both the account and bank account to show up on each row if possible for those where only my system or the bank is showing a security like in line 24,25,26,27.
<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 95px"><COL style="WIDTH: 96px"><COL style="WIDTH: 90px"><COL style="WIDTH: 96px"><COL style="WIDTH: 79px"><COL style="WIDTH: 90px"><COL style="WIDTH: 96px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD><TD>K</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</TD><TD>Result Needed</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</TD><TD style="FONT-WEIGHT: bold">Account</TD><TD style="FONT-WEIGHT: bold">Bank Account</TD><TD style="FONT-WEIGHT: bold">Security</TD><TD style="FONT-WEIGHT: bold">Security Bank</TD><TD style="FONT-WEIGHT: bold">Shares</TD><TD style="FONT-WEIGHT: bold">Shares Bank</TD><TD style="FONT-WEIGHT: bold">Difference</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>aa</TD><TD>aa</TD><TD style="TEXT-ALIGN: right">500,000.00 </TD><TD style="TEXT-ALIGN: right">500,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>bb</TD><TD>bb</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>cc</TD><TD>cc</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>dd</TD><TD>dd</TD><TD style="TEXT-ALIGN: right">200,000.00 </TD><TD style="TEXT-ALIGN: right">300,000.00 </TD><TD style="TEXT-ALIGN: right">(100,000.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">22</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>ee</TD><TD>ee</TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD style="TEXT-ALIGN: right">200,000.00 </TD><TD style="TEXT-ALIGN: right">(100,000.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">23</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>ff</TD><TD>ff</TD><TD style="TEXT-ALIGN: right">999,999.00 </TD><TD style="TEXT-ALIGN: right">999,999.00 </TD><TD style="TEXT-ALIGN: right">- </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">24</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>zz</TD><TD></TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">100,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">25</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD>yy</TD><TD></TD><TD style="TEXT-ALIGN: right">200,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">200,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">26</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD></TD><TD>gg</TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">1,234,567.00 </TD><TD style="TEXT-ALIGN: right">(1,234,567.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">27</TD><TD style="TEXT-ALIGN: right">123</TD><TD style="TEXT-ALIGN: right">2232</TD><TD></TD><TD>hh</TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">666,000.00 </TD><TD style="TEXT-ALIGN: right">(666,000.00)</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">28</TD><TD style="TEXT-ALIGN: right">234</TD><TD style="TEXT-ALIGN: right">7787</TD><TD>aa</TD><TD>aa</TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">29</TD><TD style="TEXT-ALIGN: right">234</TD><TD style="TEXT-ALIGN: right">7787</TD><TD>bb</TD><TD>bb</TD><TD style="TEXT-ALIGN: right">700,000.00 </TD><TD style="TEXT-ALIGN: right">600,000.00 </TD><TD style="TEXT-ALIGN: right">100,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">30</TD><TD style="TEXT-ALIGN: right">234</TD><TD style="TEXT-ALIGN: right">7787</TD><TD>yy</TD><TD></TD><TD style="TEXT-ALIGN: right">100,000.00 </TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">100,000.00 </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">31</TD><TD style="TEXT-ALIGN: right">234</TD><TD style="TEXT-ALIGN: right">7787</TD><TD></TD><TD>zz</TD><TD style="TEXT-ALIGN: right">- </TD><TD style="TEXT-ALIGN: right">400,000.00 </TD><TD style="TEXT-ALIGN: right">(400,000.00)</TD></TR></TBODY></TABLE>