Append/Agreggate Query Problem

[Guest]

Hello

I have a table NRL_Performance to hold the performance data of various
investments. It has the following structure:
ID
NRL_Date
Return

When a user selects a particular fund ID, I want to generate the data to
draw a graph of the rolling 12 month returns. I have done this in VB by
finding the start and end dates of the fund (DMax and DMin functions) and
then looping through the date range calculating the total return in 12-month
blocks, moving in one month steps. Hope that makes sense.

I'm putting the answer to that into a temporary table in order to generate
the graph in a report. However, although I am very able to do the
calculation, I can't find a way to catch the date at the same time.

When I try and include NRL_Date as one of the fields I'm inserting into my
new temporary table, I get the aggregate function error message.

This is the SQL for my append query -

sqlstment = "INSERT INTO tblGRAPH_ROLLING_ANNUAL_TEMP(Return) " & _
"SELECT (exp(sum(log(NRL_Performance.Return+1)))-1) as Return " & _
" FROM NRL_Performance WHERE NRL_Performance.ID =" & Chr(34) &
ctlID & Chr(34) & _
" AND NRL_Performance.NRL_Date BETWEEN " & "#" & loop_start &
"#" & "AND" & "#" & loop_end & "#"

loop_start and loop_end are date functions that allow me to step through the
dataset in 12 month batches.

Ideally, I would like the first line of the SQL to look like this -

sqlstment = "INSERT INTO tblGRAPH_ROLLING_ANNUAL_TEMP(NRL_Date, Return)

Any suggestions would be appreciated.

Thanks,
Tarryn

 

[[MVP] S.Clark]

What is the "aggregate function error message"?

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Guest]

Hi

I get run-time error '3122' - "You tried to execute a query that does not
include the specified expression 'NRL_Date' as part of an aggregate function."

Thanks for any assistance you can give.

 

[[MVP] S.Clark]

Typically, this occurs with a Group By clause, but I don't see one in your
previous post.

Maybe in NRL_Performance, you need to add it to the clause, there??

 

[Marshall Barton]

I don't understand what you're trying to do here, but you're
getting the message because you are using an aggregate
function, which requires a Group By clause with the fields
to aggregate over.

Like I said before, I do not understand the purpose of the
query, but you might be able to Group By the ID field and
NRL_Date if the dates are always the first of the month. If
the dates can be any day in the month, try using:
Format(NRL_Date, "yyyy/mm")
instead of just NRL_Date. Then you can use a
Group By ID, Format(NRL_Date, "yyyy/mm")