Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IIF query (Access 2000)

    I want to build an IIF query with 3 additional coloumns. My fields are liters and size. The first column should enumerate the liters where the size is < 6. The second coloumn should enumerathe the liters where the size is between 20 and 60 and in the thrd coloumn where size is > 180. Ii think the best way is an iif query but it is difficult for me to build it. So if for example the product has a size of 1, the liters should be in the irst coloumn.These liters will be further calculated so the query should be updatable.will you help me ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF query (Access 2000)

    What happens if the size is 13, or 100?

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF query (Access 2000)

    the size of the packages we have are :
    small : 0,4,0,5,1, 4,5
    garage : 20 60
    large : 180 ,205
    the liters derived with the packages smaller than 6 are called small, the second garage and the third large




    we have no other sizes

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF query (Access 2000)

    So you meant "greater than or equal to180" instead of "greater than 180". You should always be accurate!

    The following query will return all fields from the table, plus three calculated columns L1, L2 and L3. The original fields will be updatable, the additional columns L1 to L3 will *not* be updatable since they are calculated.
    <code>
    SELECT *, IIf([Size]<6,[Liters],Null) AS L1, IIf([Size] Between 20 And 60,[Liters],Null) AS L2, IIf([Size]>100,[Liters],Null) AS L3
    FROM [NameOfTable]
    </code>
    Replace NameOfTable with the name of your table.

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF query (Access 2000)

    Thank you very much for your reply.it is beyond me to do that since liters is in one table called order details and size is anohr table called products:
    SELECT [order details].liters, products.size
    FROM [order details] INNER JOIN products ON [order details].ProductID = products.Productid;
    Which name of table should i enter ?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF query (Access 2000)

    Try

    SELECT [order details].liters, products.size, IIf([Size]<6,[Liters],Null) AS L1, IIf([Size] Between 20 And 60,[Liters],Null) AS L2, IIf([Size]>100,[Liters],Null) AS L3
    FROM [order details] INNER JOIN products ON [order details].ProductID = products.Productid;

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IIF query (Access 2000)

    it is simply great !

Posting Permissions

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