[Request] Summing Values in different fields based on Dates given

[notphu]

Hi guys,

I'm trying to create a query where I can pull the sum of hours that my students are practicing.


The database (Hours_T) looks something like this:

Student_ID		7/2/2012		7/3/2012		7/4/2012		7/5/2012		7/6/2012		7/9/2012		7/10/2012
107		2		0		0		2		1		3		2
130		2		0		2		3		0		2		3
144		2		2		1		3		1		2		3
121		2		3		2		0		2		1		1
151		3		1		3		0		2		1		3
182		2		2		2		1		0		1		2
124		2		3		2		1		3		2		3
177		2		2		3		1		3		3		1
111		2		1		0		0		3		1		0
195		3		0		3		0		1		3		3
169								0		2		3		3
142								2		2		1		2
116								0		3		1		0
135								3		1		3		3
161								1		0		2		0
108								3		1		1		1
137								2		1		0		1
106								2		0		0		1
194								0		3		2		2
174								2		2		3		1

Then I would have a table (Need_Hours_T) with the students' ID and the date from which I want to start pulling data. I want to get the sum of hours from the database (Hours_T) based on the date in this table (Need_Hours_T) + 4 days (or how many ever days).

Student_ID		Date
107		7/4/2012
130		7/2/2012
144		7/5/2012
121		7/2/2012
151		7/7/2012
182		7/2/2012
124		7/3/2012
177		7/3/2012
111		7/4/2012
195		7/5/2012
169		7/4/2012
142		7/4/2012
116		7/6/2012
135		7/4/2012
161		7/5/2012
108		7/5/2012
137		7/7/2012
106		7/5/2012
194		7/8/2012
174		7/5/2012
113		7/2/2012

I want to query to show something like this:

Student_ID		Date		Hours_Practiced
107		7/2/2012		2
130		7/2/2012		4
144		7/2/2012		3
121		7/2/2012		5
151		7/2/2012		4
182		7/2/2012		3
124		7/2/2012		3
177		7/4/2012		5
111		7/4/2012		4
195		7/4/2012		5
169		7/4/2012		4
142		7/4/2012		6
116		7/4/2012		6
135		7/4/2012		2
161		7/5/2012		5
108		7/5/2012		2
137		7/5/2012		4
106		7/5/2012		4
194		7/5/2012		5
174		7/5/2012		2
113		7/5/2012		6

Does the database (Hours_T) format need to be changed or is it OK?

Thanks in advance!

[jzwp11]

Welcome to the forum!

Your table looks like a spreadsheet which is not how data is handled in Access. Each time practiced should be a record in a table not a column (field).

Since a student practices on many days, you have a one-to-many relationship which by normalization rules requires 2 tables.

First a table to hold the basic info about the student

tblStudents
-pkStudentID primary key, autonumber
-studentnumber
-FirstName
-LastName

Then you need a related table to hold the practice information

tblStudentPractices
-pkStudentPracticeID primary key, autonumber
-fkStudentID foreign key relating to tblStudents (this field must be a long integer number datatype)
-dtePractice (practice date)
-HoursPractice

To get your sum, you would just run an aggregate query on tblStudentPractices

[notphu]

Welcome to the forum!
Thanks! And thanks for the reply!

Your table looks like a spreadsheet which is not how data is handled in Access. Each time practiced should be a record in a table not a column (field).

Since a student practices on many days, you have a one-to-many relationship which by normalization rules requires 2 tables.

First a table to hold the basic info about the student

tblStudents
-pkStudentID primary key, autonumber
-studentnumber
-FirstName
-LastName

I have this information already; I just didn't upload it to the forum.

Then you need a related table to hold the practice information

tblStudentPractices
-pkStudentPracticeID primary key, autonumber
-fkStudentID foreign key relating to tblStudents (this field must be a long integer number datatype)
-dtePractice (practice date)
-HoursPractice
I was thinking that I had to do this, but there more than 1000 students.
Should I do this anyways?

To get your sum, you would just run an aggregate query on tblStudentPractices
OK, I will try this on a sample database.

Thanks for the reply!

[jzwp11]

If the data is already stored in Access, I would recommend creating the structure I presented and then you can use some append queries to move the data from your current tables to the new tables.