How do you create a query that produces a sum?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table containing the following fields: MemberID, HoursA, HoursB,
HoursC and HoursD. I want to create a query that will give me the total
Hours by MemberID. There will be multiple entries in to table for each
Member ID.

How do I create this query?

Thanks, Walt
 
Ignoring the fact that your table isn't properly normalized, the SQL will
look like

SELECT MemberID, Sum(Nz([HoursA], 0) + Nz([HoursB], 0) + Nz([HoursC], 0) +
Nz([HoursD], 0)) AS TotalHours
FROM MyTable
GROUP BY MemberID

That's assuming that you want the sum of all the hours fields.

If you want separate sums for the individual hour fields, it would be

SELECT MemberID, Sum(Nz([HoursA], 0)) AS TotalHoursA,
Sum(Nz([HoursB], 0)) AS TotalHoursB, Sum(Nz([HoursC], 0)) AS TotalHoursC,
Sum(Nz([HoursD], 0)) AS TotalHoursD
FROM MyTable
GROUP BY MemberID

You can create these queries through the query builder by turning your query
into a Totals query (View | Total)
 
Doug, Thanks for your insight. I an grateful for both solutions because on
thinking it through, I really do want both a total of all hours for each
Member ID as well as a total of each category of hours for each Member ID.

One question. You mention that the table is not "normalized". What does
this mean?

Thanks, Walt

Douglas J. Steele said:
Ignoring the fact that your table isn't properly normalized, the SQL will
look like

SELECT MemberID, Sum(Nz([HoursA], 0) + Nz([HoursB], 0) + Nz([HoursC], 0) +
Nz([HoursD], 0)) AS TotalHours
FROM MyTable
GROUP BY MemberID

That's assuming that you want the sum of all the hours fields.

If you want separate sums for the individual hour fields, it would be

SELECT MemberID, Sum(Nz([HoursA], 0)) AS TotalHoursA,
Sum(Nz([HoursB], 0)) AS TotalHoursB, Sum(Nz([HoursC], 0)) AS TotalHoursC,
Sum(Nz([HoursD], 0)) AS TotalHoursD
FROM MyTable
GROUP BY MemberID

You can create these queries through the query builder by turning your query
into a Totals query (View | Total)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Walt said:
I have a table containing the following fields: MemberID, HoursA, HoursB,
HoursC and HoursD. I want to create a query that will give me the total
Hours by MemberID. There will be multiple entries in to table for each
Member ID.

How do I create this query?

Thanks, Walt
Click to expand...
Click to expand...
 
Doug, I have another question. I would like to combine the Category Totals
and the Grand Total of all hours by Member ID in one query. What would the
SQL statement look like for this?

Thanks, Walt


Walt said:
Doug, Thanks for your insight. I an grateful for both solutions because on
thinking it through, I really do want both a total of all hours for each
Member ID as well as a total of each category of hours for each Member ID.

One question. You mention that the table is not "normalized". What does
this mean?

Thanks, Walt

Douglas J. Steele said:
Ignoring the fact that your table isn't properly normalized, the SQL will
look like

SELECT MemberID, Sum(Nz([HoursA], 0) + Nz([HoursB], 0) + Nz([HoursC], 0) +
Nz([HoursD], 0)) AS TotalHours
FROM MyTable
GROUP BY MemberID

That's assuming that you want the sum of all the hours fields.

If you want separate sums for the individual hour fields, it would be

SELECT MemberID, Sum(Nz([HoursA], 0)) AS TotalHoursA,
Sum(Nz([HoursB], 0)) AS TotalHoursB, Sum(Nz([HoursC], 0)) AS TotalHoursC,
Sum(Nz([HoursD], 0)) AS TotalHoursD
FROM MyTable
GROUP BY MemberID

You can create these queries through the query builder by turning your query
into a Totals query (View | Total)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Walt said:
I have a table containing the following fields: MemberID, HoursA, HoursB,
HoursC and HoursD. I want to create a query that will give me the total
Hours by MemberID. There will be multiple entries in to table for each
Member ID.

