CODEWITHSUNDEEP
sqlms-accessms-access-2010
Pavan Kumar
Pavan Kumar

Reputation: 51

In MSAccess fetch same values together from 3 columns in a table and display

I am facing a problem matching values of column1 with column2 and column3. All I want is to display all the data from column1 with matching data infront of the matching values of column1. For Example:

 +----------------+--------------+
 |SB_AccNo        |CLAIM INITATED|
 +----------------+--------------+
 |122000031730    |1854.36       |
 |122000031730    |4172.31       |
 |122000031730    |5099.49       |
 |122000069210    |54.56         |
 |122000069210    |54.56         |
 |122000069210    |14998.50      |
 |122000069210    |27317.25      |
 +----------------+--------------+

 +---------------+---------------+
 |SB_AccNo1      |CLAIM INITATED1|
 +---------------+---------------+
 |122000031730   |1483.00        |
 |122000031730   |3338.00        |
 |122000031730   |4080.00        |
 |122000069210   |11999.00       |
 |122000069210   |21854.00       |
 |122000070281   |1091.00        |
 |122000070281   |1091.00        |
 +---------------+---------------+    

 +---------------+---------------+
 |SB_AccNo2      |CLAIM INITATED2|
 +---------------+---------------|
 |122000031730   |371.00         |
 |122000031730   |834.00         |
 |122000031730   |1019.00        |
 |122000069210   |3000.00        |
 |122000069210   |5463.00        |
 |122000070281   |273.00         |
 |122000070281   |273.00         |
 |122000070281   |954.00         |
 |122000070281   |3272.00        |
 +---------------+---------------+

I want to match the columns SB_AccNo,SB_AccNo1 and SB_AccNo2 for same values. Each column might consist of same values as multiple times. I want them to display only when matched. Can anyone help me out?? The output should be like

SB_AccNo    CLAIM INITATED  SB_AccNo1   CLAIM INITATED1 SB_AccNo2CLAIM INITATED2    
122000031730    1854.36    122000031730   1483.00         122000031730  371.00  
122000031730    4172.31    122000031730   3338.00         122000031730  834.00  
122000031730    5099.49    122000031730   4080.00         122000031730  1019.00 
122000069210    54.56      122000069210   11999.00        122000069210  3000.00 
122000069210    54.56      122000069210   21854.00        122000069210  5463.00 
122000069210    14998.50   122000070281   1091.00         122000070281  273.00  
122000069210    27317.25   122000070281   1091.00         122000070281  273.00  
122000070281    1363.50    122000070281   3818.00         122000070281  954.00  
122000070281    1363.50    122000070281   13090.00        122000070281  3272.00 
122000070281    4772.25    122000070281   16362.00        122000070281  4091.00 
122000070281    16362.00   122000070281   17453.00        122000070281  4363.00

Upvotes: 1

Views: 54

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19817

By the sounds of it you need something like this SQL.
Just check if each of the values match in the WHERE clause. 

 SELECT   SB_AccNo, [CLAIM INITATED]
        , SB_AccNo1, [CLAIM INITATED1]
        , SB_AccNo2, [CLAIM INITATED2]
 FROM   MyTable
 WHERE  SB_AccNo = SB_AccNo1 AND
        SB_AccNo = SB_AccNo2 AND 
        SB_AccNo1 = SB_AccNo2

Edit: Have added field names to SQL.
Are you sure it's INITATED and not INITIATED?

Upvotes: 1

Related Questions