I am working with three tables. I am trying to see who answered a certain question (question_id=2493) and what their response to it was. The three tables we have and their structure are as follows:
[Assessment_Answers_Descriptions]
item_code - number (integer duplicates ok..also a primary key)
item_choice_code - number (long integer duplicates ok also a primary key)
item_choice_text - text (size 80)
[Assessments]
session_uuid - number (replication id, primary key)
consumer_uuid - number (replication id)
consumer_full_name - text
assessform_name - text
create user - text
[Responses]
response_uuid - number (replication id)
session_uuid - number (replication id, this auto joins to assessments.session_uuid)
question_id - number
question_length - number
response_data - memo
The assessments table has the main consumer information I need (consumer name and assessform name). As mentioned when you create a query it is joined to responses. The Responses table contains the possible answers to the questions and the Assements_answers_description table contains all of the possible answers and what it actually means. The first thought is why even use the answers_description table but the problem with that is the responses table only returns a number and not what that number translates to in the form of an answer. With the thousands of questions and answers possible it is not as easy as saying if we see the answer with the number of 2 we know what it means. 2 may mean something different for each question_id which is why I have to link it to the descriptions table which shows the possible responses to a question_id and what the answer number means.
I hope this helps.
Dale
From: "wrmosca@comcast.net [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, May 21, 2014 9:57 AM
Subject: [MS_AccessPros] Re: Cannot joing on Memo, OLE, or Hyperlink Object
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, May 21, 2014 9:57 AM
Subject: [MS_AccessPros] Re: Cannot joing on Memo, OLE, or Hyperlink Object
Dale
You cannot join on a memo field. Joins are done on the primary key of one table and the foreign key of the child table. The data types have to be the same or at least compatible (like integer & long). Can you post the table structures (the field names and data types)? Be sure to note the primary keys.
Also tell us what you are trying to do with the data.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
http://mvp.microsoft.com/en-us/mvp/Bill%20Mosca-35852
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
---In MS_Access_Professionals@yahoogroups.com, <tenn_tazz26@yahoo.com> wrote :
I am getting the above error when trying to create a query from flat lined tables that are created by our company. I have two tables I am linked to. In table1 I am trying to join question_id to item_code in table2. That works without any problems. When I do the join I make it so it uses a right outer join?? (The one with the arrow pointing to table2). In table1 I also am trying to link Response_data to item_choice_code using the same type of join. This is when the error happens. When I check the tables I think it is because Response_data is a memo type and item_choice_code is a number. I don't have write access to the table structure to be able to change it. Even though in Response_data it may have a number 2 and in table2.item_choice_code it also has a number 2 and another field called item_choice_text which represents what the number 2 means, Access does not like the fields to be different. How can I get around this?
Thanks,
Dale
__._,_.___
Posted by: Dale Condon <tenn_tazz26@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar