What am I doing wrong?

[pam]

I'm trying to design a query that will give me total hrs by adding the
"duration" for coaching hours used as well as hrs per student. There are
thirteen coaches so I need totals for each coach as well as totals per
student. But, only some of the rows give me a total. I imported the file from
excel where the formula is already in place.

SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration 3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Sum(Mastersheet.[Total
Coaching Hours Used]) AS [SumOfTotal Coaching Hours Used],
Mastersheet.[SumOfTotal Hrs per Student]
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 Student];

 

[Jeff Boyce]

Pam

Access is not a "spreadsheet on steroids". Don't expect Access to (easily)
"sum across", like Excel does.

Well-normalized Access tables (necessary if you are to get the best use of
Access' relationally-oriented features/functions) are "deep", not "wide."

If you need "across" and "down", why not continue using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[pam]

Jeff,

You said Access can't (easily) "sum across", like Excel does. But can it be
done at all?

Pam

Access is not a "spreadsheet on steroids". Don't expect Access to (easily)
"sum across", like Excel does.

Well-normalized Access tables (necessary if you are to get the best use of
Access' relationally-oriented features/functions) are "deep", not "wide."

If you need "across" and "down", why not continue using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to design a query that will give me total hrs by adding the
"duration" for coaching hours used as well as hrs per student. There are
thirteen coaches so I need totals for each coach as well as totals per
student. But, only some of the rows give me a total. I imported the file
from
excel where the formula is already in place.

SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2], Mastersheet.[Duration
3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5], Sum(Mastersheet.[Total
Coaching Hours Used]) AS [SumOfTotal Coaching Hours Used],
Mastersheet.[SumOfTotal Hrs per Student]
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 Student];

 

[Jeff Boyce]

Sure ... and I can drive nails with my chainsaw! Not pretty, not easy, not
safe, but I can.

If you have a tool (Excel) that easily does what you need it to, why not use
it?

By the way, if you are concerned about "slicing & dicing", selecting by
criteria, you could filter that Excel spreadsheet, or create a pivot table
to help you get different looks.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You said Access can't (easily) "sum across", like Excel does. But can it
be
done at all?

Pam

Access is not a "spreadsheet on steroids". Don't expect Access to
(easily)
"sum across", like Excel does.

Well-normalized Access tables (necessary if you are to get the best use
of
Access' relationally-oriented features/functions) are "deep", not "wide."

If you need "across" and "down", why not continue using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to design a query that will give me total hrs by adding the
"duration" for coaching hours used as well as hrs per student. There
are
thirteen coaches so I need totals for each coach as well as totals per
student. But, only some of the rows give me a total. I imported the
file
from
excel where the formula is already in place.

SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration
3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5],
Sum(Mastersheet.[Total
Coaching Hours Used]) AS [SumOfTotal Coaching Hours Used],
Mastersheet.[SumOfTotal Hrs per Student]
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 Student];

 

[Dale Fye]

Sure, but it would be helpful to give us a couple of sample records so we can
see how your data is formatted. Then, give us a sample of what you would
like the output to look like, given the sample data you have given us.

