Prev: Calender to switch to the month
Next: Long list of fields, how to check if either is null
From: SF on 27 Apr 2010 00:26
Hi,

I biult a query from another one. When I run the query, Access keep ask the
value of M and F. When I click OK I get the result that I want. Is there a
way to tell Access not to display dialog asking for value of M and F?

SELECT NumberOfCitizenSpeakingByQuarter.Quaters,
Sum(NumberOfCitizenSpeakingByQuarter.[Meeting Observed]) AS [Meeting
Observed], Sum(NumberOfCitizenSpeakingByQuarter.Male) AS M,
Sum(NumberOfCitizenSpeakingByQuarter.Female) AS F, [M]/[Meeting Observed] AS
[Avg Male], [F]/[Meeting Observed] AS [Avg Female]
FROM NumberOfCitizenSpeakingByQuarter
GROUP BY NumberOfCitizenSpeakingByQuarter.Quaters, [M]/[Meeting Observed],
[F]/[Meeting Observed];


SF



From: Duane Hookom on 27 Apr 2010 01:24
IMO, don't use derived field names in other expressions. Try:
SELECT Quaters,
Sum([Meeting Observed]) AS [MeetingObserved],
Sum(Male) AS M,
Sum(Female) AS F,
Sum(Male)/Sum([Meeting Observed]) AS [Avg Male],
Sum(Female)/Sum([Meeting Observed]) AS [Avg Female]
FROM NumberOfCitizenSpeakingByQuarter
GROUP BY Quaters;


--
Duane Hookom
MS Access MVP


"SF" <samnangs(a)pactcambodia.org> wrote in message
news:#pxNLIc5KHA.420(a)TK2MSFTNGP02.phx.gbl...
> Hi,
>
> I biult a query from another one. When I run the query, Access keep ask
> the value of M and F. When I click OK I get the result that I want. Is
> there a way to tell Access not to display dialog asking for value of M and
> F?
>
> SELECT NumberOfCitizenSpeakingByQuarter.Quaters,
> Sum(NumberOfCitizenSpeakingByQuarter.[Meeting Observed]) AS [Meeting
> Observed], Sum(NumberOfCitizenSpeakingByQuarter.Male) AS M,
> Sum(NumberOfCitizenSpeakingByQuarter.Female) AS F, [M]/[Meeting Observed]
> AS [Avg Male], [F]/[Meeting Observed] AS [Avg Female]
> FROM NumberOfCitizenSpeakingByQuarter
> GROUP BY NumberOfCitizenSpeakingByQuarter.Quaters, [M]/[Meeting Observed],
> [F]/[Meeting Observed];
>
>
> SF
>
>
>
From: John W. Vinson on 27 Apr 2010 01:32
On Tue, 27 Apr 2010 11:26:32 +0700, "SF" <samnangs(a)pactcambodia.org> wrote:

>Hi,
>
>I biult a query from another one. When I run the query, Access keep ask the
>value of M and F. When I click OK I get the result that I want. Is there a
>way to tell Access not to display dialog asking for value of M and F?
>
>SELECT NumberOfCitizenSpeakingByQuarter.Quaters,
>Sum(NumberOfCitizenSpeakingByQuarter.[Meeting Observed]) AS [Meeting
>Observed], Sum(NumberOfCitizenSpeakingByQuarter.Male) AS M,
>Sum(NumberOfCitizenSpeakingByQuarter.Female) AS F, [M]/[Meeting Observed] AS
>[Avg Male], [F]/[Meeting Observed] AS [Avg Female]
>FROM NumberOfCitizenSpeakingByQuarter
>GROUP BY NumberOfCitizenSpeakingByQuarter.Quaters, [M]/[Meeting Observed],
>[F]/[Meeting Observed];
>
>
>SF
>
>

You usually can't calculate a field (your Sums M and F) and then use the
calculated field in a further calculation; you'll need to recapitulate the
expression:

SELECT NumberOfCitizenSpeakingByQuarter.Quaters,
Sum(NumberOfCitizenSpeakingByQuarter.[Meeting Observed]) AS [Meeting
Observed], Sum(NumberOfCitizenSpeakingByQuarter.Male) AS M,
Sum(NumberOfCitizenSpeakingByQuarter.Female) AS F,
Sum(NumberOfCitizenSpeakingByQuarter.Male)]/[Meeting Observed] AS
[Avg Male], Sum(NumberOfCitizenSpeakingByQuarter.Female)/[Meeting Observed] AS
[Avg Female]
FROM NumberOfCitizenSpeakingByQuarter
GROUP BY NumberOfCitizenSpeakingByQuarter.Quaters,
Sum(NumberOfCitizenSpeakingByQuarter.Male)/[Meeting Observed],
Sum(NumberOfCitizenSpeakingByQuarter.Female)/[Meeting Observed];

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Calender to switch to the month
Next: Long list of fields, how to check if either is null