How do I create this query?

Thanks, Walt
Click to expand...
Click to expand...
Click to expand...
 
Having field names like HoursA, HoursB, etc is usually a sign of a repeating
group. Assuming that HoursA, HoursB, etc mean something (HoursA is regular
hours, HoursB is overtime hours and so on), it's preferred to have them as
separate rows, rather than separate fields.

Jeff Conrad has a bunch of links to explanations of database normalization
techniques at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Walt said:
Doug, Thanks for your insight. I an grateful for both solutions because
on
thinking it through, I really do want both a total of all hours for each
Member ID as well as a total of each category of hours for each Member ID.

One question. You mention that the table is not "normalized". What does
this mean?

Thanks, Walt

Douglas J. Steele said:
Ignoring the fact that your table isn't properly normalized, the SQL will
look like

SELECT MemberID, Sum(Nz([HoursA], 0) + Nz([HoursB], 0) + Nz([HoursC], 0)
+
Nz([HoursD], 0)) AS TotalHours
FROM MyTable
GROUP BY MemberID

That's assuming that you want the sum of all the hours fields.

If you want separate sums for the individual hour fields, it would be

SELECT MemberID, Sum(Nz([HoursA], 0)) AS TotalHoursA,
Sum(Nz([HoursB], 0)) AS TotalHoursB, Sum(Nz([HoursC], 0)) AS TotalHoursC,
Sum(Nz([HoursD], 0)) AS TotalHoursD
FROM MyTable
GROUP BY MemberID

You can create these queries through the query builder by turning your
query
into a Totals query (View | Total)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Walt said:
I have a table containing the following fields: MemberID, HoursA,
HoursB,
HoursC and HoursD. I want to create a query that will give me the
total
Hours by MemberID. There will be multiple entries in to table for each
Member ID.

How do I create this query?

Thanks, Walt
Click to expand...
Click to expand...
Click to expand...
 
You'd leave MemberId out of the statement:

SELECT Sum(Nz([HoursA], 0)) AS TotalHoursA,
Sum(Nz([HoursB], 0)) AS TotalHoursB, Sum(Nz([HoursC], 0)) AS TotalHoursC,
Sum(Nz([HoursD], 0)) AS TotalHoursD
FROM MyTable


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Walt said:
Doug, I have another question. I would like to combine the Category
Totals
and the Grand Total of all hours by Member ID in one query. What would
the
SQL statement look like for this?

Thanks, Walt


Walt said:
Doug, Thanks for your insight. I an grateful for both solutions because
on
thinking it through, I really do want both a total of all hours for each
Member ID as well as a total of each category of hours for each Member
ID.

One question. You mention that the table is not "normalized". What does
this mean?

Thanks, Walt

Douglas J. Steele said:
Ignoring the fact that your table isn't properly normalized, the SQL
will
look like

SELECT MemberID, Sum(Nz([HoursA], 0) + Nz([HoursB], 0) + Nz([HoursC],
0) +
Nz([HoursD], 0)) AS TotalHours
FROM MyTable
GROUP BY MemberID

That's assuming that you want the sum of all the hours fields.

If you want separate sums for the individual hour fields, it would be

SELECT MemberID, Sum(Nz([HoursA], 0)) AS TotalHoursA,
Sum(Nz([HoursB], 0)) AS TotalHoursB, Sum(Nz([HoursC], 0)) AS
TotalHoursC,
Sum(Nz([HoursD], 0)) AS TotalHoursD
FROM MyTable
GROUP BY MemberID

You can create these queries through the query builder by turning your
query
into a Totals query (View | Total)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



I have a table containing the following fields: MemberID, HoursA,
HoursB,
HoursC and HoursD. I want to create a query that will give me the
total
Hours by MemberID. There will be multiple entries in to table for
each
Member ID.

How do I create this query?

Thanks, Walt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top