Sum records in column continued...multiply count by 5 --------M---

[Mark909]

Thanks for the help earlier guys.

I now have another query....

Say i have a colums with like so:

Pen
Pen
Pen
Pen
Pen

I do "Group" by in one column and "Count" in another

This give me the query

Pens 4


Now say for every pen I want to automatically count 5 pencils.

Therefore in a column I'd want to calculate

"total of count" * 5


How do i get a new column to multiply the results of the count??

 

[ghetto_banjo]

You could do another query, based on the above query you already have
built.

Then you will actually have a field called "CountofPen" or whatever
your pen field is called, and then you can do a column that is 5 *
[CountOfPen]


but if this is for reporting or something else, this probably isnt the
best to go about this.

 

[Ken Sheridan]

If its only pen's which you want to include a value in the extra column you
can do it with a simple expression which multiplies the count by five for
pens, by zero for everything else e.g.

SELECT Item,
COUNT(*) AS ItemCount,
COUNT(*) * IIF(Item= "Pen",5,0) AS PencilCount
FROM Items
GROUP BY Item;

If you want to multiply by a different factor for each type of item then
create another table with one row per item and columns Item and Factor, the
latter column containing the value by which the count is to be multiplied for
that item, then join the tables on Item and for the computed column use:

COUNT(*) * Factor

Ken Sheridan
Stafford, England