Query quandary

P

pam

I am trying to calculate "Total Hrs per Student" by adding (across the row)
the duration which is the number of hours a student spends per session with a
coach for each student (114 students), then assign the student and hrs to the
appropriate coach in order to calculate "Total Hrs per Coach" (13 coaches)
and last calculate a Grand Total of the durations for a "Total Hours for
Billing Period"

Table Heading:
StudentID#=Primary Key/ Student Name / Coach Name / Duration 1 / Duration 2
/ Duration 3 / Duration 4 / Duration 5 / Total Hours for Billing Period /
Total Hrs per Coach / Total Hrs per Student.
SQL:
SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration 3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Mastersheet.[Total Hrs
per Coach], Mastersheet.[Total Hrs per Student], Mastersheet.[Total Hours for
Billing Period]
FROM Mastersheet
GROUP BY Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name], Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration 3], Mastersheet.[Duration 4], Mastersheet.[Duration 5],
Mastersheet.[Total Hrs per Coach], Mastersheet.[Total Hrs per Student],
Mastersheet.[Total Hours for Billing Period];

Is this feasible in a query? If so would someone help me?
 
J

Jeff Boyce

Pam

If you are "adding across the row", you are working with a ... spreadsheet!

A well-normalized relational table in MS Access is "deep", rather than
"wide".

The features/functions in MS Access expect relationally-oriented data, not
'sheet data.

Both you and Access will have to work much harder if you don't spend the
time now to put your data in a structure that Access knows how to (and is
set up to) use.

Is there a reason you aren't just using Excel to "add across"?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

jgroom

 I am trying to calculate "Total Hrs per Student" by adding (across therow)
the duration which is the number of hours a student spends per session with a
coach for each student (114 students), then assign the student and hrs tothe
appropriate coach in order to calculate "Total Hrs per Coach" (13 coaches)
and last calculate a Grand Total of the durations for a "Total Hours for
Billing Period"

Table Heading:
StudentID#=Primary Key/ Student Name / Coach Name / Duration 1 / Duration 2
/ Duration 3 / Duration 4 / Duration 5 / Total Hours for Billing Period /
Total Hrs per Coach / Total Hrs per Student.
SQL:
SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Mastersheet.[Total Hrs
per Coach], Mastersheet.[Total Hrs per Student], Mastersheet.[Total Hoursfor
Billing Period]
FROM Mastersheet
GROUP BY Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name], Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration 3], Mastersheet.[Duration 4], Mastersheet.[Duration5],
Mastersheet.[Total Hrs per Coach], Mastersheet.[Total Hrs per Student],
Mastersheet.[Total Hours for Billing Period];

Is this feasible in a query? If so would someone help me?
Click to expand...

I believe you're looking for something along the lines of this?

SELECT MasterSheet.[Student Name], MasterSheet.[Coach Name],
Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration 2]+MasterSheet.
[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.[Duration 5]) AS
TotalDurationPerStudentAndCoach
FROM MasterSheet
GROUP BY MasterSheet.[Student Name], MasterSheet.[Coach Name];

along with

SELECT MasterSheet.[Coach Name], Sum(MasterSheet.[Duration
1]+MasterSheet.[Duration 2]+MasterSheet.[Duration 3]+MasterSheet.
[Duration 4]+MasterSheet.[Duration 5]) AS TotalDurationPerCoach
FROM MasterSheet
GROUP BY MasterSheet.[Coach Name];

and this...

SELECT Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration
2]+MasterSheet.[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.
[Duration 5]) AS TotalDuration
FROM MasterSheet;

This should get you going.

You can do some joins on student and coach or however you need to look
at the data.
I'm not sure how you would store the totals in the mastersheet table.

Hope this helps!
 
J

Jeff Boyce

I'll point out that if you are going to build an "across" formula, you
probably also need to use the Nz() function to handle those situations in
which there is not a value in every one of the "across" fields.

