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).