Query to Combine Records if one field is the same

smoothlarryhughes

New Member
Joined
Feb 12, 2009
Messages
39
I'm sure this is easy...but any help would be great.

If I have a table with these records:

a b c d e f
1 2 3 4 e f

If column 5 is equal combine to look like this:

a1 b2 c3 d4 e ff

Thanks!
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
no, it's not easy as a matter of fact, but you can do it.

1) how many records in the table?
2) only column 5 that has to match?
3) matching WHERE? from record to record? all records have to be the same? row to row at the beginning? in the middle? consecutively from a certain point? at the end? CLARIFY.
 

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
From that recordset in the picture, write this query for it and see if it works:
Code:
SELECT DISTINCT tbl.fld1, tbl.fld2, tbl.fld3, "combo of above" as fld4, 
   "combo of above" as fld5,
      tbl.fld6, tbl.fld7, tbl.fld8, tbl.fld9

FROM tbl WHERE tbl.fld7 = 0
This will give you what you want (I think, from juding by the picture). Replace TBL and FLD# with your table and field names.

It looks like an accounting journal and its entries. Is that what you're working with?
 

smoothlarryhughes

New Member
Joined
Feb 12, 2009
Messages
39

ADVERTISEMENT

i'm working with a reconciliation system that has trouble comparing data when the identifier is different.
your query is only pulling records where fld7 is 0. I need it to combine records when fld1 is the same. Any other ideas.

Thanks alot!
 

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
i know that is what you want, but WHICH RECORD do you want to display? that's the real question here. If all of the records are going to be different, which they obviously are, you need to specify which record you want to display, out of the 2, or 3, or 4 records that you have.
 

smoothlarryhughes

New Member
Joined
Feb 12, 2009
Messages
39

ADVERTISEMENT

i want to be able to display a combination of both records...only 2 records max will have the same identifier...so basically the query will have to look at each record in column 1 and if the identifier is the same in each, combine the two records. Here is a better example. Our system will identify both records that I need combined into one record. Example the CaseID isn't always going to show up twice. So when it does I want it to put the records on one line. Example CaseID 112588 (line 17 and 18)...want it to look like the result below after the query is run, but also show everything else that is not being combined with anything.


Sheet1

<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: 49px"><COL style="WIDTH: 54px"><COL style="WIDTH: 80px"><COL style="WIDTH: 100px"><COL style="WIDTH: 132px"><COL style="WIDTH: 63px"><COL style="WIDTH: 70px"></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>CaseID</TD><TD>Status</TD><TD>Aging (days)</TD><TD>SECURITY ID</TD><TD>SECURITY ID CUSIP</TD><TD>BP2S</TD><TD>Custodian</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">112347</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">752</TD><TD>195998AC3</TD><TD></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">492809.44</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">112372</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">752</TD><TD>DU00XX3287PP</TD><TD>S02120126</TD><TD style="TEXT-ALIGN: right">15711063</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">112375</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">752</TD><TD>XXX3062D</TD><TD>195998AC3</TD><TD style="TEXT-ALIGN: right">1058348</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">112588</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">751</TD><TD>195998AC3</TD><TD></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">1442782</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">112588</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">751</TD><TD>XXX3062D</TD><TD>195998AC3</TD><TD style="TEXT-ALIGN: right">2212116</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


RESULT NEEDED (COMBINATION OF THE TWO RECORDS) and if any other CaseID's are the same they would be combined too.

Sheet1

<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: 49px"><COL style="WIDTH: 54px"><COL style="WIDTH: 80px"><COL style="WIDTH: 100px"><COL style="WIDTH: 132px"><COL style="WIDTH: 63px"><COL style="WIDTH: 70px"></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>CaseID</TD><TD>Status</TD><TD>Aging (days)</TD><TD>SECURITY ID</TD><TD>SECURITY ID CUSIP</TD><TD>BP2S</TD><TD>Custodian</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">112347</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">752</TD><TD>195998AC3</TD><TD></TD><TD style="TEXT-ALIGN: right">0</TD><TD style="TEXT-ALIGN: right">492809.44</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">112372</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">752</TD><TD>DU00XX3287PP</TD><TD>S02120126</TD><TD style="TEXT-ALIGN: right">15711063</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">112375</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">752</TD><TD>XXX3062D</TD><TD>195998AC3</TD><TD style="TEXT-ALIGN: right">1058348</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">112588</TD><TD>Pending</TD><TD style="TEXT-ALIGN: right">751</TD><TD>195998AC3</TD><TD>195998AC3</TD><TD style="TEXT-ALIGN: right">2212116</TD><TD style="TEXT-ALIGN: right">1442782</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
 

ajetrumpet

Banned for being rude
Joined
Apr 12, 2008
Messages
569
Office Version
  1. 365
  2. 2016
  3. 2007
Platform
  1. Windows
tHIS IS next to impossible with access larry. i'm sorry to tell you that, but it would take a lot of VBA to do. It is probably better done in excel. I can write a function for it, but it takes a lot of time to figure out. perhaps someone else here who has the time to do it for you will take a stab. It can be done with a function call within the query's sql, as I have done it.
 

