Aggregate Funcation

[margaret]

I'm confused. When I try to execute the following query:

SELECT tblSales.game, tblSales.fairdate, tblSales.amount, tblSales.cash,
tblSales.ctrlyear, tblSales.DAYOFFAIR, gamemaster.gname,
IIf([CTRLYEAR]=[First Year?],Sum([amount])) AS currentamt,
IIf([CTRLYEAR]=[Second Year?],Sum([amount])) AS previousamt
FROM gamemaster INNER JOIN tblSales ON gamemaster.game = tblSales.game;

I get this message:

You tried to execute a query that does not include the specified expression
'game' as a part of an aggregate function. However, if I remove the
"currentamt" and the "previousamt", the query will work? Why?

Thanks for any help.

 

[Keith Wilby]

I'm confused. When I try to execute the following query:

SELECT tblSales.game, tblSales.fairdate, tblSales.amount, tblSales.cash,
tblSales.ctrlyear, tblSales.DAYOFFAIR, gamemaster.gname,
IIf([CTRLYEAR]=[First Year?],Sum([amount])) AS currentamt,
IIf([CTRLYEAR]=[Second Year?],Sum([amount])) AS previousamt
FROM gamemaster INNER JOIN tblSales ON gamemaster.game = tblSales.game;

I get this message:

You tried to execute a query that does not include the specified
expression
'game' as a part of an aggregate function. However, if I remove the
"currentamt" and the "previousamt", the query will work? Why?

Thanks for any help.

Is "gname" a typo or a mistake in your query?

Keith.

 

[margaret]

Neither ... gname is actually a field (It stands for game name)

I'm confused. When I try to execute the following query:

SELECT tblSales.game, tblSales.fairdate, tblSales.amount, tblSales.cash,
tblSales.ctrlyear, tblSales.DAYOFFAIR, gamemaster.gname,
IIf([CTRLYEAR]=[First Year?],Sum([amount])) AS currentamt,
IIf([CTRLYEAR]=[Second Year?],Sum([amount])) AS previousamt
FROM gamemaster INNER JOIN tblSales ON gamemaster.game = tblSales.game;

I get this message:

You tried to execute a query that does not include the specified
expression
'game' as a part of an aggregate function. However, if I remove the
"currentamt" and the "previousamt", the query will work? Why?

Thanks for any help.

Is "gname" a typo or a mistake in your query?

Keith.

 

[Duane Hookom]

You can't use a SUM() without a GROUP BY in the SQL statement.

It looks like you are attempting to display both detailed and aggregate
information. Typically you will display the aggregates in a report footer
section using the expression Marsh suggested to you in a previous thread. You
never replied to Marsh's most recent post.

Do you really need summary and detail information in the query?

--
Duane Hookom
Microsoft Access MVP

Neither ... gname is actually a field (It stands for game name)

I'm confused. When I try to execute the following query:

SELECT tblSales.game, tblSales.fairdate, tblSales.amount, tblSales.cash,
tblSales.ctrlyear, tblSales.DAYOFFAIR, gamemaster.gname,
IIf([CTRLYEAR]=[First Year?],Sum([amount])) AS currentamt,
IIf([CTRLYEAR]=[Second Year?],Sum([amount])) AS previousamt
FROM gamemaster INNER JOIN tblSales ON gamemaster.game = tblSales.game;

I get this message:

You tried to execute a query that does not include the specified
expression
'game' as a part of an aggregate function. However, if I remove the
"currentamt" and the "previousamt", the query will work? Why?

Thanks for any help.

Is "gname" a typo or a mistake in your query?

Keith.