Results 1 to 3 of 3

Thread: New

  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Red face Unanswered: New

    Hello everybody,
    I am new and without experience doing queries in Access. A friend advise me to join this forum.
    My problem is that I need to do a query to show only the error in a sequence of numbers in two columns. The number from "RangeTo" has to be in the next row of the column to the left. If there is another number it is consider a "0".
    Can someone be so kind and help me please.
    Mimi

    |ID | |RangeFrom ||RangeTo|
    |001| | 0 | |4.5 |
    |002| | 4.5 | |6.8 |
    |003| | 6.8 | |8.5 |
    |004| | 8.5 | |10.2 |
    |005| |11.2 | |12.6 |
    |006| |12.6 | |4.5 |

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This is not the Access forum, but the Microsoft SQL Server forum.

    The SQL below will not work on Access. You will at least have to convert the CASE construct in an IIF statement.

    You can join the table to itself, and then check if the value of the RangeFrom column equals that of the previous RangeTo column.

    It will not show the first row. If you want that, use a SELECT of the first record with a UNION ALL with the below SELECT.
    Code:
    SELECT	T.Id, 
    	CASE WHEN F.RangeTo = T.RangeFrom THEN T.RangeFrom ELSE 0 END as T.RangeFrom, 
    	T.RangeTo
    FROM DaTable as T --To table
    	INNER JOIN DaTable as F ON  -- From table
    		T.Id = F.Id + 1
    ORDER BY T.Id
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    Thanks Wim for your FAST reply.
    I will try to convert it to an IIF statement.
    You are a n !

Posting Permissions

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