Parameter Query with mathamatical expressions

[prismlight]

Hello again everyone,
Thanks in all who have helped me along thus far.
I have a table named FSAClients with various feilds such as EthnicGroup,
Gender, ZipCode, StartDate, EndDate ect. I am trying to create a paramter
querie that will prompts me for the Startdate and then Enddate (this part
isnt included in the below queries-please help). After promting for the
Startdate and Enddate I would like the results to list the total and
percentages of EthnicGroup (for example).

Based on all your help and ideas here is what I have so far.

SELECT [EthnicGroup], Count(*) / DCount("*", "[FSAClients]") AS
PercentByGroup FROM FSAClients GROUP BY [EthnicGroup];

Or a longer idea:

SELECT FSAClients.[EthnicGroup], Count(FSAClients.[EthnicGroup]) AS
[CountOfEthnicGroup],
DCount("[EthnicGroup]","FSAClients","[EthnicGroup]='" & [EthnicGroup] &
"'")/DCount("[EthnicGroup]","FSAClients")*100 AS Total
FROM FSAClients
GROUP BY FSAClients.[EthnicGroup],
DCount("[EthnicGroup]","FSAClients","[EthnicGroup]='" &
[EthnicGroup] & "'")/DCount("[EthnicGroup]","FSAClients")*100;

and then what would I add to this query to get it to first promt me to
specify the StartDate and Enddate (now are seperate feilds thanks to Johns
suggestion)

Thanks in advace,
Denise

 

[Dirk Goldgar]

Hello again everyone,
Thanks in all who have helped me along thus far.
I have a table named FSAClients with various feilds such as
EthnicGroup, Gender, ZipCode, StartDate, EndDate ect. I am trying to
create a paramter querie that will prompts me for the Startdate and
then Enddate (this part isnt included in the below queries-please
help). After promting for the Startdate and Enddate I would like the
results to list the total and percentages of EthnicGroup (for
example).

Based on all your help and ideas here is what I have so far.

SELECT [EthnicGroup], Count(*) / DCount("*", "[FSAClients]") AS
PercentByGroup FROM FSAClients GROUP BY [EthnicGroup];

Or a longer idea:

SELECT FSAClients.[EthnicGroup], Count(FSAClients.[EthnicGroup]) AS
[CountOfEthnicGroup],
DCount("[EthnicGroup]","FSAClients","[EthnicGroup]='" & [EthnicGroup]
& "'")/DCount("[EthnicGroup]","FSAClients")*100 AS Total
FROM FSAClients
GROUP BY FSAClients.[EthnicGroup],
DCount("[EthnicGroup]","FSAClients","[EthnicGroup]='" &
[EthnicGroup] & "'")/DCount("[EthnicGroup]","FSAClients")*100;

and then what would I add to this query to get it to first promt me
to specify the StartDate and Enddate (now are seperate feilds thanks
to Johns suggestion)

Thanks in advace,
Denise

How about something like this:

SELECT
EthnicGroup,
Count(*) AS GroupCount,
(Count(*)/
(
SELECT Count(*) FROM FSAClients
WHERE StartDate >= [Enter Start Date]
AND EndDate >= [Enter End Date]
)
) AS GroupPercent
FROM FSAClients
WHERE StartDate >= [Enter Start Date]
AND EndDate >= [Enter End Date]
GROUP BY EthnicGroup;

?
I haven't really tested it, but something along those lines ought to
work.