Percent of Total Query

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I'm a relatively new Access user. I'm trying to write a
query to break out the percent of the total value for each
field category. Each record in the database has several
fields, for simplicity: SKU#, Division, Dollars. The DB
contains about 700K records. There are 7 divisions. What
is the best way to write a query (or set of queries) to
give the % of Total Dollars by Division. Otherwise, I
could just dump the totals into Excel and get the %, but
I'd rather do it all in Access.

Thanks
 
How about something like the following. This will give you the
Division, the number of dollars, and the percentage of the total
number of dollars.

SELECT D.Division, D.SumDollars,
Format(D.SumDollars/T.TotDollars, "percent")
FROM
(SELECT Division, Sum(Dollars) as SumDollars
FROM yourTable
GROUP BY Division) as D,
(SELECT Sum(Dollars) as TotDollars FROM yourTable) as T


--
HTH

Dale Fye


I'm a relatively new Access user. I'm trying to write a
query to break out the percent of the total value for each
field category. Each record in the database has several
fields, for simplicity: SKU#, Division, Dollars. The DB
contains about 700K records. There are 7 divisions. What
is the best way to write a query (or set of queries) to
give the % of Total Dollars by Division. Otherwise, I
could just dump the totals into Excel and get the %, but
I'd rather do it all in Access.

Thanks
 
Back
Top