Lookup help needed

[voyagergregory]

I have a table of information eg. First Name, Last Name. Included in the table I also have a number of question fields, which the answers are filled in differently in every record. However...for every different question (there are MANY questions) there are only 5 possible answers-these 5 answers changing for each question.

I have set-up a lookup field to this master table. For each different question, there is a new table showing the 5 possible answers to that question. In these individual 'answer' tables there are the following fields: 1) Rating Number 2) Rating and 3) Report Value. Basically...I have a form connected the master table. The person answering would use the form and have a drop-down box from which to choose 5 different answers. I have done this by using a lookup field to the individual answer tables. I have also set it-up so the Report Value data is entered into the master table - how I want to be.
However...the problem comes when the user selects a possible answer from the drop-down box. The lookup field/drop-down box shows the Rating Number, Rating and Report Value. I ONLY want it to show the Rating Number, so the other two fields are hidden. I still need the Report Value data to be entered into the master table even though it isn't shown in the drop-down box.
If this doesnt make sense, please let me know and I can try and explain.
Please help,
thanks
Nathan

 

[WildHare]

Alter your combo box column widths in the properties sheet for it, look for the COLUMNWIDTHS property of the CB

You'd probably want something like

2;0;0

to set the first column 2 inches wide, and the next two columns zero-width.

You can still refer to them, and the BOUND column (Column 3) of the combo box need not be visible, either. This is how one usually does a combo box where you want to display "text stuff", but save a NUMBER code or something.

Jim

(oh, one other thing - if you refer to the combo box in code, the columns are ZERO based, but in the property sheet, they are ONE based...)

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[binky]

Where did you set up the drop down boxes? If it was in the table design then you can edit what displays from there. Otherwise lookin the datasource property of the combo box on your form. If you need anymore help with what to do from here let me know. I'd be pleased to help anyway I can.

Binky :
><>

 

[voyagergregory]

I designed this form and the drop-down boxes in the table design yes. I get how to hide the text now, but still be able to have it bound. Thanks.

By the way...is there a way instead, for me to not select the 5 different answers/options from the drop-down box, but have 5 option boxes, from 1-5. Whenever they clicked on either 1, 2, 3, 4 or 5 - a different, corresponding bit of text is actually entered into my master table.
However, this will be sufficient, so thanks a lot
Nath

 

[voyagergregory]

Sadly, I tried to change the column widths and I was unsuccessful when I went to the table and tested this.

I'll try to explain again.
I have a table, with a question. My drop-down box shows the 5 possible answers. These 5 answers are &quot;looked up&quot; from another table, (named Question 1 etc.) This lookup table shows:

Field 1 = Rating Number (eg, 1)
Field 2 = Rating (eg, Agree)
Field 3 = Report Value (the actual answer, which can be long)

When the user clicks the drop down box it shows these three fields, I only want it to show the Rating Number field. I do this through a form you see.....so when the user uses the form they only see and select a Rating Number, not the Report Value. This Report Value needs to be entered in the master table.

I tried changing the column widths just like you said, but the table is entered with the Rating Number, no REport Value.
Please help.
Wouldit be easier to use Option Boxes, which are somehow linked to the lookup table for that question? Eg. 5 different options boxes, from one to five.

Please help,
Nathan

 

[voyagergregory]

Please help. im desparate!

 

[WildHare]

So you want to SHOW the &quot;1&quot;, but STORE the &quot;AGREE&quot; ?

Isn't this backwards?...LOL

Make the column widths

.5;0;0

Make Column 2 the bound column - it looks like you have Column 1 the bound column.

It should work...

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[voyagergregory]

Right.
I can explain it all like this

My Master Table has the answer fields to all the questions eg, Question 1, Question 2 etc....

I set these up with the maximum field size as 255.

I also set up the Question 1 table which contains the answers. That table has the following fields:
1) Rating Number (from 1 to 5, is what I want user to see) (It's also the Primary Key-does this matter?)
2) Rating (doesnt really matter, but is there)
3) Report Value (This is a long piece of text, which is why I set the large field size).

I then went to the master table and used the lookup wizard.
I told it to look up the values from the Question 1 table. I then said I wanted the columns (in this order) as:
Rating Number
Report Value
(I also said I didnt want to hide the rating number field beacuse it was a primary key).