Are the [Duration #] fields durations for a specific Student/Coach
combination? What is the [Total Coaching Hours Used] and [SumOfTotal Hrs per
Student] fields? It looks like these are the results from some other query.

Dale

 

[pam]

I have a "mastertable" listing each student and coach. Then I have tables for
each coach listing their students. The [Duration#] represents time each
student spends with a coach. [Total Coaching Hours Used] and [SumOfTotal Hrs
per student] was just my feeble attempt to get the sums I'm looking for.

I am trying to capture:
How many coaching hours have been used in total
How many coaching hours per coach
How many coaching hours per student
There are 13 coaches and 144 students and the duration is the time a student
spends with that coach.

I hope this helps.

 

[Dale Fye]

I can already tell you that if you have separate tables for each of the
coaches, your data structure is wrong from the get-go. It is still not
clear exactly how your data is setup. Describe the tables (field names,
primary keys, ... ) and I'll make some recommendations on normalizing your
data structure.

Then, give me some sample data from each of these tables, and then show me
the results you would like to see based on the sample data. Once I have
that I'll try to write a query that will get you the information you are
seeking.

Dale

I have a "mastertable" listing each student and coach. Then I have tables
for
each coach listing their students. The [Duration#] represents time each
student spends with a coach. [Total Coaching Hours Used] and [SumOfTotal
Hrs
per student] was just my feeble attempt to get the sums I'm looking for.

I am trying to capture:
How many coaching hours have been used in total
How many coaching hours per coach
How many coaching hours per student
There are 13 coaches and 144 students and the duration is the time a
student
spends with that coach.

I hope this helps.

Sure, but it would be helpful to give us a couple of sample records so we
can
see how your data is formatted. Then, give us a sample of what you would
like the output to look like, given the sample data you have given us.

Are the [Duration #] fields durations for a specific Student/Coach
combination? What is the [Total Coaching Hours Used] and [SumOfTotal Hrs
per
Student] fields? It looks like these are the results from some other
query.

Dale

 

[pam]

In creating a table for each coach I was duplicating the file I imported from
Excel where there is a mastersheet tab and tabs for each coach.

Here are the column headings for the information I am interested in capturing.
Table Headings:
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.

Duration represents the number of hours spent per session with a coach.
i.e., Duration 1 might be 1 hr / Duration 2 may be .5 hrs / Duration 3 may be
1 hr, etc.
I am trying to get a Grand Total of the durations for a "Total Hours for
Billing Period" as well as separate totals: 1 - "Total Hrs per Student"(114
students) and 2 - get a "Total Hrs per Coach"(13 coaches).

I can already tell you that if you have separate tables for each of the
coaches, your data structure is wrong from the get-go. It is still not
clear exactly how your data is setup. Describe the tables (field names,
primary keys, ... ) and I'll make some recommendations on normalizing your
data structure.

Then, give me some sample data from each of these tables, and then show me
the results you would like to see based on the sample data. Once I have
that I'll try to write a query that will get you the information you are
seeking.

Dale

I have a "mastertable" listing each student and coach. Then I have tables
for
each coach listing their students. The [Duration#] represents time each
student spends with a coach. [Total Coaching Hours Used] and [SumOfTotal
Hrs
per student] was just my feeble attempt to get the sums I'm looking for.

I am trying to capture:
How many coaching hours have been used in total
How many coaching hours per coach
How many coaching hours per student
There are 13 coaches and 144 students and the duration is the time a
student
spends with that coach.

I hope this helps.

Sure, but it would be helpful to give us a couple of sample records so we
can
see how your data is formatted. Then, give us a sample of what you would
like the output to look like, given the sample data you have given us.

Are the [Duration #] fields durations for a specific Student/Coach
combination? What is the [Total Coaching Hours Used] and [SumOfTotal Hrs
per
Student] fields? It looks like these are the results from some other
query.

Dale

 

[pam]

Thanks Jeff.

Sure ... and I can drive nails with my chainsaw! Not pretty, not easy, not
safe, but I can.

If you have a tool (Excel) that easily does what you need it to, why not use
it?

By the way, if you are concerned about "slicing & dicing", selecting by
criteria, you could filter that Excel spreadsheet, or create a pivot table
to help you get different looks.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

You said Access can't (easily) "sum across", like Excel does. But can it
be
done at all?

Pam

Access is not a "spreadsheet on steroids". Don't expect Access to
(easily)
"sum across", like Excel does.

Well-normalized Access tables (necessary if you are to get the best use
of
Access' relationally-oriented features/functions) are "deep", not "wide."

If you need "across" and "down", why not continue using Excel?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to design a query that will give me total hrs by adding the
"duration" for coaching hours used as well as hrs per student. There
are
thirteen coaches so I need totals for each coach as well as totals per
student. But, only some of the rows give me a total. I imported the
file
from
excel where the formula is already in place.

SELECT Mastersheet.ID, Mastersheet.[Student Name], Mastersheet.[Coach
Name],
Mastersheet.[Duration 1], Mastersheet.[Duration 2],
Mastersheet.[Duration
3],
Mastersheet.[Duration 4], Mastersheet.[Duration 5],
Sum(Mastersheet.[Total
Coaching Hours Used]) AS [SumOfTotal Coaching Hours Used],
Mastersheet.[SumOfTotal Hrs per Student]
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 Student];