Accumulating totals

  • Thread starter Thread starter Tom R
  • Start date Start date
T

Tom R

I have a series of records as follows

Name Course Level Number of Credits
Bloggs AAAA 1 20
Bloggs AAAA 1 10
Bloggs AAAA 1 10
Bloggs AAAA 2 20
Bloggs AAAA 2 20
Smith AAAA 1 10
Smith AAAA 2 10

I want to accumulate the number of credits at each level
for each person e.g.:
Name Course Level Number of Credits
Bloggs AAAA 1 40
Bloggs AAAA 2 40
Smith AAAA 1 10
Smith AAAA 2 10

Do I use a query to do this?

Thanks

Tom
 
Yes, use a query.

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon).
Access adds a Total row to the query grid.

3. Drag Name, Course, and Level into the output grid.
Accept Group By under these fields.

4. Drag NumberOfCredits into the grid.
In the Total row beneath this field choose:
Sum
 
Tom ~

An alternative to the previous responder would be to use
the SQL view (right mouse click on Select Query and choose
SQL View). You want it to look like this and it can be
copied and pasted if your table name is TableName:

SELECT TableName.Name, TableName.Course, TableName.Level,
Sum(TableName.[Number of Credits]) AS [SumOfNumber of
Credits]
FROM TableName
GROUP BY TableName.Name, TableName.Course, TableName.Level;

One word of caution, Level is an Access reserved word or
clause that can impact how an abbreviated SQL statement
would need to look in order to function properly. For
example, TableName could be removed from all of the fields
except Level with no ambiguity problems. But if removed
from the Level field you will get an error.

SELECT Name, Course, TableName.Level, Sum([Number of
Credits]) AS [SumOfNumber of Credits]
FROM TableName
GROUP BY Name, Course, TableName.Level;

You may wish to rename this field something like
Course_Level and then the following would work fine:

SELECT Name, Course, Course_Level, Sum([Number of
Credits]) AS [SumOfNumber of Credits]
FROM TableName
GROUP BY Name, Course, Course_Level;

Not to overwhelm you, but the [] brackets in a query are
only necessary when a field or table name has blank spaces
in it. For example the underscore in Corse_Level allows me
to exclude the brackets from the query name. If I used a
space instead I would have to query it as [Course Level].

~ Gary
 
Thanks for your help on this.

Tom
-----Original Message-----
Tom ~

An alternative to the previous responder would be to use
the SQL view (right mouse click on Select Query and choose
SQL View). You want it to look like this and it can be
copied and pasted if your table name is TableName:

SELECT TableName.Name, TableName.Course, TableName.Level,
Sum(TableName.[Number of Credits]) AS [SumOfNumber of
Credits]
FROM TableName
GROUP BY TableName.Name, TableName.Course, TableName.Level;

One word of caution, Level is an Access reserved word or
clause that can impact how an abbreviated SQL statement
would need to look in order to function properly. For
example, TableName could be removed from all of the fields
except Level with no ambiguity problems. But if removed
from the Level field you will get an error.

SELECT Name, Course, TableName.Level, Sum([Number of
Credits]) AS [SumOfNumber of Credits]
FROM TableName
GROUP BY Name, Course, TableName.Level;

You may wish to rename this field something like
Course_Level and then the following would work fine:

SELECT Name, Course, Course_Level, Sum([Number of
Credits]) AS [SumOfNumber of Credits]
FROM TableName
GROUP BY Name, Course, Course_Level;

Not to overwhelm you, but the [] brackets in a query are
only necessary when a field or table name has blank spaces
in it. For example the underscore in Corse_Level allows me
to exclude the brackets from the query name. If I used a
space instead I would have to query it as [Course Level].

~ Gary
-----Original Message-----
I have a series of records as follows

Name Course Level Number of Credits
Bloggs AAAA 1 20
Bloggs AAAA 1 10
Bloggs AAAA 1 10
Bloggs AAAA 2 20
Bloggs AAAA 2 20
Smith AAAA 1 10
Smith AAAA 2 10

I want to accumulate the number of credits at each level
for each person e.g.:
Name Course Level Number of Credits
Bloggs AAAA 1 40
Bloggs AAAA 2 40
Smith AAAA 1 10
Smith AAAA 2 10

Do I use a query to do this?

Thanks

Tom




.
Click to expand...
.
Click to expand...
 
Thanks for your help on this.

Tom
-----Original Message-----
Yes, use a query.

1. Create a query into this table.

2. Depress the Totals button on the toolbar (upper sigma icon).
Access adds a Total row to the query grid.

3. Drag Name, Course, and Level into the output grid.
Accept Group By under these fields.

4. Drag NumberOfCredits into the grid.
In the Total row beneath this field choose:
Sum

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")



.
Click to expand...
 
Back
Top