Fields Names Too Long

[YuriGagarin]

The project: I am trying to create a databases that captures feedback from surveys. Most of the feedback is numerical while other is txt (I am aware of the 255 txt limit and the problems that memo fields have). There are about 50 surveys, some repeat the same questions, some do not.
The problem: As you might have guessed, some of the questions asked in this survey are longer than the 64 allowed characters for Field Names. So, my solution is to have two tables, Question_Table has all of the Questions asked (with their associated Question_ID) and another table (ex: TableA) where the values ("feedback") is stored. The field names in TableA correspond to the Question_ID, however I do not know how to link the field names of TableA to the row values of Question_ID in the Question_Table.
My solutions: So far, the only proposed solution that I can think of is writing some VBA code using arrays that would link the two together. I know that I couldn't be the only one who has had this issue of how to work around long field names but my searches have not resulted in anything. What solutions do you propose? Would it be a query, code? Using arrays or another method?

Thanks in advance!

[ranman256]

your field name does NOT need >64 characters. just the questions, so the field names only need to be Q1, Q2, etc.

[YuriGagarin]

That is correct and I have it set as such, but I still need to associate that Q1 = How was your class? and Q2 = What did you think of the class? etc.....

[orange]

Consider:
tblQuestion
QID........1
QText......How was your class? <----this can be up to 255 chars.

[YuriGagarin]

Sorry I seem to be not communicating this effectively. I have two tables:
1)tblQuestion has QID and QText as fields
2)TableA has the field names of Q1, Q2, Q3, Q4.....(ie QID from tblQuestion)

I have to link these together for the purposes of Forms and Reporting, Access needs to know that the TableA has field names that correspond to specific QText.
(Keep in mind that there are about 50 "TableA" and not every table has the same questions)

[orange]

No. QID is Q1
QText is the question

You would use a query
eg to get the first 10 questions

If you have 50 Surveys that contain Questions and you have say 250 questions.
You tables would look something like

Survey--->SurveyContainsQuestions<------Questions

so you have
surveys 1--50
questions 1-250

but SurveyContainsQuestions is a junction table
could have
1 1
1 23
1 24
1 29
1 89
1 96
....

where the 1 reprsents the Survey and the 23, 24... represents the questions you used in survey 1.

[ssanfu]

You might look at "At Your Survey" by Duane Hookom at
http://www.rogersaccesslibrary.com/f...ey_topic3.html

It is an Access 2000 survey application - maybe it will help you with the table structure and relationships.

[ItsMe]

You might look at "At Your Survey" by Duane Hookom ...

That sounds like a useful template.

[YuriGagarin]

Okay I am going to try to keep this short.

ssanfu: Thank you for showing me that database, it had some key set ups that structured things the way I needed them to and hopefully will solve any future problems that I may encounter.

orange: Thank you for assisting me in figuring this out. While I had a hard time understanding your last suggestion, I think we are alluding to the same thing.

The problem was I had structured the database this way:

tblQuestions

Question_ID	Qtext
Q1	How are…
Q2	What are…
Q3	If you…

Feedback_TableA

Feedback_ID	Q1	Q2	Q3
1	Agree	Neutral	Agree
2	Diagree	Neutral	Agree
3	Diagree	Disagree	Disagree
4	Neutral	Agree	Disagree

And needed to structure the Feedback_TableA this way instead:

Question_ID	Feedback 1	Feedback2	Feedback 3
Q1	Agree	Neutral	Agree
Q2	Diagree	Neutral	Agree
Q3	Diagree	Disagree	Disagree

Then a relationship could be developed between the two tables. I am also finding out that this solves other problems with the database design. So I have to do more work on it and want to make sure it works before I label this thread solved. Let me know if you have other suggestions towards this design.
Thanks again

[orange]

You might use

tblSurveyResponses
SurveyId
QuestionID
ResponseID

and possibly
ResponderID/ResponderName etc