Still not a good idea to do it this way, as any change to the number of
"durations" being recorded/reported will require re-engineering of any
tables, forms, reports, queries, code, etc. that were specifc to the OLD
number of "durations"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to calculate "Total Hrs per Student" by adding (across the
row)
the duration which is the number of hours a student spends per session
with a
coach for each student (114 students), then assign the student and hrs to
the
appropriate coach in order to calculate "Total Hrs per Coach" (13 coaches)
and last calculate a Grand Total of the durations for a "Total Hours for
Billing Period"

Table Heading:
StudentID#=Primary Key/ Student Name / Coach Name / Duration 1 / Duration
2
/ Duration 3 / Duration 4 / Duration 5 / Total Hours for Billing Period /
Total Hrs per Coach / Total Hrs per Student.
SQL:
SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration
3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Mastersheet.[Total Hrs
per Coach], Mastersheet.[Total Hrs per Student], Mastersheet.[Total Hours
for
Billing Period]
FROM Mastersheet
GROUP BY Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name], Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration 3], Mastersheet.[Duration 4], Mastersheet.[Duration
5],
Mastersheet.[Total Hrs per Coach], Mastersheet.[Total Hrs per Student],
Mastersheet.[Total Hours for Billing Period];

Is this feasible in a query? If so would someone help me?
Click to expand...

I believe you're looking for something along the lines of this?

SELECT MasterSheet.[Student Name], MasterSheet.[Coach Name],
Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration 2]+MasterSheet.
[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.[Duration 5]) AS
TotalDurationPerStudentAndCoach
FROM MasterSheet
GROUP BY MasterSheet.[Student Name], MasterSheet.[Coach Name];

along with

SELECT MasterSheet.[Coach Name], Sum(MasterSheet.[Duration
1]+MasterSheet.[Duration 2]+MasterSheet.[Duration 3]+MasterSheet.
[Duration 4]+MasterSheet.[Duration 5]) AS TotalDurationPerCoach
FROM MasterSheet
GROUP BY MasterSheet.[Coach Name];

and this...

SELECT Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration
2]+MasterSheet.[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.
[Duration 5]) AS TotalDuration
FROM MasterSheet;

This should get you going.

You can do some joins on student and coach or however you need to look
at the data.
I'm not sure how you would store the totals in the mastersheet table.

Hope this helps!
 
P

pam

Thanks, Jeff. I'll give it a try.

Jeff Boyce said:
I'll point out that if you are going to build an "across" formula, you
probably also need to use the Nz() function to handle those situations in
which there is not a value in every one of the "across" fields.

Still not a good idea to do it this way, as any change to the number of
"durations" being recorded/reported will require re-engineering of any
tables, forms, reports, queries, code, etc. that were specifc to the OLD
number of "durations"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to calculate "Total Hrs per Student" by adding (across the
row)
the duration which is the number of hours a student spends per session
with a
coach for each student (114 students), then assign the student and hrs to
the
appropriate coach in order to calculate "Total Hrs per Coach" (13 coaches)
and last calculate a Grand Total of the durations for a "Total Hours for
Billing Period"

Table Heading:
StudentID#=Primary Key/ Student Name / Coach Name / Duration 1 / Duration
2
/ Duration 3 / Duration 4 / Duration 5 / Total Hours for Billing Period /
Total Hrs per Coach / Total Hrs per Student.
SQL:
SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration
3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Mastersheet.[Total Hrs
per Coach], Mastersheet.[Total Hrs per Student], Mastersheet.[Total Hours
for
Billing Period]
FROM Mastersheet
GROUP BY Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name], Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration 3], Mastersheet.[Duration 4], Mastersheet.[Duration
5],
Mastersheet.[Total Hrs per Coach], Mastersheet.[Total Hrs per Student],
Mastersheet.[Total Hours for Billing Period];

Is this feasible in a query? If so would someone help me?
Click to expand...

I believe you're looking for something along the lines of this?

