Query ask for parameter value?

[SF]

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

 

[Duane Hookom]

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;

 

[John W. Vinson]

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];