Value translations with msaccess..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Value translations with msaccess..

Author

Topic

spiceman
Starting Member

2 Posts

Posted - 2005-07-18 : 08:27:49

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

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-07-18 : 08:47:18

You need a combination of left outer joins and the IIF() function.

Join from your base table to the outer tables using LEFT OUTER JOINS, and then use the IIF() function to conditionally return values from the appropriate table. MS Access has full on-line help available.

If you need more help, you need to be much more specific and some sample data, expected results, and more precise logic would help greatly.

- Jeff

spiceman
Starting Member

2 Posts

Posted - 2005-07-18 : 09:59:24

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 = (3rd value is the translated value)
34,45, 99
56,878, 33

Table3 = (2nd value is the translated value)
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources