Calculating Fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have created a query that consist of the following (sample data is
shown below):
Name; Year; Readingscore; Mathscore

Bob 2003 321 300
Bob 2004 395 250
Sally 2003 300 351
Sally 2004 295 375
Carl 2003 350 300 (Note only one year of data)

I need to perform a calculation to find out how much the student increased
of decreased in Reading and Math. This will give me the learning gain.

The problem is I do not know where to begin after creating the query. Could
you please help me?

Thanks,

Robin
 
Hello, I have created a query that consist of the following (sample data is
shown below):
Name; Year; Readingscore; Mathscore

Bob 2003 321 300
Bob 2004 395 250
Sally 2003 300 351
Sally 2004 295 375
Carl 2003 350 300 (Note only one year of data)

I need to perform a calculation to find out how much the student increased
of decreased in Reading and Math. This will give me the learning gain.

The problem is I do not know where to begin after creating the query. Could
you please help me?
Click to expand...

In order to compare two records within the same table, you can use
what's called a 'self join' query.

Create a new query based on your table; add your table to the query
AGAIN - if your table is named Scores, you'll get Scores and Scores_1.
Join the two instances by Name(* see below!)

Put a critirion on Scores_1.Year of

=[Scores].[Year] - 1

You can now enter

ReadingChange: [Scores].[Readingscore] - [Scores_1].[Readingscore]

and so on.

* A couple of concerns here, if this is your actual query structure.
The words NAME and YEAR are reserved for builtin Access properties
(the Name of any object, and the builtin Year function); as such they
should not be used for fieldnames. Secondly, if you're identifying
students by just their first name (or even full name) you risk
problems if you have two students who both happen to be named Sally
Jones, or if Sally legally changes her name to Monique. A unique
StudentID is a much safer link than a name.


John W. Vinson[MVP]
 
Back
Top