Using MSACESS for translations?

[Sam M via AccessMonster.com]

I will try and explain the scenario as best i can...

Ultimately, i will have 3 tables... one table with values and the other two
that map to new values for the old table...

TABLE1:
Old_Value1
Old_Value2
Old_Value3 (Can be null)

TABLE2:
Old_Value1
Old_Value2
New_Value1
New_Value2

TABLE3:
Old_Value3
New_Value3
New_Value4

Based on each record in TABLE1.... the select statement should return
TABLE2.New_Valu e1, TABLE2.New_Valu e2, TABLE3.New_Valu e3, TABLE2.New_Valu e4

Ultimately, I am reading each record in Table1 to return the translated
values from the other tables.

IMPORTANT: Sometimes TABLE1.Old_valu e3 can be null so the lookup is not
necessary but I need the first lookup to occur...

So i need some IF logic and way to fetch values within the original select..
Here is the kicker... I can't use SQL Server... only what msacess provides
and i don't know any VB....
p.s. I am an oracle guy... but they want this solution portable (MSACCESS) on
a laptop with no connectivity... otherwise i would have written a package
with store procedures..

Even if anyone could suggest a good HELP topic to lookup...

[Sam M via AccessMonster.com]

actually... if the value is NULL i can return "000"

[Sam M via AccessMonster.com]

Here's an example...

I ultimately want to return one record based on the translation rules
obtained from TABLE2/3 that go against the values coming in from TABLE1

Example Data

Table1 = (first 2 values translated and 2nd value has a 1:1 translate)
34,45,45
56,878, NULL

Table2 = (translation values)
34,45, 99
56,878, 33

Table3 = (translation values)
45, 66

Returned results/translations against table1

99, 66 (rec1)
33, 000 (rec2)
Thanks for the quick response... need to see an example of code to see how
the IF() works within SQL
--
Message posted via http://www.accessmonster.com

[Ronald Roberts]

1. Createa blank form
2. Add a command button.
3. Put the following in the Event procedure.
I used the DLookUP Aggregate function for looking up the values
for table three.

HTH
Ron
Private Sub Command0_Click( )
On Error GoTo Err_Command0_Cl ick
Dim db As Database
Dim rs As Recordset
Dim txtNewValue1 As String
Dim txtNewValue2 As String
Dim SQLString As String

Set db = CurrentDb
Set rs = db.OpenRecordse t("Table1")
rs.MoveFirst
If rs.EOF Then
Exit Sub
End If
txtNewValue1 = "000"
txtNewValue2 = "000"
Do Until rs.EOF
'This will return a value or "000" for value1
SQLString = "Old_Value1 = '" & rs!Old_Value1 & "' and Old_Value2 = '" &
rs!Old_Value2 & "'"
If IsNull(txtNewVa lue1 = DLookup("New_Va lue1", "Table2", SQLString)) Then
txtNewValue1 = "000"
Else
txtNewValue1 = DLookup("New_Va lue1", "Table2", SQLString)
End If

'This will return a value or "000" for value 2
If Not IsNull(rs!Old_V alue3) Then
If IsNull(txtNewVa lue2 = DLookup("New_Va lue2", "Table3", "Old_Value1
= '" & rs!Old_Value3 & "'")) Then
txtNewValue2 = "000"
Else
txtNewValue2 = DLookup("New_Va lue2", "Table3", "Old_Value1 = '" &
rs!Old_Value3 & "'")
End If
Else
txtNewValue2 = "000"
End If

db.Execute " INSERT INTO Table4 (fld1, fld2) VALUES (" & " '" &
txtNewValue1 & "'" & ", '" & txtNewValue2 & "' );"

txtNewValue1 = "000"
txtNewValue2 = "000"
rs.MoveNext
Loop

MsgBox "Done", 48
Exit_Command0_C lick:
Exit Sub

Err_Command0_Cl ick:
MsgBox Err.Description
Resume Exit_Command0_C lick

End Sub
Sam M via AccessMonster.c om wrote:

Here's an example...

I ultimately want to return one record based on the translation rules
obtained from TABLE2/3 that go against the values coming in from TABLE1

Example Data

Table1 = (first 2 values translated and 2nd value has a 1:1 translate)
34,45,45
56,878, NULL

Table2 = (translation values)
34,45, 99
56,878, 33

Table3 = (translation values)
45, 66

Returned results/translations against table1

99, 66 (rec1)
33, 000 (rec2)
Thanks for the quick response... need to see an example of code to see how
the IF() works within SQL