I then said that the column that the information should be used from was the Repor Value field.
The wizard then completed.

I saved.

Then I went into the Master Table and used the drop-down box for question 1. It showed the 5 options and the corresponsing text/sentence next to it. I selected one, but the field was only filled by the number, even though it says (even in the properties of that field in design view) that column 2 is bound.

I changed the column widths as you said an the field is still only filled by a number. This shouldn't happen!

If this helps...The limit to list says yes. I an't change taht because it goes on a bout column widths and binding.

Sorry, if this seems strange
Nath

 

[voyagergregory]

Can anybody help or suggest something?
thanks

 

[WildHare]

AHA! said the blind man..&quot;it's an elephant&quot;...

I think you've run in to a rather odd behavior of the LUWizard, and in fact, most combo boxes, especially when they're bound like this.

If I read it right, you are actually storing the second column. But you SEE the first column in your field cell because 1) it is the only visible column in the combo behind it, and B) lol the BOUND column is invisible.

I'm not sure why our lookup-source ANSWER table is called &quot;Questions&quot;, but I'll leave that for a later date.

Is this something you can zip up and email over to me? I might be able to figger a better solution if I can see it in front of me....

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[voyagergregory]

I will zip it and email it to you.
Perhaps when you see it you will understand why it is called questions. I can explain like this

I am developing questionnaire software.

The person answering the questions will answer on a piece of paper.
A data inputter will use the form to enter the answers to the questions.
The answers are on a 5 point scaled ie. 1 2 3 4 5
There are lots and lots of questions. Depending on the question and the answer chosen, there is a very different bit of text which needs to be recorded in the Master Table.

I need all these different bits of text, because when my report is printed it will show these answers in a certain way as you will see!

BTW- you'll also see that the question isnt called Question 1 it is actually a question.

The four questions youll see as fields in the master table (theres only 4 for now to test it), each link up (lookup) with the 4 tables, named Teamworking 1, Teamworking 2,Teamworking 3,Teamworking 4.
So, I've been trying to get the first question to properly work with the teamwork 1 lookup.

The databse many be different to what I've described earlier.

I would actually like the form to only display the 1,2,3,4, and 5. WHen this is selected the &quot;lookedup&quot; text from the Report Value should be put in the Master Table so it can be incldued in the report.
While lookign at my databse, if you can see any easier way of doing it all, please help.
my email is voyager_gregory@hotmail.com

ill email it to you!

 

[voyagergregory]

I will compact it and email it to you.
Perhaps when you see it you will understand why it is called questions. I can explain like this

I am developing questionnaire software.

The person answering the questions will answer on a piece of paper.
A data inputter will use the form to enter the answers to the questions.
The answers are on a 5 point scaled ie. 1 2 3 4 5
There are lots and lots of questions. Depending on the question and the answer chosen, there is a very different bit of text which needs to be recorded in the Master Table.

I need all these different bits of text, because when my report is printed it will show these answers in a certain way as you will see!

BTW- you'll also see that the question isnt called Question 1 it is actually a question.

The four questions youll see as fields in the master table (theres only 4 for now to test it), each link up (lookup) with the 4 tables, named Teamworking 1, Teamworking 2,Teamworking 3,Teamworking 4.
So, I've been trying to get the first question to properly work with the teamwork 1 lookup.

The databse many be different to what I've described earlier.

I would actually like the form to only display the 1,2,3,4, and 5. WHen this is selected the &quot;lookedup&quot; text from the Report Value should be put in the Master Table so it can be incldued in the report.
While lookign at my databse, if you can see any easier way of doing it all, please help.
my email is voyager_gregory@hotmail.com

ill email it to you!

 

[voyagergregory]

Umm. Jim, wots ure email?
thanks
im voyager_gregory@hotmail.com

 

[voyagergregory]

I emailed ya at webmaster@jmhare.com

 

[WildHare]

6:30PM EDT
I'm looking at it now. It might be better if we take this offline - I'll email you with some thoughts in a little while.

I did a lot of survery/questionnaire applications when I worked for the local utility. I think I can get something working for you....

Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm