Looping through a field to display running differences

Overview:
Each Preventative Maintenance (PM) check has to have a running log
of the days that it was completed. For each date, I need to know how
many days it took since the last check. I will need that information
to build reports from.

Current Position:
Right now I have the two tables linked together with a one-to-many
link (tblChecks to tblLog) with the common field being CheckID.

My Approach:
I would like to have a query that will have the date field in one
column, and the running difference in the second column.
I.e.

[Date] [DaysElapsed]
Date[1] ---
Date[2] Date[2]-Date[1]
Date[3] Date[3]-Date[2]
. .
. .
Date[i] Date[i]-Date[i-1]

Pseudo-Code -

For Each CheckID in tblLog
For Each Date
DaysElapsed[i] = DaysElapsed[i]-DaysElapsed[i-1]
Next
Next

I invite solutions that implement VBA to solve the problem. Please
keep in mind that though I have some programming background, I have no
VB or VBA experience. If code is involved, please explain where I
need to enter the code and how to invoke it.

I am comfortable with the Access environment, but have no idea how to
use VBA with it.

re: Looping through a field to display running differences

"TheSho" <shobhit_shanker@hcm.honda.com> wrote in message
news:bfd9e8fd.0411021342.552ff378@posting.google.c om...[color=blue]
> Overview:
> Each Preventative Maintenance (PM) check has to have a running log
> of the days that it was completed. For each date, I need to know how
> many days it took since the last check. I will need that information
> to build reports from.
>
>
> I invite solutions that implement VBA to solve the problem.[/color]


Use SQL for this not VBA. Post your simplified table structure with some
sample data and the output you need and someone will be able to help you.

re: Looping through a field to display running differences

Someone suggested that I provide a sample table and the output I wish to
obtain.

Here they are...

For every entry in the tblCheck table, there is a corresponding log
stored under the DateCompleted field in the tblLog table. The linking
field is CheckID

[DateCompleted] [CheckID]
09/02/03 18
10/01/04 18
10/20/04 18
10/29/04 18
10/20/03 19
07/29/04 19
08/29/04 19
09/29/04 19
10/15/04 19

For each date corresponding to a particular check (i.e. for each
CheckID), I need to know how long it has taken since the last check.

I want the query to look like the following:

[DateComp] [DaysElapsed] [CheckID]
09/02/03 - 18
10/01/04 395.00 18
10/20/04 19.00 18
10/29/04 9.00 18
10/20/03 - 19
07/29/04 283.00 19
08/29/04 31.00 19
09/29/04 31.00 19
10/15/04 16.00 19

If you know of an SQL solution to this, then please provide the code. I
am new to database developing, and I don't know how to look through each
record using SQL. A VBA solution will be okay as well.

Thanks.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

re: Looping through a field to display running differences

Shobhit Shanker wrote:
[color=blue]
> I want the query to look like the following:
>
> [DateComp] [DaysElapsed] [CheckID]
> 09/02/03 - 18
> 10/01/04 395.00 18
> 10/20/04 19.00 18
> 10/29/04 9.00 18
> 10/20/03 - 19
> 07/29/04 283.00 19
> 08/29/04 31.00 19
> 09/29/04 31.00 19
> 10/15/04 16.00 19
>
> If you know of an SQL solution to this, then please provide the code. I
> am new to database developing, and I don't know how to look through each
> record using SQL. A VBA solution will be okay as well.[/color]

Presumeably you can make the above query but can't figure out the days
elapsed column, correct?

Combine the datediff function and the now() or date() function. In your
query design view, put this into the field cell:

DaysElapsed:datediff("d", now(), DateComp)

Since this is a PM operation, you may want to look up help on datediff
and explore the firstweekday and firstweek which might have some
significance for you (such as seasonal based PMs).
--
Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

re: Looping through a field to display running differences

"Shobhit Shanker" <shobhit_shanker@hcm.honda.com> wrote in message
news:4188dca2$0$14476$c397aba@news.newsgroups.ws.. .[color=blue]
> Someone suggested that I provide a sample table and the output I wish to
> obtain.
>[/color]

This should work

select t.DateCompleted,
t.DateCompleted -
(
select max(t2.DateCompleted)
from tblCheck as t2
where t2.CheckID = t.CheckID
and t2.DateCompleted < t.DateCompleted
) as DaysElapsed,
t.CheckID
from tblCheck as t

re: Looping through a field to display running differences

Hey John,

Thanks a LOT for your code. It did EXACTLY what I wanted!

Cheers,

Shobhit



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!