1-year sum question

[Guest]

I have a query as follows:

Date Amount RunningSum
6/01/06 100 100
6/02/06 150 250
etc.

I would like to add another column to this query to calculate the sum of one
year out (i.e. 6/01/06-6/01/07....6/02/06-6/02/07....) I have been having
alot of trouble getting this to work. I would use the sum of the present day
(6/01/06 in this case) as the base date, and then build on it (i.e. this sum
plus any the sum of any activity on 6/02/07). I would appreciate any help
that anyone could give me on this. Has anyone ever built a query similar to
this?

Thanks

 

[strive4peace]

make another column in the query:

field --> DateFieldname
show --> No
criteria --> >= DateSerial(
year(date)-1,
month(date),
day(date))

this will get records in the table from the past year

..... but, if I understand you right, you want records from
this date forward, so...

field --> JustDate: DateValue(DateFieldname)
show --> No
criteria --> >= Date()


a little confusing to me is your use of RunningSum, which is
a report property (in A2K)... is this query the recordset of
a report?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Thank you for the quick response, I am still having trouble with this query
however. Let me try to explain a little better. I would like to add a column
to this query that is the total of the amount column from day 1 to day 365
(the same amount as in the running sum column for day 365). Each day going
forward would be the 1-yr running sum (day 365) total plus the amount from
that day + 365. For instance:

Day________Amount_________RunSum________1-YR Out
1 ________100 _________100 ________12,000 (total amount day
1-365)
2 ________200 _________300 ________12,000 (day 366 has no
amount)
3 ________150 _________450 ________12,100 (day 367's amount=
100)

My running sum column in the query is built with an SQL inner join query. I
realize that I don't need it for this query, I use it for another report.
Would I need to write another SQL statment for this to occur? Would a DLookup
function work to grab the value of runsum day 365? Any help appreciated.

Thank you

 

[strive4peace]

Hi Nate,

you could use a calculation in the column:

OneYrOut: dsum("DateValue([fieldname])",
"[tablename_or_queryname]",
"[Dafefieldname] >=#"
& date &"# <= "# and "
"[Dafefieldname] < # "
& dateserial(year(date)+1,
month(date), day)date) & "#")


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Thanks once again for the your help Crystal, I am having a little trouble
getting this expression to work in a query. I keep getting the error message
in each line. I will post my expression below, can anyone tell me what I am
doing wrong?

OneYrOut: dsum("DateValue([amount])","[runsumtable]","[date]>= #" & date
&"#<="# and ""[date] < #" & dateserial(year(date)+1,month(date),day(date) &
"#")

I am getting an error message before the query runs. I'm not sure what is
going wrong.
Thanks agian

Hi Nate,

you could use a calculation in the column:

OneYrOut: dsum("DateValue([fieldname])",
"[tablename_or_queryname]",
"[Dafefieldname] >=#"
& date &"# <= "# and "
"[Dafefieldname] < # "
& dateserial(year(date)+1,
month(date), day)date) & "#")


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Thank you for the quick response, I am still having trouble with this query
however. Let me try to explain a little better. I would like to add a column
to this query that is the total of the amount column from day 1 to day 365
(the same amount as in the running sum column for day 365). Each day going
forward would be the 1-yr running sum (day 365) total plus the amount from
that day + 365. For instance:

Day________Amount_________RunSum________1-YR Out
1 ________100 _________100 ________12,000 (total amount day
1-365)
2 ________200 _________300 ________12,000 (day 366 has no
amount)
3 ________150 _________450 ________12,100 (day 367's amount=
100)

My running sum column in the query is built with an SQL inner join query. I
realize that I don't need it for this query, I use it for another report.
Would I need to write another SQL statment for this to occur? Would a DLookup
function work to grab the value of runsum day 365? Any help appreciated.

Thank you


:

 

[strive4peace]

Hi Nate,

DateValue must be used on a field of data type DATE -- the
reason for doing this is to strip the time...

if your actual fieldname is DATE, you should change it as
DATE is a reserved word. Qualify the date like SaleDate or
TranDate...

try something like this for your equation:

OneYrOut: dsum
(
"[amount]",
"[runsumtable]",
"DateValue([date_fieldname]) >= #"
& date
& "# AND DateValue([date_fieldname])<= #"
& dateserial(year(date)+1,month(date),day(date))
& "#"
)

don't forget to put a space on either side of &


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Thanks once again for the your help Crystal, I am having a little trouble
getting this expression to work in a query. I keep getting the error message
in each line. I will post my expression below, can anyone tell me what I am
doing wrong?

OneYrOut: dsum("DateValue([amount])","[runsumtable]","[date]>= #" & date
&"#<="# and ""[date] < #" & dateserial(year(date)+1,month(date),day(date) &
"#")

I am getting an error message before the query runs. I'm not sure what is
going wrong.
Thanks agian

:

Hi Nate,

you could use a calculation in the column:

OneYrOut: dsum("DateValue([fieldname])",
"[tablename_or_queryname]",
"[Dafefieldname] >=#"
& date &"# <= "# and "
"[Dafefieldname] < # "
& dateserial(year(date)+1,
month(date), day)date) & "#")


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Thank you for the quick response, I am still having trouble with this query
however. Let me try to explain a little better. I would like to add a column
to this query that is the total of the amount column from day 1 to day 365
(the same amount as in the running sum column for day 365). Each day going
forward would be the 1-yr running sum (day 365) total plus the amount from
that day + 365. For instance:

Day________Amount_________RunSum________1-YR Out
1 ________100 _________100 ________12,000 (total amount day
1-365)
2 ________200 _________300 ________12,000 (day 366 has no
amount)
3 ________150 _________450 ________12,100 (day 367's amount=
100)

My running sum column in the query is built with an SQL inner join query. I
realize that I don't need it for this query, I use it for another report.
Would I need to write another SQL statment for this to occur? Would a DLookup
function work to grab the value of runsum day 365? Any help appreciated.

Thank you


:



make another column in the query:

field --> DateFieldname
show --> No
criteria --> >= DateSerial(
year(date)-1,
month(date),
day(date))

this will get records in the table from the past year

..... but, if I understand you right, you want records from
this date forward, so...

field --> JustDate: DateValue(DateFieldname)
show --> No
criteria --> >= Date()


a little confusing to me is your use of RunningSum, which is
a report property (in A2K)... is this query the recordset of
a report?

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Nate wrote:


I have a query as follows:

Date Amount RunningSum
6/01/06 100 100
6/02/06 150 250
etc.

I would like to add another column to this query to calculate the sum of one
year out (i.e. 6/01/06-6/01/07....6/02/06-6/02/07....) I have been having
alot of trouble getting this to work. I would use the sum of the present day
(6/01/06 in this case) as the base date, and then build on it (i.e. this sum
plus any the sum of any activity on 6/02/07). I would appreciate any help
that anyone could give me on this. Has anyone ever built a query similar to
this?

Thanks