Total per id in table

  • Thread starter Thread starter colby
  • Start date Start date
C

colby

I have a simple database with one table that has the fields userID,
date, comments, votes. I need to figure out how to setup a query to
list each id only once and also total up the number of votes that id
hade for the previous month. The userID will have about 60 different
values that can be in the table more than once. Each record will have
one vote. During the month people will vote on people for whatever and
I just want to be able to run a query and report to have a grand total
for each userID.
 
I have a simple database with one table that has the fields userID,
date, comments, votes. I need to figure out how to setup a query to
list each id only once and also total up the number of votes that id
hade for the previous month. The userID will have about 60 different
values that can be in the table more than once. Each record will have
one vote. During the month people will vote on people for whatever and
I just want to be able to run a query and report to have a grand total
for each userID.
Click to expand...

A Totals query will do this. Create a Query based on your table,
selecting the UserID and Votes fields (include the [Date] field -
which should be renamed to something other than a reserved word, by
the way - only if you need it for criteria to select a subset of the
records).

Change the Query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M). Change the default Group By on Votes to
Sum; if you included the date field, change its Group By to WHERE and
put a criterion (such as

Between DateSerial(Year(Date()), Month(Date()), 1) AND Date

to get the votes for the current month).

This will get your total vote count.
 
John Vinson said:
I have a simple database with one table that has the fields userID,
date, comments, votes. I need to figure out how to setup a query to
list each id only once and also total up the number of votes that id
hade for the previous month. The userID will have about 60 different
values that can be in the table more than once. Each record will have
one vote. During the month people will vote on people for whatever and
I just want to be able to run a query and report to have a grand total
for each userID.
Click to expand...

A Totals query will do this. Create a Query based on your table,
selecting the UserID and Votes fields (include the [Date] field -
which should be renamed to something other than a reserved word, by
the way - only if you need it for criteria to select a subset of the
records).

Change the Query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M). Change the default Group By on Votes to
Sum; if you included the date field, change its Group By to WHERE and
put a criterion (such as

Between DateSerial(Year(Date()), Month(Date()), 1) AND Date

to get the votes for the current month).

This will get your total vote count.
Click to expand...

Thanks alot John!
I learned something new with the sigma icon, that is why I could not
figure out the sum part. Again, Thanks!
 
Back
Top