Query Help on "Lookup" Type Function

[craisbeck@boxlogixautomat]

I have 2 tables.

Table1
Reg#,CED%, WW%, YW%
100,5%,5%,15%
101,7%,4%,10%
102,10%,6%,10%

Table 2
EPD,%,Val
CED,5%,12
CED,6%,14
CED,7%,16
CED,10%,25
WW,4%,2
WW,5%,4
WW,6%,6
WW,10%,5
YW,10%,1
YW,15%,2

I am looking to get a query that would create the following result set comparable to a lookup function in excel.

100,12,4,15
101,16,2,1
102,25,6,1

Any help would be greatly apprecaited.

 

[MajP]

One easy way. Make three queries from table 2 returning the percent and value.
qryCED 'filtered to EPD = "CED"
qryWW ...
qryYW

Now join Table 1 to qryCED CED % to %
qryWW by % to WW%
qryYW by % to YW%

Each time you would pull in Val and you could give that fields an alias
Reg#, CED_Val, WW_VAL, YW_VAL

I would not use #,% in field names. You will likely run into problems
% to "Percent" Reg# to Reg_No

 

[theDBguy]

Hi. Welcome to AWF!

This might be easier if Table1 was properly Normalized. Just a thought...

 

[craisbeck@boxlogixautomat]

Hi. Welcome to AWF!

This might be easier if Table1 was properly Normalized. Just a thought...

I could union all it and normailize it. I didnt control that table structure so I am workign from behind a bit to start with.

 

[MajP]

I could union all it and normailize it.

You could, but it would be about the same amount of work as I showed.

Union would give results like

Reg_No  EPD  EPDVal
100     CED  .5
100     WW   .5
100     Yw   .15

Then you would have to make a query pulling in three instances of your union query. Link each by EPD to EPD and return EPDVal each time. Then alias the three EPDVal

 

[craisbeck@boxlogixautomat]

True,
Table 1 has about 40 fields.

 

[MajP]

That sounds like a PITA to do manually.
I would do it like this in code, if you can use a temp table.

I still have my table

RegNO
EPD_TYPE
EPD_Value

Loop table 1
  get RegNO
    Loop the Fields after RegNO
       insert RegNo, Insert FieldName into EPD_Type
       get percent value from current field
      lookup EPD_VALUE in table two based on Field Name and Percent
      insert EPD_VALUE
      update

If you provide the tables I can do this quick

Then do a Crosstab
Row : RegNO
Column: EPD_Tupe
Value: EPD_VALUE

 

[Pat Hartman]

When you import external data that is not normalized, it is best if you bite the bullet and fix it on the way in. That way you don't need to fix it every time you need to use it. Then you end up with a simple crosstab query instead of code and constantly making and deleting temp tables.

 

[MajP]

This will do what you want with as many columns as you have. This assumes all the names, datatypes, and structure match what I have.

Public Sub UpdateTable()
  Dim strSql As String
  Dim fld As DAO.Field
  Dim rs1 As DAO.Recordset
  Dim rs2 As DAO.Recordset
  Dim rsNew As DAO.Recordset
  Dim regNo As Integer
  Dim fldName As String
  Dim EPDval As Integer
  Dim Perc As Double
  Dim i As Integer
 
  Set rs1 = CurrentDb.OpenRecordset("table1")
  Set rs2 = CurrentDb.OpenRecordset("table2", dbOpenDynaset)
  Set rsNew = CurrentDb.OpenRecordset("tblNew")
  If Not rs1.EOF Then rs1.MoveFirst
  Do While Not rs1.EOF
     regNo = rs1![reg#]
     For i = 2 To rs1.Fields.Count - 1
       Set fld = rs1.Fields(i)
         fldName = fld.Name
         Debug.Print fldName
         Perc = rs1.Fields("[" & fldName & "]")
       
         fldName = Left(fldName, Len(fldName) - 1)
         rsNew.AddNew
           rsNew!Reg_Number = regNo
           rsNew!EPD_Type = fldName
           'remove %
             rs2.FindFirst ("EPD = '" & fldName & "' AND [%] = " & Perc)
           If Not rs2.NoMatch Then rsNew!EPD_Val = rs2!val
         rsNew.Update
      Next i
    rs1.MoveNext
  Loop
 
End Sub

Run the code in module 1 then open the query. Look at tblNew before and after running

 

[MajP]

However I think the Vlookup will provide the closes match. I set it up to return exact matches (more database-ish). If you want closest match that requires some code, but can be done if that is needed.
Example, if you put CED 5.7% then a real vlookup should return 14 which is closer to 6%. I do not think there is any easy way in sql to do that even if normalized. You can find a value in a range, but not closest to a value.