none
Access VB code to find out odd pairs of data RRS feed

  • Question

  • Dear All,

    In my access table there are two columns Column A and Column B

    my requirement is I should find out if there are any rows which have different value for Column A for same value of Column B

    e.g.

    column A Column B

    Value1    Value4

    Value1    Value4

    Value1    Value4

    Value 5  Value 7

    Value1    Value4

    Value 5  Value 7

    Value 6  Value 9

    Value 7 Value 9

    IT should return me last two rows

    Thanks and regards

    Friday, July 10, 2015 3:40 PM

Answers

  • Try the following:

    SELECT tbl1.columnA, tbl1.ColumnB, Count([ColumnA] & [ColumnB]) AS Expr1
    FROM tbl1
    GROUP BY tbl1.columnA, tbl1.ColumnB
    HAVING (((Count([ColumnA] & [ColumnB]))=1));

    Friday, July 10, 2015 6:03 PM
  • According to you description, you can try like below:

    select temp.columnB,count(*) as [Num] from(

    select tbl1.columnA, tbl1.ColumnB

    from tbl1

    group by tbl1.columnA, tbl1.ColumnB

    having (((Count([ColumnA] & [ColumnB]))=1))) as temp

    group by temp.ColumnB

    Tuesday, July 14, 2015 5:58 AM

All replies

  • I think this could be done with a query:

    Select ColumnB, Count(ColumnA)
    From YourTable
    Group By ColumnB
    Having Count(ColumnA) > 1;
    
    Matthias Kläy, Kläy Computing AG

    Friday, July 10, 2015 5:44 PM
  • Try this --

    SELECT Nik_MI.ColumnB, Nik_MI.ColumnA, Nik_MI_1.ColumnA
    FROM Nik_MI INNER JOIN Nik_MI AS Nik_MI_1 ON Nik_MI.ColumnB = Nik_MI_1.ColumnB
    WHERE (((Nik_MI_1.ColumnA)<>[Nik_MI].[ColumnA]))
    ORDER BY Nik_MI.ColumnB, Nik_MI.ColumnA;


    Build a little, test a little

    Friday, July 10, 2015 5:45 PM
  • Hi Matthias,

    This query is returning everything e.g

    Value4  4

    Value7  2

    Value9 2

    I only expect 

    Value9 2

    Friday, July 10, 2015 5:53 PM
  • Try the following:

    SELECT tbl1.columnA, tbl1.ColumnB, Count([ColumnA] & [ColumnB]) AS Expr1
    FROM tbl1
    GROUP BY tbl1.columnA, tbl1.ColumnB
    HAVING (((Count([ColumnA] & [ColumnB]))=1));

    Friday, July 10, 2015 6:03 PM
  • According to you description, you can try like below:

    select temp.columnB,count(*) as [Num] from(

    select tbl1.columnA, tbl1.ColumnB

    from tbl1

    group by tbl1.columnA, tbl1.ColumnB

    having (((Count([ColumnA] & [ColumnB]))=1))) as temp

    group by temp.ColumnB

    Tuesday, July 14, 2015 5:58 AM