Can Access calculate similar to Excel's offset/match functions?

[nikilee]

I am trying to take an Excel worksheet and move it into Access because the number of workbooks grow by at least five every day.

Here's my problem: I have anywhere from 5 to 15 (depending on the product) fields of numerical data. They are calculations from other parts of the query/table and I need to be able to choose the two fields (separately) between which '90' would fall. Clear? Look at this...

4 6.0
8 29.0
16 65.8
30 85.0
50 92.8
100 97.2

I need one field to return the 92.8 value, another with the correlating 50, another with 85.0 and yet another with it's correlating 30. Can I do that in Access? Here is what the Excel formulas look like.

H22 =OFFSET($F21,MATCH(90,$F21:$F33,1),0)
H23 =OFFSET($F21,MATCH(90,$F21:$F33,1),-4)
H24 =OFFSET($F21,MATCH(90,$F21:$F33,1)-1,0)
H25 =OFFSET($F21,MATCH(90,$F21:$F33,1)-1,-4)
H26 =(90-H24)/(H22-H24)*(H23-H25)+H25

Anybody have any suggestions? Every function that I've tried wants to match exactly to 90 or isn't capable of looking at so many fields of data. Any ideas are welcome!

Thank you ~ NikiLee

 

[Remou]

In Access, this type of data would occur in rows allowing you to use queries to return the information you want:

ProductID Data
1 1
1 23
1 97
51 99
52 5
51 100

Query:

SELECT tblT.ProductID, Min(tblT.Data) AS MinData
FROM tblT
WHERE tblT.Data>=90 And tblT.Data<100
GROUP BY tblT.ProductID

 

[nikilee]

I thank you for your reply. The 90><100 stuff hasn't been returning the numbers I need, because I need it to choose the set of numbers where 90 would fall in between. Otherwise, both 92.8 and 97.2 would be returned and what I need is the 85.0 and 92.8 to be returned as well as the correlated 0.30 and 0.60 respectively in separate fields so that data can be used in yet another calculation.

MM %Retained
4.75 6.0
2.38 29.0
1.19 65.8
0.60 85.0
0.30 92.8
0.15 97.2

Also keep in mind that this data can vary greatly where one day the 90% could fall between the 1.19 and 0.60 fields and another day it falls between 0.30 and 0.15 fields. Also some products have only six fields in which to choose and others have 15 fields.

What I've done so far is create a different field for each of the numbers you see above as all of this is calculated for one product ID on one particular date. All of it comes together to calculate two important numbers by which the product is judged to be good or bad.

Thanks again ~ NikiLee

 

[Remou]

This demonstrates the use of two subqueries to return the two values you require from the sample data. The number of rows (5 or fifteen) should not be relevant. The greater/less than is used in conjunction with Min and Max to return one value. Distinct is used in this case as there is no product ID to group by, so it is an unfinished query, however, it should be suitable for viewing the sample data set out in rows in a table called tblTx.

SELECT DISTINCT (

   SELECT MM 
   FROM tblTx 
   WHERE Retained=(
      SELECT  Max(tblTx.Retained) 
      FROM tblTx 
      WHERE tblTx.Retained<90)) AS MM1, 
   (SELECT MM 
   FROM tblTx 
   WHERE Retained=(
      SELECT  Min(tblTx.Retained) 
      FROM tblTx 
      WHERE tblTx.Retained>=90)) AS MM2

FROM tblTx;

It is also possible to use a User Defined Function (UDF).