if two criteria are met then ...

[dbertanjoli]

Hello,
I already tried everything I know (I don’t know much though), and I already checked with table and query discussions but wasn’t able to resolve my problem.

I have access database and asp web form (on line questionnaire). I set up everything properly (I think) except I can’t attach scoring in my database to questions.

There are 30 questions (multiple choise5 or more possible answers) and even through answers to each questions are mostly repeatable they score differently depending on question.

For example:
Question 1: answer: very often score 5
Question 1: answer: never score 4
….

Question 2: answer; very often score 1
Question 2: answer, never score 2

So I have a table with predefined score for each option.

So in order for score to be correct it has to mach two criteria: question number and question answer. Can you please help me with this?

Debbie

 

[dbertanjoli]

ps, I tried adding questionid to my score table but it doesn't feed in from my webform

 

[georgedwilkinson]

I have access database and asp web form (on line questionnaire). I set up everything properly (I think) except I can’t attach scoring in my database to questions.

Debbie,

What does the above mean? When you say you can't attach scoring to questions, that does not compute.

For instance, if you had a "Scores" table and a "Questions" table, there are 4possibilities:

1. 1 question for multiple scores (solved with QuestionID FK in Scores table)
2. 1 score for multiple questions (solved with ScoreID FK in Questions table)
3. multiple scores for multiple questions (solved with a juntion table called QuestionScores with ID (PK), ScoreID (FK), QuestionID(FK), and potentially other defining fields)
4. the 2 are related in some other way (You need to do full analysis of your business problem and define/refine your objects)

In order to resolve your problem, you need to pick one of those possible scenarios. The first 3 are easy. If it is the 4th (which I guess), you are not asking yourself, and thus not telling us, what you need to be asking to complete your analysis so you can design the system.

Please also note that my answer holds whether you're using Access, SQL Server, Oracle, ASP, JSP, G, Acme Programming Language, or XYZ database.

When we understand this, we'll be better able to discuss how you get that data into a programming environment.

 

[dbertanjoli]

Some light in my life

I am attaching my DB. Please check tblResults. You will notice that scoring is different based on two criteria (description and Question Number). So in my query I can’t get scores showed related to each record (so it should be a name, question number, description (which is answer) and score based on question number and description. I know I made it sounds complicated but once you see it it will be clear to you what I am trying to do.

Please don’t pay attention to my existing query as I was trying to split fields and get scoring that way but I don’t like it as a long term solution.

Many thanks in advance.
Deb

 

[dbertanjoli]

Basicly I want to be able to pull out reports by name and calculate their scoring.

 

[dbertanjoli]

I just tried:
multiple scores for multiple questions (solved with a juntion table called QuestionScores with ID (PK), ScoreID (FK), QuestionID(FK), and potentially other defining fields) but it doesn't record IDs.

Maybe It is something wrong with my webform structure:

This is what I am doing:
......
' Receiving values from Form
tmpName = (Request.Form("FullName"))
tmpAge = (Request.Form("age"))
tmpDateDB = (Request.Form("dateDB"))
tmpMedicalRecord = (Request.Form("MedicalRecord"))

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.connectionstring = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
& Server.Mappath("Questions.mdb") & ";"
objConn.Open

SQLstmt = "INSERT INTO users (txtFullName, intAge, txtDateDB, intMedicalRecord)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & tmpName & "',"
SQLstmt = SQLstmt & "'" & tmpAge & "',"
SQLstmt = SQLstmt & "'" & tmpDateDB & "',"
SQLstmt = SQLstmt & "'" & tmpMedicalRecord & "'"


SQLstmt = SQLstmt & ")"
SET RS = objConn.Execute(SQLstmt)

SQLstmt = "SELECT MAX(idUsers) as MaxUserId FROM Users;"
Set RS=objConn.Execute(SQLstmt)
thisRecord = rs("MaxUserId")

For x = 0 to 30


tmpAnswer = Request.Form("Question" & x)
SQLstmt = "INSERT INTO Questions (idUser, QuestionNumber, QuestionAnswer)"
SQLstmt = SQLstmt & " VALUES ("
SQLstmt = SQLstmt & "'" & thisRecord & "',"
SQLstmt = SQLstmt & "'" & x & "',"
SQLstmt = SQLstmt & "'" & tmpAnswer & "'"
SQLstmt = SQLstmt & ")"

SET RS = objConn.Execute(SQLstmt)
next

objConn.Close
Set objConn = Nothing

Response.Write "All records were successfully entered into the database."


%>

 

[georgedwilkinson]

Some light in my life

I am attaching my DB. Please check tblResults. You will notice that scoring is different based on two criteria (description and Question Number). So in my query I can’t get scores showed related to each record (so it should be a name, question number, description (which is answer) and score based on question number and description. I know I made it sounds complicated but once you see it it will be clear to you what I am trying to do.

Debbie,

I'm so sorry for you. I've seen this design twice already and still cannot perceive what it is you think you're doing.

It isn't that it is too complex, it is not complex enough. Did you at least look at CraigDolphin's lengthy dissertation on this very topic? I sent you the link twice. My answer would almost certainly look exactly like his, sans the "tbl" prefix.

A database cannot do anything that you don't tell it to do. Best I can tell, you haven't told it to do much. Where are the tables that you can ask for the data from? You have to design them (or just use Craig's design and get an A--and I really want you to do well on this assignment).

I'm not going to go into the same dissertation that Craig did. My ADD does not allow for that (as mentioned in that post).

Is tblResult the results of a person answering the questions? Or is it a list of potential results that a user of the system can choose from?

Yoip, I'm so confused because I cannot determine within your design where the "answer" to the "question" is located and who answered it. Do yourself a favor and look throroughly at Craig's solution...try to understand it...create a design that is usable...then try to get the data in/out.

With much respect and hope for your good fortunes, George

 

[dbertanjoli]

I know I made it sounds too confusing and I know that names are not actually very descriptive (as it started as test project (I will changes names) .

So for example.
I am a patient and I will be directed to fill in this questionnaire in the waiting room.

So I will have to answer 30 questions (multiple choices). and then submit it. This is my questinarrie on line(still work in progress) http://www.opsei.bc.ca/2000040101/DebBertanjoli/Questions.asp . So know there is a table called results (but actually contains scoring for each question). So question one, answer very good, should have 5 point, or qustion 1, answer good should have 4 point, or question 2 answer very good 5 points and so on. I can't find the way to attach scores from tblResults to received records (so it is not anonomis, we will have visible name of the person, answers and scoring and send out report)

Please see by database in previous message.

Did I make it more confusing