SELECT MasterSheet.[Student Name], MasterSheet.[Coach Name],
Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration 2]+MasterSheet.
[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.[Duration 5]) AS
TotalDurationPerStudentAndCoach
FROM MasterSheet
GROUP BY MasterSheet.[Student Name], MasterSheet.[Coach Name];

along with

SELECT MasterSheet.[Coach Name], Sum(MasterSheet.[Duration
1]+MasterSheet.[Duration 2]+MasterSheet.[Duration 3]+MasterSheet.
[Duration 4]+MasterSheet.[Duration 5]) AS TotalDurationPerCoach
FROM MasterSheet
GROUP BY MasterSheet.[Coach Name];

and this...

SELECT Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration
2]+MasterSheet.[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.
[Duration 5]) AS TotalDuration
FROM MasterSheet;

This should get you going.

You can do some joins on student and coach or however you need to look
at the data.
I'm not sure how you would store the totals in the mastersheet table.

Hope this helps!
Click to expand...
 
P

pam

I'm just going to suggest they stick with Excel on this one. Thanks again

Jeff Boyce said:
I'll point out that if you are going to build an "across" formula, you
probably also need to use the Nz() function to handle those situations in
which there is not a value in every one of the "across" fields.

Still not a good idea to do it this way, as any change to the number of
"durations" being recorded/reported will require re-engineering of any
tables, forms, reports, queries, code, etc. that were specifc to the OLD
number of "durations"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am trying to calculate "Total Hrs per Student" by adding (across the
row)
the duration which is the number of hours a student spends per session
with a
coach for each student (114 students), then assign the student and hrs to
the
appropriate coach in order to calculate "Total Hrs per Coach" (13 coaches)
and last calculate a Grand Total of the durations for a "Total Hours for
Billing Period"

Table Heading:
StudentID#=Primary Key/ Student Name / Coach Name / Duration 1 / Duration
2
/ Duration 3 / Duration 4 / Duration 5 / Total Hours for Billing Period /
Total Hrs per Coach / Total Hrs per Student.
SQL:
SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration
3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Mastersheet.[Total Hrs
per Coach], Mastersheet.[Total Hrs per Student], Mastersheet.[Total Hours
for
Billing Period]
FROM Mastersheet
GROUP BY Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name], Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration 3], Mastersheet.[Duration 4], Mastersheet.[Duration
5],
Mastersheet.[Total Hrs per Coach], Mastersheet.[Total Hrs per Student],
Mastersheet.[Total Hours for Billing Period];

Is this feasible in a query? If so would someone help me?
Click to expand...

I believe you're looking for something along the lines of this?

SELECT MasterSheet.[Student Name], MasterSheet.[Coach Name],
Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration 2]+MasterSheet.
[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.[Duration 5]) AS
TotalDurationPerStudentAndCoach
FROM MasterSheet
GROUP BY MasterSheet.[Student Name], MasterSheet.[Coach Name];

along with

SELECT MasterSheet.[Coach Name], Sum(MasterSheet.[Duration
1]+MasterSheet.[Duration 2]+MasterSheet.[Duration 3]+MasterSheet.
[Duration 4]+MasterSheet.[Duration 5]) AS TotalDurationPerCoach
FROM MasterSheet
GROUP BY MasterSheet.[Coach Name];

and this...

SELECT Sum(MasterSheet.[Duration 1]+MasterSheet.[Duration
2]+MasterSheet.[Duration 3]+MasterSheet.[Duration 4]+MasterSheet.
[Duration 5]) AS TotalDuration
FROM MasterSheet;

This should get you going.

You can do some joins on student and coach or however you need to look
at the data.
I'm not sure how you would store the totals in the mastersheet table.

Hope this helps!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What am I doing wrong? 8
Help to create a formulae 1
Start Date Not Calculating Correctly? 0
Variable statement 3
Variable substitution 1
Receiving unexpected Duration 0
Summing Calculated Fields 11
Extract data from previous week, previous two weeks, etc. 2

Top