0

I've 2 Tables, as shown in the attached example I want to retrieve the data from table 2 based on two records, "size" as 1st field value & "category" as a field name. In Excel, it is easy by using Vlookup & match functions, but I want it In Access. As an example: From Table 1 at 3rd record , "size"=3 & "category"=D, then "item 3" value shall be retrieved from Table 2 with two conditions: "Size" = 3 & field name ="D" i. e. "30". Thank you.

Example ; Actual Data

Ashraf Fouad
  • 119
  • 8
  • *Welcome to [so]!* I'm not entirely clear on what you're asking, but **the Access table equivalent of VLOOKUP is [`DLOOKUP`](https://support.office.com/article/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937)**. Also, check out the [tour] (you'll earn your first badge!). There's other good tips in "[ask]" and how to create a [mcve], as well as this [checklist](https://codeblog.jonskeet.uk/2012/11/24/stack-overflow-question-checklist/) from the sites' top user. You can always [edit] your questions to add more information if necessary. – ashleedawg Sep 03 '18 at 11:01
  • Thank you, I already added an example. – Ashraf Fouad Sep 03 '18 at 12:10
  • Your data in table 2 isn't normalised, meaning that any working query would have to be "dynamic" SQL which is not easy to manage or a preferred method. If you normalised your data this would be simple . Have a read of this to better understand - https://stackoverflow.com/questions/246701/what-is-normalisation-or-normalization – Minty Sep 03 '18 at 12:14
  • Actually, this is an example but the original data for table 2 is only numbers. I already changed the example. Thank you – Ashraf Fouad Sep 03 '18 at 13:03
  • If that really is your data, then you can calculate the value based on the letter, as it's a fixed off-set ? E.g `NumberVal: Asc([Category])-54+((IIf([Size]>1,([Size]-1)*8,0)))` Except your data seems to miss the value of 35 out ? – Minty Sep 03 '18 at 15:20
  • Unfortunately, the actual data is completely different - see the attached actual data. Thank you – Ashraf Fouad Sep 04 '18 at 04:38

1 Answers1

0

The following function solved my problem to find a value on a table based on a value in a row & column name I think it may require some tuning on the code to be faster.

Function MatchColumn(row As Single, column As String) As Single
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim Pos As Integer ' coulmn position
Set db = CurrentDb
Set tdf = db.TableDefs("Table 2")
Set rs = tdf.OpenRecordset

For Each fld In tdf.Fields
 If fld.Name <> column Then Else Exit For
Next
Pos = fld.OrdinalPosition

If Not (rs.EOF And rs.BOF) Then
 Do While Not rs.Fields(0).Value = row
 rs.MoveNext
Loop
MatchColumn = rs.Fields(Pos).Value

rs.Close
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
End If
End Function
Ashraf Fouad
  • 119
  • 8