Access Data (VLOOKUP function?)

[rmoreno]

Hi all. I’m having difficulty trying to figure out how to do this.

I have a query [qry_GRADENUM] with a field name [GRADE] that has a number from 0 to 9.


I also have a table [tbl_GRADELIST] that has 10 columns with data.

What I need is to somehow link the qry_GRADENUM.[GRADE] number to the correct column in the [tbl_GRADELIST] table.
It would be great if Access had an OFFSET function but it doesn't. Does anyone have a suggestion on how I can do this?

[pbaldy]

In a relational database, the GradeList table would have 3 fields and look like:

GradeLst   Grade   Description
Test           0         No Action
Test           1         Contact Me

From there it's a simple lookup with GradeLst and Grade as criteria.

[rmoreno]

I agree, however, the GradeLst table format is fixed and cannot be changed. I have to work with what is already there.

[pbaldy]

Then I would probably create a UNION query that "normalized" the data, and use that in queries or for lookups.

[rmoreno]

OK, how do I do that. Can you show me an example please?

[pbaldy]

Along the lines of:

SELECT GradeLst As GradeType, 0 As Grade, Grade0 As Description
FROM GradeList
UNION ALL
SELECT GradeLst As GradeType, 1 As Grade, Grade1 As Description
FROM GradeList
...

[rmoreno]

I tried using the code you suggested and I am getting an error message: "The syntax of the subquery in this expression is incorrect"
What am I doing wrong?

[pbaldy]

From the sound of it, you added it in the design grid somewhere. Get a new query into SQL view and type it there. The design grid does not support UNION queries.

[rmoreno]

Did that. Now I get the error: Syntax error in query. Incomplete query clause.

I guess I don't really understand what and how a UNION query works. Could you please explain?

[pbaldy]

You didn't include the ellipsis did you? Does this work?

SELECT GradeLst As GradeType, 0 As Grade, Grade0 As Description
FROM GradeList
UNION ALL
SELECT GradeLst As GradeType, 1 As Grade, Grade1 As Description
FROM GradeList

[rmoreno]

Here is my code:
SELECT GradeLst As GradeType, 0 As Grade, Grade0 As Description FROM GradeList
UNION ALL SELECT GradeLst As GradeType, 1 As Grade, Grade1 As Description FROM GradeList
SELECT GradeLst As GradeType, 2 As Grade, Grade2 As Description FROM GradeList
SELECT GradeLst As GradeType, 3 As Grade, Grade3 As Description FROM GradeList
SELECT GradeLst As GradeType, 4 As Grade, Grade4 As Description FROM GradeList
SELECT GradeLst As GradeType, 5 As Grade, Grade5 As Description FROM GradeList
SELECT GradeLst As GradeType, 6 As Grade, Grade6 As Description FROM GradeList
SELECT GradeLst As GradeType, 7 As Grade, Grade7 As Description FROM GradeList
SELECT GradeLst As GradeType, 8 As Grade, Grade8 As Description FROM GradeList
SELECT GradeLst As GradeType, 9 As Grade, Grade9 As Description FROM GradeList;

It is now giving me a differenct error message: Syntax error in FROM clause.

[pbaldy]

Each SELECT/FROM clause must be separated by UNION ALL, just as I did the first two.

[rmoreno]

OK. That did it. I can't thank you enough for your expertise and patience with me on this problem.

I consider this issue SOLVED.

[pbaldy]

Happy to help!