Calculating percentage of Total?

[Jay]

I have the following query:

SELECT tblSales.Person, Count(tblSales.Model) AS CountOfModel
FROM tblSales;

This obviously gives me a very simple Count of the Models sold by each
Person.

What I want is to add a field which calulates what each individual Person's
total is as a percentage of the combined total.

How can I do this. Would I need a subquery? Any help/suggestions greatly
appreciated.

Regards....Jason

 

[John Spencer]

Yes, you would need a subquery. And what you posted would not work so you
must have left something off.

SELECT Person
, Count(Model) as Count
, (SELECT Count(Model) FROM tblSales) as TotalCount
, Count(Model)/(Select Count(Model) From TblSales) as PartOfTotal
FROM tblSales
GROUP BY Person

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jay]

Many thanks John, Yes I had made a mistake in typing my original query,
that's because I was changing the field names to protect some commercial
interests, and didn't proof what I'd typed.

Thanks for the sql. Works a treat & now I understand a lot better.

One other question though? Is it possible to format the 'PartOfTotal'
calculated field without using the QBE grid? Can you specify field formats
in sql?

Regards,

Jason

Than

 

[John Spencer]

You can use the format function in the query, but that will turn the result
into a string and not a number. If you can live with that then the syntax is
Format(SomeValue,"Percent") as x

Format(Count(Model)/(Select Count(Model) From TblSales) ,"Percent") as
PartOfTotal

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..