counting all things

  • Thread starter Thread starter Jean-Paul
  • Start date Start date
J

Jean-Paul

Hi,

This one confuses me soooo much:

I have a table with (among others):
Weeknumber
remarks

Remaks could be: Leads - Job - any_thing_else

Now my boss wants a list like:

week 40
leads 5
Job 4
any_thing_else 15

week 41
leads 12
Job 25
any_thing_else 3

I can't find a way how?
Anybody wants to help?

Thanks
 
Create a query:

SELECT weeknumber,
SUM(IIF(remarks = "Leads",1,0)) As LeadsCount,
SUM(IIF(remarks = "Job",1,0)) As JobCount,
SUM(IIF(remarks NOT IN("Leads","Job"),1,0)) As OtherCount
FROM YourTable
GROUP BY weeknumber;

The way it works is that each IIF function call returns a 1 or a 0 depending
on the value of the remarks column. The sum of the returned values is the
equivalent of counting the 1s. Use the query as the RecordSource of a report
to produce the list.

NB: the OtherCount will not include Null remarks, only those with values.
If you want Null remarks included amend the query like so:

SELECT weeknumber,
SUM(IIF(remarks = "Leads",1,0)) As LeadsCount,
SUM(IIF(remarks = "Job",1,0)) As JobCount,
COUNT(*) - SUM(IIF(remarks IN("Leads","Job"),1,0)) As OtherCount
FROM YourTable
GROUP BY weeknumber;

Ken Sheridan
Stafford, England
 
Back
Top