Record count

[Noah114]

I have a query that takes the field 'PLAN NAME' and 'SALES' and the purpose of the query is to total sales for the plan name. How do I create a field that will count the records. In other words

PLAN NAME SALES RECORD
I.H.A. $100 1
U.P.M.C. $98 2
TUFTS $96 3

and so forth

 

[randy700]

Select Count(Record) As NumRecs FROM TableName

Randy

 

[Noah114]

I get counts all over the board;

SELECT [Express Scripts Financials tbl (#4)].[PLAN NAME], Sum([Express Scripts Financials tbl (#4)].SALES) AS SumOfSALES INTO [Top 20 Plans Reference tbl]
FROM [Express Scripts Financials tbl (#4)]
GROUP BY [Express Scripts Financials tbl (#4)].[PLAN NAME]
ORDER BY Sum([Express Scripts Financials tbl (#4)].SALES) DESC;

 

[PHV]

Simply add an AutoNumber field in [Top 20 Plans Reference tbl] after you've run your MakeTable query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

SELECT [PLAN NAME], COUNT(*), SUM(SALES)
FROM [Express Scripts Financials tbl (#4)] F
GROUP BY [PLAN NAME]
ORDER BY 3 DESC;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Leslie, I guess the OP wants a ranking method ...

 

[lespaul]

I guess I can see that now that you've pointed it out.....

les