is this query problem unsolvable in ms access?

[bettereachday]

Hello all,

I'm obviously inexperienced.

Here is my problem. I have data stored in one BIG microsoft access table that tracks clients, session date, session no, and score etc. (see schema below). We are trying to find ALL clients that have a score > or = 20 points higher than the score when they came for session no 1 -which is the first session.

I want to show that the client's score improves in later sessions compared to the session no 1. Hopefully that means my coworkers and I are doing something right. I have been trying various sql codes without success.

This is the schema of my client_tutor table:
tbl.client_tutor

client____ sessiondate session score
N0010____ 9/7/2006____ 1____ 84
N0020____ 9/12/2006____ 1____ 92
N0020____ 2/9/2011_____ 13____ 118
N0030____ 9/14/2006____ 1____ 95
N0030____ 12/10/2007____ 5____ 134
N0030____ 1/15/2009____ 9____ 125
N0040____ 9/19/2006____ 1____ 99
N0080____ 9/22/2006____ 1____ 92
N0080____ 1/25/2008____ 5____ 82

What I would like to do is select out those records where the client's score is at least 20 points higher than it was at session 1.

In other words I want to filter based on a logical expression that says something like this in plain english:

select client, sessiondate, session and score where the client's score1 is greater than or equal to 20 plus (the same client's score1 when the session is equal to 1).

This is very different from just selecting the max score1 for the client. I know its a bit tricky, but in the example above the only two clients that fulfil this criteria (and result of the query i'm looking for) are:

client____ sessiondate session score
N0020____ 2/9/2011_____ 13____ 118
N0030____ 12/10/2007____ 5____ 134
N0030____ 1/15/2009____ 9____ 125

Note that the same client may have had improved scores in more than one session-like client N0030 who had improved scores in session 5 and 9.

How can I write a query and select this output?

Many thanks

[GolferGuy]

First, select the table (Table1) and only select Session 1 records from this table. Then, select the same table, giving it a name within the SQL (Table1_1). Join these two tables by the Client field being equal in both tables. Then in the WHERE clause, select the record(s) from table "Table1_1" where the score is greater than or equal to (>=) the Score from "Table1". I have given you the SQL code here:

SELECT Table1.Client, Table1.SessionNumber, Table1.Score, 
  Table1_1.SessionDate, Table1_1.Score
FROM Table1 INNER JOIN Table1 AS Table1_1 
  ON Table1.Client = Table1_1.Client
WHERE (((Table1.SessionNumber)=1) 
  AND ((Table1_1.Score)>=[Table1].[Score]+20));

I do believe this will give you the records you want. It did for me.

[bettereachday]

Thanks GolferGuy!

The query did exactly what I wanted. I am now unofficialy your follower on this forum (I know this isnt twitter!).

I would like to improve my ms access and sql skills. I am at the beginner level and just finished ms access and sql for dummies books (I'm sure I'll have to read them multiple times before I can get to an intermediate level of proficiency).

What books or resources do you suggest to move me ahead quickly?

Gratefully,

bettereachday

[GolferGuy]

I don't know of any books as I learned this stuff in 1993 and what I had to help me is no longer available. But, I can tell you this. In my opinion, this is the MOST IMPORTANT thing to know when dealing with Access. Normalization! If you don't know what it is, use Google. If you know what it is, use Google to find more information and LEARN it. The better you know Normalization of tables, the better your database design will be. The better our database design, and the better your database itself will be. Normalization is the BEDROCK of any relational database design.

Normalization is not the easiest concept to grasp, but it is a MUST if you want to do good databases.

Without normalized data tables, you will constantly run into roadblocks when you are trying to do something that was not part of your original design.

But with normalized data tables, what could have been a roadblock is not. You will be amazed how many times you would be worried about a requested change to the database, actually works out very easy because your data was normalized from the very beginning!

Can you tell that normalization is very important to me? I hope so.