Multiple Count/Sum Queries

[Guest]

I need to have multiple queries in one report from the same table. I have set
names and numbers required per person that I need to keep track of, but I
need to have a tally of where they are in certain areas, thus the multiple
queries. I need each query to give me the total of what each person has done
so far this year in a certain field and print a monthly report showing the
status of each field. How can I have these run together in a query and create
one report showing all the sums? I don't know how to set up SQL commands.

Thanks.

 

[Guest]

It may not be the most elegant way, butin situations that sound similar I've
had reasonable success by using IIf statements against the various things
you're trying to count, e.g.:

Sum(IIf(Thing1iswhatIwant,1,0))
Sum(IIf(Thing2iswhatIwant,1,0))
etc.

If you need the actual value and not just a count, replace the 1's with
[TheActualValue] in the IIf statement.

 

[Guest]

Where would I put this?

It may not be the most elegant way, butin situations that sound similar I've
had reasonable success by using IIf statements against the various things
you're trying to count, e.g.:

Sum(IIf(Thing1iswhatIwant,1,0))
Sum(IIf(Thing2iswhatIwant,1,0))
etc.

If you need the actual value and not just a count, replace the 1's with
[TheActualValue] in the IIf statement.

I need to have multiple queries in one report from the same table. I have set
names and numbers required per person that I need to keep track of, but I
need to have a tally of where they are in certain areas, thus the multiple
queries. I need each query to give me the total of what each person has done
so far this year in a certain field and print a monthly report showing the
status of each field. How can I have these run together in a query and create
one report showing all the sums? I don't know how to set up SQL commands.

Thanks.