Results 1 to 7 of 7

Thread: MS Access Query

  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Smile Unanswered: MS Access Query

    Hi All,

    Can any one help me in creating query in MS access for the below formula;

    =IF(A2=A3,0,1)

    Example:
    The query has to match the first and second record if it matches then it will update as "0".


    Can anyone tell how i can do that?

    Thanks i advance.


    Regards,

    Vinoth

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Your "formula" looks like something out of Excel or another spreadsheet. It is meaningless in the context of Access. A query works on tables (or other queries) that have columns and each column has a name.

    You cannot refer to the contents of a field with a "A1" notation. For the column you have to specify its name and for the row there is nothing like an absolute row number: it depends on the current index set on the table (if any) as well as on the sort order you specify in the query (again if any).
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    23
    Are you doing this on a form in an unbound field or in a query? either way the formula is the same.

    =IIF([Field Title 1] = [Field Title 2], "0","1")

  4. #4
    Join Date
    Sep 2011
    Posts
    3
    Actually both are from the same column value. in that case which query i need to use?

  5. #5
    Join Date
    Apr 2011
    Posts
    23
    Quote Originally Posted by vinwin06 View Post
    Actually both are from the same column value. in that case which query i need to use?
    Ah didnt realise all the data was in the same column. You may not be able to do it.

    Whats the data? and why do you need to compare it?

  6. #6
    Join Date
    Sep 2011
    Posts
    3
    I need to compare whether the PO numbers are unique and if the first and second value are not same then it will update as "1", its same then it vl update as "0".

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Usually this kind of operation is performed using updates queries.

    1. To change the value of a column to 1 for rows with duplicate values in a column:
    Code:
    UPDATE TableName
    SET ColumnToUpdate = 1
    WHERE ColumnToCheck IN (
        SELECT TableName.ColumnToCheck
        FROM TableName
        WHERE ((TableName.ColumnToCheck In (SELECT [ColumnToCheck] FROM [TableName] As Tmp GROUP BY [ColumnToCheck] HAVING Count(*)>1 )))
        ORDER BY TableName.ColumnToCheck
    );
    2. To change the value of a column to 0 for rows with no duplicate values in a column:
    Code:
    UPDATE TableName
    SET ColumnToUpdate = 1
    WHERE ColumnToCheck IN (
        SELECT TableName.ColumnToCheck
        FROM TableName
        WHERE ((TableName.ColumnToCheck In (SELECT [ColumnToCheck] FROM [TableName] As Tmp GROUP BY [ColumnToCheck] HAVING Count(*)=1 )))
        ORDER BY TableName.ColumnToCheck
    );
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •