Results 1 to 7 of 7

Thread: Query question

  1. #1
    Join Date
    Apr 2005
    Posts
    21

    Unanswered: Query question

    I am new to the database world and I have two questions. I am using MS Access

    1) I have a table (Say table1) which has 2 columns (Col1,Col2). Col3 is

    generated on fly by using an expression based on col1 and col2 like

    (Col1/Col2) AS Col3 in the select statement. Some of the values in Col1,Col2

    make calaculated value to become infinity eg Col2 is zero. I just want to

    know if I can use any conditional statement so that error won't occur, i.e.

    if the value of col2 is zero then calculated value should result in zero.

    2) I also want to filter on calculated column col3. I want to say in my where

    clause show only those results where Col3 > 10.


    I suspect that I cannot use like this "Select Col1,Col2,(Col1/Col2) AS Col3

    from table1 where Col3 > 10" . If I use like this then MS Access ask to enter value for Col3



    Thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    iif might be appropriate
    if you are using NULL the nz function may be appropriate

    set the iif statement as the source for your derived column
    Col3: iif([col1]=0 OR [col2]=0,0,[Col1]/[Col2])

  3. #3
    Join Date
    Apr 2005
    Posts
    21
    Thanks for the quick reply. I will try this iif statement. Can I filter on Col3 also ??

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Actually, healdem wasn't talking SQL-ese, he was talking Access Query-ese. In SQL, the code would be
    Code:
    SELECT Col1, Col2, IIf(([Col1]=0) OR ([Col2]=0),0,[Col1]/[Col2]) As Col3 WHERE IIf(([Col1]=0) OR ([Col2]=0),0,[Col1]/[Col2])>10
    HTH,
    Sam

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As Sam helpfully points out the iif example was geared to someone using the Access query designer, rather than someone rolling their own SQL. The query designer helpfully repeats the iif as part of the criteria, thus avoiding typos.

    IMHV It flattens ever so slightly the steepness of the learning curve in desiging queries. Its also quite a good tool for seeing how SQL could vbe written, especially if, as and when you start doing complex joins and selection criteria.

  6. #6
    Join Date
    Apr 2005
    Posts
    21
    Since Col1 and Col2 are also calculated columns ( ie Col1 --> Max(another col), I have trouble using the above query

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Then you need to be explicit and include the calculations as opposed to the field names:

    Select MAX(someCol) As Col1,Col2,(MAX(someCol)/Col2) AS Col3
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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