smoothlarryhughes

New Member
Joined
Feb 12, 2009
Messages
39
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>
 

gaj104

Well-known Member
Joined
Nov 9, 2002
Messages
864
I had a bash at the CASEID table, and with the assumption there are never more than 2 of the same CASEID identifiers in the table the following SQL produces the following results, which is what i think you are looking for (btw ive named the table tbl_TEST);

SQL:

Code:
SELECT T4.CASEID,
       T4.F_STATUS,
       T4.[F_AGING (DAYS)],
       T4.[F_SECURITY ID],
       T4.[F_SECURITY ID CUSIP],
       T4.F_BP2S,
       T4.F_CUSTODIAN
FROM   (SELECT   T3.CASEID,
                 First(T3.S_STATUS)              AS F_STATUS,
                 First(T3.[S_AGING (DAYS)])      AS [F_AGING (DAYS)],
                 First(T3.[S_SECURITY ID])       AS [F_SECURITY ID],
                 First(T3.[S_SECURITY ID CUSIP]) AS [F_SECURITY ID CUSIP],
                 First(T3.S_BP2S)                AS F_BP2S,
                 First(T3.S_CUSTODIAN)           AS F_CUSTODIAN
        FROM     (SELECT T2.CASEID,
                         T2.S_STATUS,
                         T2.[S_AGING (DAYS)],
                         T2.[S_SECURITY ID],
                         T2.[S_SECURITY ID CUSIP],
                         T2.S_BP2S,
                         T2.S_CUSTODIAN
                  FROM   (SELECT T1.CASEID,
                                 Iif([TBL_TEST].[STATUS] = [TBL_TEST_1].[STATUS],
                                     [TBL_TEST].[STATUS],[TBL_TEST].[STATUS] & [TBL_TEST_1].[STATUS]) AS S_STATUS,
                                 Iif([TBL_TEST].[AGING (DAYS)] = [TBL_TEST_1].[AGING (DAYS)],
                                     [TBL_TEST].[AGING (DAYS)],[TBL_TEST].[AGING (DAYS)] & [TBL_TEST_1].[AGING (DAYS)]) AS [S_AGING (DAYS)],
                                 Iif([TBL_TEST].[SECURITY ID] = [TBL_TEST_1].[SECURITY ID],
                                     [TBL_TEST].[SECURITY ID],[TBL_TEST].[SECURITY ID] & [TBL_TEST_1].[SECURITY ID]) AS [S_SECURITY ID],
                                 Iif([TBL_TEST].[SECURITY ID CUSIP] = [TBL_TEST_1].[SECURITY ID CUSIP],
                                     [TBL_TEST].[SECURITY ID CUSIP],[TBL_TEST].[SECURITY ID CUSIP] & [TBL_TEST_1].[SECURITY ID CUSIP]) AS [S_SECURITY ID CUSIP],
                                 Iif([TBL_TEST].[BP2S] = [TBL_TEST_1].[BP2S],[TBL_TEST].[BP2S],
                                     [TBL_TEST].[BP2S] + [TBL_TEST_1].[BP2S]) AS S_BP2S,
                                 Iif([TBL_TEST].[CUSTODIAN] = [TBL_TEST_1].[CUSTODIAN],
                                     [TBL_TEST].[CUSTODIAN],[TBL_TEST].[CUSTODIAN] + [TBL_TEST_1].[CUSTODIAN]) AS S_CUSTODIAN
                          FROM   TBL_TEST AS TBL_TEST_1
                                 INNER JOIN (TBL_TEST
                                             INNER JOIN (SELECT   TBL_TEST.CASEID
                                                         FROM     TBL_TEST
                                                         GROUP BY TBL_TEST.CASEID) AS T1
                                               ON TBL_TEST.CASEID = T1.CASEID)
                                   ON TBL_TEST_1.CASEID = T1.CASEID) AS T2
                  WHERE  (((T2.S_STATUS) IS NOT NULL)
                          AND ((T2.[S_AGING (DAYS)]) IS NOT NULL)
                          AND ((T2.[S_SECURITY ID]) IS NOT NULL)
                          AND ((T2.[S_SECURITY ID CUSIP]) IS NOT NULL)
                          AND ((T2.S_BP2S) IS NOT NULL)
                          AND ((T2.S_CUSTODIAN) IS NOT NULL))) AS T3
        GROUP BY T3.CASEID) AS T4

RESULTS:
Test.xls
ABCDEFG
1CASEIDF_STATUSF_AGING (DAYS)F_SECURITY IDF_SECURITY ID CUSIPF_BP2SF_CUSTODIAN
2112372Pending752DU00XX3287PPS0212012615,711,063.00-
3112375Pending752XXX3062D195998AC31,058,348.00-
4112588Pending751XXX3062D195998AC3195998AC32,212,116.001,442,782.00
Sheet2


Does this work for you?

G
 

Watch MrExcel Video

Forum statistics

Threads
1,122,669
Messages
5,597,474
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top