Efficiency in Table Construction

[corfs1]

Hi,
I am setting up a database of school student information, which I will eventual need to output in a file format compatible for use with SPSS.

Here is my dilemma...I have a table that has the students demographic information called Students with fields as follows:

Students
s_id (fixed school assigned student identification number)
sex
race

I have a table of behavioral data that has data for each student for several years called Behavior with fields as follows:
Behavior
ID (autonumber)
s_id (fixed school assigned student identification number)
school_id (school name)
variable1
variable2
sch_yr (school year)

Eventually, I need to create a query (or something) that will be able to output the data as follows:
s_id, sex, race, variable1 in 2000, variable2 in 2000, variable1 in 2001, variable2 in 2001 (and so on, although order is not very important to me).

Do I have this table set up correctly or should I have a separate table for each year? Keep in mind that I will, at times, also need to be able to create queries to calculate data by school_id)

If the tables are set up correctly - Should I go about creating this output by creating separate queries on the Behavior table that are filtered by year and then run a query on those queries that incorporates the fields from the various years that I need? This method seems very time consuming (as I have several tables that are like Behavior but contain additional data - and thus, mean lots of queries).

Ideally, I would like a script that would find matching s_ids and pull the variable fields from duplicate s_ids onto the same row...

Thanks in advance for your help!

 

[corfs1]

Much Thanks

Thanks for your reply - I just need a stamp of approval that I was moving in the right direction, before I load in the years and years of data I have.

Much Thanks!

 

[corfs1]

As I have many variables that fall into the case of the variable 1 in 2000, variable 2 in 2000, variable 1 in 2001, variable 2 in 2001 senerio...Is there a quicker way to get those variables to pivot, without have to create all of the pivot tables?

thanks

 

[corfs1]

As I have many variables that fall into the case of the variable 1 in 2000, variable 2 in 2000, variable 1 in 2001, variable 2 in 2001 senerio...Is there a quicker way to get those variables to pivot, without have to create all of the pivot tables?

thanks