Looping through a field to display running differences

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TheSho - 02 Nov 2004 22:42 GMT

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.

Reply to this Message

John Winterbottom - 03 Nov 2004 00:00 GMT

> Overview:
> Each Preventative Maintenance (PM) check has to have a running log
[quoted text clipped - 3 lines]
>
> I invite solutions that implement VBA to solve the problem.

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.

Reply to this Message

Shobhit Shanker - 03 Nov 2004 14:26 GMT

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.

Reply to this Message

Tim Marshall - 03 Nov 2004 16:35 GMT

> I want the query to look like the following:
>
[quoted text clipped - 12 lines]
> 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.

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).

Signature

Tim
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Reply to this Message

John Winterbottom - 04 Nov 2004 04:38 GMT

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

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

Reply to this Message

Shobhit Shanker - 04 Nov 2004 14:26 GMT

Hey John,

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

Cheers,

Shobhit

Reply to this Message

Dennis Lee Bieber - 04 Nov 2004 16:20 GMT

On 04 Nov 2004 13:26:54 GMT, Shobhit Shanker
<shobhit_shanker@hcm.honda.com> declaimed the following in
comp.databases.ms-access:

> Hey John,
>
> Thanks a LOT for your code. It did EXACTLY what I wanted!

    I definitely need more practice with sub-selects -- especially
when they appear in the list of "output fields" rather than in join or
where clauses...

    The best I'd come up with was:

ID CheckID Date
1 18 9/2/2003
2 18 10/1/2004
3 18 10/20/2004
4 18 10/29/2004
5 19 10/20/2003
6 19 8/29/2004
7 19 9/29/2004
8 19 10/15/2004
9 19 7/29/2004

SELECT tc.CheckID, tc.Date, (tc.Date+Elapsed) AS CheckDate,
(min(tc1.Date-tc.Date)) AS Elapsed
FROM tblCheck AS tc LEFT JOIN tblCheck AS tc1 ON tc.CheckID=tc1.CheckID
WHERE tc.Date<tc1.Date
GROUP BY tc.CheckID, tc.Date
ORDER BY tc.CheckID, tc.Date;

CheckID Date CheckDate Elapsed
18 9/2/2003 10/1/2004 395
18 10/1/2004 10/20/2004 19
18 10/20/2004 10/29/2004 9
19 10/20/2003 7/29/2004 283
19 7/29/2004 8/29/2004 31
19 8/29/2004 9/29/2004 31
19 9/29/2004 10/15/2004 16

    Which, as you can see, doesn't show the "0" elapsed lines, and
has the misfortune of needing the prior date record...

> ============================================================== <
> wlfraed@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
> wulfraed@dm.net | Bestiaria Support Staff <
> ============================================================== <
> Home Page: <http://www.dm.net/~wulfraed/> <
> Overflow Page: <http://wlfraed.home.netcom.com/> <

Reply to this Message