Linking, lookups or back coding or what?

[kallo]

I have a table with about 50 integer fields (coded answers to questions), and equal number of tables where those integers correspond to short bits of text. (Human readable version of those answers.) I now want to produce either a query or a table where all the integers have been replaced with the corresponding bits of text.

Linking the integer fields result in an extremely slow query after 18 or so linked tables, and if I try to link all the about fifty tables, Access complains that the query is too complicated.

What am I doing wrong? This seems such a common problem, I feel I'm missing something very fundamental. How do I do this?

[beacon]

I just want to make sure I'm understanding what you're doing...you have a table with one field and one record for EVERY record in your integer table?

If that's the case, you should really scrap all the extra tables and create just one 'Answer' table and put all the answers in it with an autonumber field to ID them (primary key). Make sure your integer table also has an autonumber field to ID each record.

Then, I would create a third table that is related to each of the first two using a foreign key (one to answers, the other to integers) and then create the link here. You will be able to see the ID for the integers here and can assign one or more answers to each of the items.

Does that make sense? It follows your basic database normalization techniques.

[kallo]

No, I have one big table where the answers are coded by numbers, say, 1 to 4, and lots of other, small tables, mapping the coding, say, 1=" I agree strongly", 5="I disagree strongly", as well as handling missing answers and miscoding.

Currently I have solved the issue by having made several update queries, each handling ten fields each, and then running them all, but this seems like a very "clunky" and "naff" solution.

The performance drop seems to occur after the query includes over 15 tables, with a 18 field query taking a LOT of time, whilst you hardly notice the delay with a ten table update query.

I'm quite sure there is a more "professional" way to handle this task, or Access wouldn't get used in serious applications.


P.S. the field value matching in queries seems to not work with empty values (ie. a missing value in the coded answers table doesn't match to a missing value in my codes to text mapping table.) This is no biggie, but I'm wondering about that too.

[beacon]

Check out the attached database and let me know if I've gotten close to what you're attempting to accomplish.

There are 3 tables, one for question numbers (that you called integers), one for question answers (the short bits of text), and a table (that I called tblQuiz) that combines them into one. The last table is setup with combo boxes so you don't have to physically touch the other two tables to enter data.

There is also a query that is basically a print out of tblQuiz. You can filter this however you want though, or could create a report with this query as the record source if your ultimate goal is to create Quizzes for students.

Let me know...