By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Using MSACESS for translations?

P: n/a

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_Value1, TABLE2.New_Value2, TABLE3.New_Value3, TABLE2.New_Value4

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

IMPORTANT: Sometimes TABLE1.Old_value3 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...
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

actually... if the value is NULL i can return "000"
Nov 13 '05 #2

P: n/a

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
Nov 13 '05 #3

P: n/a

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_Click
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.OpenRecordset("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(txtNewValue1 = DLookup("New_Value1", "Table2", SQLString)) Then
txtNewValue1 = "000"
Else
txtNewValue1 = DLookup("New_Value1", "Table2", SQLString)
End If

'This will return a value or "000" for value 2
If Not IsNull(rs!Old_Value3) Then
If IsNull(txtNewValue2 = DLookup("New_Value2", "Table3", "Old_Value1
= '" & rs!Old_Value3 & "'")) Then
txtNewValue2 = "000"
Else
txtNewValue2 = DLookup("New_Value2", "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_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
Sam M via AccessMonster.com 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


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.