Creating new table with only certain for an existing table

[Wagon]

Good Evening,



I was wondering if it was possible to create a new table using data from an existing one but only selecting the columns that were populated with certain values?

I'm really struggling with this so any help would be much appreciated

thank you

[June7]

Fields cannot be dynamically selected in query object. A field (column) is either selected or it isn't.

What are you really trying to accomplish?

[Wagon]

Hi,

The data in my original table is the responses to a customer satisfaction questionaire. What I need is only the questions that have been answered in a form so it's possible to check if the correct response was recorded

Thank you

[orange]

What are your table designs?
It seems you have:
Questions and customer responses

and Questions with Correct answers.

So, based on question numbers, you are trying to determine
if Customer Response = correct answer????

Is that correct?

[Wagon]

Yeah that's exactly it.

What I'm trying to do is pull each record into a form 1 by 1 where I can check each response has been populated correctly

Thank you

[June7]

How this is done depends on the database structure. If you want to provide for analysis, follow instructions at bottom of my post. If you are really struggling, I suspect the data structure is not normalized. If there is a field for each question then my suspicion is confirmed.

[Wagon]

Yes there is a field per question,

Does this mean what I'm trying to do won't be possible?

Thank you

[orange]

Wagon,

Data is stored in tables. Forms are a means of displaying data to users.
What this all means is that, if your underlying tables and relationships support your requirements, then
there will be some way to present these in a form(s).

We really need to see your tables and relationships.

[Wagon]

At the minute all I have is a standard Table with Names, Addresses and responses to some questions, I haven't done anything else yet as I didn't want to do too much before I knew what i was trying was possible

Is it possible to only display columns in a form that are populated?

[orange]

Can you show us a few questions and responses?
Are you responses specific eg a, b or c?

Customer --takes---quiz

Customer reviews question and provides response

CustomerResponses are compared with CorrectAnswers

[Wagon]

The Questions are currently given code numbers e.g. 100,101,102 etc

The Responses are yes, no, N/A or blank. Ideally I'd just like the Question codes and Answers where the answer is Yes or no

thank you

[orange]

Consider:

tblCorrect
TestNo
QuestionNo
Response


tblUserResponse
UserID
TestNo
QuestionNo
UserResponse

tblUser
UserId
UserFName
UserLName
OtherUserInfo
TestDate

So tblCorrect could be

100,1,yes (test 100, question1, correct response = yes)
100,2,yes
100,3,no
.....

I added TestNo so that you could reorganize or select different questions(reuse questions) in a different order.

and the tblUserResponse could be

275,100,1,yes (userid = 275, testno = 100, question=1, response = yes)

In this example, user 275 got question1 correct (his/her response = correct answer to question1 in test 100)

[June7]

We really need to know more about the nature of these questions. I presume this is a standard set of questions that will be repeatedly presented to respondents. Is this some kind of a survey? Maybe TestNo should be SurveyNo and TestDate would be SurveyDate. Could each user take the test/survey more than once?

I am not sure TestNo would belong in tblCorrect. Would the correct answer be different between tests/surveys? Do you want to associate multiple respondents with the same test/survey?

Questionnaire type db has been topic of numerous threads.

[orange]

Good questions June. My thinking was he may have to juggle questions on different tests (I've heard that students can glean info from old tests), so testno might be a means to reduce "cheating"/"excellent memory syndrome...". In fact there may be 200 (1000) questions and answers from which 50 are chosen randomly to create a specific testNo.

Need more info from OP.