Query Question

[John Torres]

I am trying to create a query for my sampling size table and I can’t get the right expression i.e. Lot Size 1 to 50 = 5 and so on… I tried the AQL: IIf([Test].[Lot Size]<=50,"5") and I can’t pass the beyond this expression.

Lot Size ----- Sample Size
1-50 ---------------- 5
51-90 --------------- 7
91-150 ------------- 11
151-280 ----------- 13
281-500 ---------- 16
501-1200 --------- 19
1201-3200 --------- 23
3201-10000 ---------- 29
10001-35000 ------- 35
35001- Over -----40

Thanks

[ADezii]

Assuming you have a Table named tblLotSize consisting of a single Field named [Lot Size] {LONG}, then the following Query and associated Function shouold return the desired results for you as indicated by the Sample Data and Output:

1. Query SQL:
   

   SELECT tblLotSize.[Lot Size], fReturnSampleSize([Lot Size]) AS [Sample Size]
   FROM tblLotSize;

2. Function utilized in the Query:
   

   Public Function fReturnSampleSize(lngLotSize As Long) As Variant
   Select Case lngLotSize
     Case Is >= 35001
       fReturnSampleSize = 40
     Case Is >= 10001
       fReturnSampleSize = 35
     Case Is >= 3201
       fReturnSampleSize = 29
     Case Is >= 1201
       fReturnSampleSize = 23
     Case Is >= 501
       fReturnSampleSize = 19
     Case Is >= 281
       fReturnSampleSize = 16
     Case Is >= 151
       fReturnSampleSize = 13
     Case Is >= 91
       fReturnSampleSize = 11
     Case Is >= 51
       fReturnSampleSize = 7
     Case Is >= 1
       fReturnSampleSize = 5
     Case Else
       fReturnSampleSize = Null
   End Select
   End Function

3. Sample Data in tblLotSize:
   

   Lot Size
   1
   50
   51
   90
   91
   150
   151
   280
   281
   500
   501
   1200
   1201
   3200
   3201
   10000
   10001
   35000
   35001
   692
   89765
   -13

4. OUTPUT of Query:
   

   Lot Size    Sample Size
   1                 5
   50                 5
   51                 7
   90                 7
   91                11
   150                11
   151                13
   280                13
   281                16
   500                16
   501                19
   1200            19
   1201            23
   3200            23
   3201            29
   10000            29
   10001            35
   35000            35
   35001            40
   692                19
   89765            40
   -13    

[John Torres]

Where do i put the Query SQL and the Function... Sorry I'm a newbie and still learning...
Thanks again.
J

Assuming you have a Table named tblLotSize consisting of a single Field named [Lot Size] {LONG}, then the following Query and associated Function shouold return the desired results for you as indicated by the Sample Data and Output:

1. Query SQL:
   

   SELECT tblLotSize.[Lot Size], fReturnSampleSize([Lot Size]) AS [Sample Size]
   FROM tblLotSize;

2. Function utilized in the Query:
   

   Public Function fReturnSampleSize(lngLotSize As Long) As Variant
   Select Case lngLotSize
     Case Is >= 35001
       fReturnSampleSize = 40
     Case Is >= 10001
       fReturnSampleSize = 35
     Case Is >= 3201
       fReturnSampleSize = 29
     Case Is >= 1201
       fReturnSampleSize = 23
     Case Is >= 501
       fReturnSampleSize = 19
     Case Is >= 281
       fReturnSampleSize = 16
     Case Is >= 151
       fReturnSampleSize = 13
     Case Is >= 91
       fReturnSampleSize = 11
     Case Is >= 51
       fReturnSampleSize = 7
     Case Is >= 1
       fReturnSampleSize = 5
     Case Else
       fReturnSampleSize = Null
   End Select
   End Function

3. Sample Data in tblLotSize:
   

   Lot Size
   1
   50
   51
   90
   91
   150
   151
   280
   281
   500
   501
   1200
   1201
   3200
   3201
   10000
   10001
   35000
   35001
   692
   89765
   -13

4. OUTPUT of Query:
   

   Lot Size    Sample Size
   1                 5
   50                 5
   51                 7
   90                 7
   91                11
   150                11
   151                13
   280                13
   281                16
   500                16
   501                19
   1200            19
   1201            23
   3200            23
   3201            29
   10000            29
   10001            35
   35000            35
   35001            40
   692                19
   89765            40
   -13    

[ADezii]

Where do i put the Query SQL and the Function... Sorry I'm a newbie and still learning...
Thanks again.
J

Download the Attachment which consists of the Test Database and only three Objects: a Table, Query, and Module. You should be able to get a very clear picture of what is going on now.

[John Torres]

Thank you very much...Now I got it...

Download the Attachment which consists of the Test Database and only three Objects: a Table, Query, and Module. You should be able to get a very clear picture of what is going on now.

[ADezii]

You are quite welcome, sometimes a picture is worth a thousand words (LOL).