Aggregate Funcation

  • Thread starter Thread starter margaret
  • Start date Start date
M

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.
 
margaret said:
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.
Click to expand...

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

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

Keith Wilby said:
margaret said:
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.
Click to expand...

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

Keith.
Click to expand...
 
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


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

Keith Wilby said:
margaret said:
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.
Click to expand...

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

Keith.
Click to expand...
Click to expand...
 
Back
Top