stanl
02-12-2012, 11:58 AM
Given time, I would hope I could figure this out for myself, but... this is a rush request:
Given:
Access table with several field values in the format
date eligible saved
1/1/2012 100 79
1/2/2012 114 90
...
with all data filled on a daily basis. Obvioulsy the % saved is saved/eligible on any given day, or summed over a date range....
The issue is I need to create a corresponding 7-day rollover table,
date saved saved%
1/6/2012
1/7/2012
where the values are saved=sum(1/1 -1/6) saved % = saved 1-6/eligible 1-6
This is easily accomplished if the data were in Excel, as the first formulas on the 7th day could be copied going forward.
So assuming I can obtain
x= first date (in main table)
y=x+6 = first date to start new table (as inserted into new table)
I need to create a loop to gather
sum of eligible, sum of saved from first table where date >=x and date<=y
insert y as date, sum of saved, sum of saved/sum of eligible into new table.
quit when no data from main table.
Hopefully this makes sense...:dunno
TIA Stan
Given:
Access table with several field values in the format
date eligible saved
1/1/2012 100 79
1/2/2012 114 90
...
with all data filled on a daily basis. Obvioulsy the % saved is saved/eligible on any given day, or summed over a date range....
The issue is I need to create a corresponding 7-day rollover table,
date saved saved%
1/6/2012
1/7/2012
where the values are saved=sum(1/1 -1/6) saved % = saved 1-6/eligible 1-6
This is easily accomplished if the data were in Excel, as the first formulas on the 7th day could be copied going forward.
So assuming I can obtain
x= first date (in main table)
y=x+6 = first date to start new table (as inserted into new table)
I need to create a loop to gather
sum of eligible, sum of saved from first table where date >=x and date<=y
insert y as date, sum of saved, sum of saved/sum of eligible into new table.
quit when no data from main table.
Hopefully this makes sense...:dunno
TIA Stan