Count and Multiply

[mani]

Does anyone know how to count a field and multiply the answer automaticaly.. so if i have a count function on number of names repeated in a table via nameID the result would be presented in the new query with how many times name repeated multiplyed by a defualt set number.instead of name repleated 3 times, it be 30 times..using a query. any help appreciated..thanks

 

[Tony1258]

Are you using the Totals function in the query as this should group your records.

 

[mani]

I don't need total, i need to cound number of name occurance in a table and multiply it by 10..so if a person appears twice it would display 20 and not 2..i have done the count function and it works but need to multiply it...

 

[workmad3]

SELECT COUNT(name) * 10 FROM table GROUP BY name

 

[Tony1258]

Try running a query first to group the rows by field you need and creating a table. With the unique rows in the new table you can multiply the values by whatever number you want and they will be unique.

Example
name value
Scott 1
jim 2
Jeff 2
Scott 5
Scott 2
jim 3

Create unique table,
SELECT Table1.name, Count(Table1.value) AS CountOfvalue INTO test
FROM Table1
GROUP BY Table1.name;

name CountOfvalue
Jeff 1
jim 2
Scott 3


SELECT test.name, [CountOfvalue]*3 AS Expr1
FROM test;

Hope this is what you need

Tony

 

[workmad3]

Try running a query first to group the rows by field you need and creating a table. With the unique rows in the new table you can multiply the values by whatever number you want and they will be unique.

Example
name value
Scott 1
jim 2
Jeff 2
Scott 5
Scott 2
jim 3

Create unique table,
SELECT Table1.name, Count(Table1.value) AS CountOfvalue INTO test
FROM Table1
GROUP BY Table1.name;

name CountOfvalue
Jeff 1
jim 2
Scott 3


SELECT test.name, [CountOfvalue]*3 AS Expr1
FROM test;

Hope this is what you need

Tony

Huh? What is it with people here and creating new tables to do things? It is a single simple query that is needed.