Help creating Percentage Expression in Query (Design View)

P

prismlight

Hello,
Can anyone help me figure out whats wrong with this expression?

EthnicGroupPercent:[CountOfNumberInGroup]/DCount("*","[Ethnic Group")

Im trying to use it to calculate the percentages of each EthnicGroup of my
table.
Im in design view (Not SQL view).

Thanks in advance. ;)
 
J

John Vinson

Hello,
Can anyone help me figure out whats wrong with this expression?

EthnicGroupPercent:[CountOfNumberInGroup]/DCount("*","[Ethnic Group")

Im trying to use it to calculate the percentages of each EthnicGroup of my
table.
Im in design view (Not SQL view).

Thanks in advance. ;)
Click to expand...

Several things.

Your DCount() expression is missing a closing square bracket after
Ethnic Group; and even if you put the bracket in, it would give you a
count of all the records in the table or query named [Ethnic Group] -
which I don't think is what you want.

If your table has a *FIELD* named [Ethnic Group], and you want to
calculate the percentage of records in the entire table for each
group, you could use a Query

SELECT [Ethnic Group], Count(*) / DCount("*", "[YourTableName]") AS
PercentByGroup FROM YourTableName GROUP BY [Ethnic Group];

Count(*) will count all records for each ethnic group (because of the
GROUP BY expression); the DCount() will count all the records ("*") in
the table YourTableName (which of course you should edit to your
actual table name).

John W. Vinson[MVP]
 
Top