Percent of Total Query

[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

 

[Dale Fye]

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