simple yet beyond me

[trinity]

I have two columns:

Plantdate Totalpower
9/26/02 6217
10/31/02 5187
11/28/02 4246
12/26/02 4132
1/23/03 4313
2/21/03 4416
3/21/03 4355
4/24/03 5189
5/22/03 4237
6/26/03 5189
8/1/03 5339
8/28/03 4157
9/25/03 4270

I need to find the average use of power for each date by finding the
number of days between each date and dividing that into the power used
on that date. (dividing each power reading by 30 days is not accurate
enough). DateDiff doesn't work as there are not different date fields.
This is all between rows. I guess a procedure is the way to go. I
haven't programmed in Visual Basic. I would appreciate any pointers
in this area.

Or Is there a simple way of setting this up as a query?

Thank you

Trinity

[Larry Linson]

I think you're going to need some VBA code to do this in Access... there's
not really a "refer back to the previous record" in SQL. What are you going
to do with the result? If that is, display it in a report or form, I don't
think it's going to be very difficult.

Actually, it wouldn't be difficult in VBA code, but that would be useless
unless you were going to display or store the value.

Larry Linson
Microsof Access MVP
"trinity" <tr**********@lycos.com> wrote in message
news:f3**************************@posting.google.c om...

I have two columns:

Plantdate Totalpower
9/26/02 6217
10/31/02 5187
11/28/02 4246
12/26/02 4132
1/23/03 4313
2/21/03 4416
3/21/03 4355
4/24/03 5189
5/22/03 4237
6/26/03 5189
8/1/03 5339
8/28/03 4157
9/25/03 4270

I need to find the average use of power for each date by finding the
number of days between each date and dividing that into the power used
on that date. (dividing each power reading by 30 days is not accurate
enough). DateDiff doesn't work as there are not different date fields.
This is all between rows. I guess a procedure is the way to go. I
haven't programmed in Visual Basic. I would appreciate any pointers
in this area.

Or Is there a simple way of setting this up as a query?

Thank you

Trinity

[DFS]

trinity,

Here's an all-SQL solution involving 3 queries - no VBA coding necessary:

Query1 (note: self-join Table to itself)
SELECT Table.PlantDate, Table_1.PlantDate AS PrevPlantDate,
Table.TotalPower, Table_1.TotalPower AS PrevPower
FROM [Table] INNER JOIN [Table] AS Table_1 ON Table.PlantDate >
Table_1.PlantDate;

Query2: for each date, get the minimum other date
SELECT PrevPlantDate, Min(PlantDate) AS MinOfPlantDate
FROM Query1
GROUP BY PrevPlantDate;

Query3: join query2 to query1
SELECT Query1.*
FROM Query2 INNER JOIN Query1 ON (Query1.PrevPlantDate =
Query2.PrevPlantDate) AND (Query2.MinOfPlantDate = Query1.PlantDate);

Query 3 will give you the data you want. Note: I did it kind of quickly, so
I might have mislabeled some things, but it should be right.


"trinity" <tr**********@lycos.com> wrote in message
news:f3**************************@posting.google.c om...

I have two columns:

Plantdate Totalpower
9/26/02 6217
10/31/02 5187
11/28/02 4246
12/26/02 4132
1/23/03 4313
2/21/03 4416
3/21/03 4355
4/24/03 5189
5/22/03 4237
6/26/03 5189
8/1/03 5339
8/28/03 4157
9/25/03 4270

I need to find the average use of power for each date by finding the
number of days between each date and dividing that into the power used
on that date. (dividing each power reading by 30 days is not accurate
enough). DateDiff doesn't work as there are not different date fields.
This is all between rows. I guess a procedure is the way to go. I
haven't programmed in Visual Basic. I would appreciate any pointers
in this area.

Or Is there a simple way of setting this up as a query?

Thank you

Trinity

[DFS]

I didn't read your question thoroughly. My solution will work, but you need
to add a calculated field to the last query.

So Query3 becomes:

SELECT Query1.*, TotalPower/(PlantDate - PrevPlantDate) as CalcPower
FROM Query2 INNER JOIN Query1 ON (Query1.PrevPlantDate =
Query2.PrevPlantDate) AND (Query2.MinOfPlantDate = Query1.PlantDate);

"DFS" <no****@nospam.com> wrote in message
news:10*************@corp.supernews.com...

trinity,

Here's an all-SQL solution involving 3 queries - no VBA coding necessary:

Query1 (note: self-join Table to itself)
SELECT Table.PlantDate, Table_1.PlantDate AS PrevPlantDate,
Table.TotalPower, Table_1.TotalPower AS PrevPower
FROM [Table] INNER JOIN [Table] AS Table_1 ON Table.PlantDate >
Table_1.PlantDate;

Query2: for each date, get the minimum other date
SELECT PrevPlantDate, Min(PlantDate) AS MinOfPlantDate
FROM Query1
GROUP BY PrevPlantDate;

Query3: join query2 to query1
SELECT Query1.*
FROM Query2 INNER JOIN Query1 ON (Query1.PrevPlantDate =
Query2.PrevPlantDate) AND (Query2.MinOfPlantDate = Query1.PlantDate);

Query 3 will give you the data you want. Note: I did it kind of quickly, so I might have mislabeled some things, but it should be right.


"trinity" <tr**********@lycos.com> wrote in message
news:f3**************************@posting.google.c om...

I have two columns:

Plantdate Totalpower
9/26/02 6217
10/31/02 5187
11/28/02 4246
12/26/02 4132
1/23/03 4313
2/21/03 4416
3/21/03 4355
4/24/03 5189
5/22/03 4237
6/26/03 5189
8/1/03 5339
8/28/03 4157
9/25/03 4270

I need to find the average use of power for each date by finding the
number of days between each date and dividing that into the power used
on that date. (dividing each power reading by 30 days is not accurate
enough). DateDiff doesn't work as there are not different date fields.
This is all between rows. I guess a procedure is the way to go. I
haven't programmed in Visual Basic. I would appreciate any pointers
in this area.

Or Is there a simple way of setting this up as a query?

Thank you

Trinity