access SQL query help... complex for me, simple for you??

[domanicap]

Hello, I'm wondering if you can advise me on how to accomplish this ms access SQL query.
The data is from a simple memory task where people sit in front of the computer and see a string of letters flashed in front of them (i.e. VCBDLIWE) briefly, then they are shown one letter (i.e. I) and they need to press 1 if they think that letter was in the original string, and 2 if they don't think it was. I have the data in an access table but I can't figure out how to score it. Here's how the table is set up
ID Trial EventType Code
1 1 Picture VCBDLIWE ---> the string
2 1 Picture 8_delay
3 2 Picture I ----> the letter
4 2 Response 1 ----> their response
5 3 Picture RAMEUHTZ
6 3 Picture 8_delay
7 4 Picture N
8 4 Response 1 --> they got it wrong
etc

There's one more field (TTime) with the time it took to hit the button (reaction time) that I want to keep in there, but basically I need a query to just select out all the ones who got it right, and tell me how long it took them to hit the button (TTime).

Where I'm running into problems is that the string, the letter, and the response is all in the same field (Code).

Is there a semi-simple SQL statement where I could SELECT * FROM CODE where they got it right?

Thank you!

 

[zepphead80]

Hi:

Shouldn't be too hard. Assuming that the string, the letter, and the response is all one long string, you need to use a string function to pick out the correct responses. For instance:

SELECT [Name of your table].* 
FROM [Name of your table]
WHERE (Right([Name of your table].[Code],1)="1");

What the Right(field,num) function does is picks out the rightmost "num" of characters from a string. In this case, you want the last character in the string, and that character has to be "1" to pick out the correct responses.

Running this query will then give you a table that has all the same fields as your base table, but with just the correct responses...you can then do whatever statistics you want on TTime using aggregate functions, etc.

 

[JoeAtWork]

I don't think ZeppHead's SQL will work because it's under the assumption that if the right-most character is a "1" the answer is correct. Whereas I interpret your description as saying "1" or "2" is the user's response, but you need to compare the first record (the string) with the third record (the letter) to determine whether "1" or "2" is correct.

I can't see how an SQL statement can be written to do this, bacause your table is unnormalized. The field "Code" seems to have at least 4 different types of data (string, delay, letter, response). And it seems each memory test consists of a set of 4 records. The only way I could see of processing this is through code.

If you could create a normalized table, where each record represents the entire memory test, the results could easily be calculated. I envision such a table with having fields something like:

ID - unique record ID
Displayed - the string displayed to the tester
Letter - the letter shown to the tester
Response - the response (i.e. 1 or 2)

SELECT Count(ID) AS NumCorrect
FROM MemTest
WHERE (IIf(InStr(1,[Displayed],[Letter])>0,1,2))=[Response]

 

[zepphead80]

JoeAtWork is 100% correct...my test won't work. I did not look closely enough at your problem.

I guess the reason that I went with the structure I presented is that domanicap states that the string, the letter and the response are all in the same field. That doesn't quite match up with the way the table is shown.

In any event, your table structure has to be clarified, and corrected in the manner JoeAtWork is suggesting if it's not already set up in such a way...