time since last high tide

[Guest]

Hi,
I have data with the following fields at type of info in it.
Date Time TideHt HighLow
7/21/2004 3:19 207.23 H
7/21/2004 3:20 207.23
7/21/2004 3:21 207.23
7/21/2004 3:22 207.23
7/21/2004 3:23 207.2

The HighLow column has either an H or an L in it when it is exactly high or
low tide. I want to create a field that calculates the time since the last
high tide. I was able to do this for times between each high tide, but what
I really need is a value on each line that is the minutes since the last H.
All the data are in 1 minute increments, BUT the last high tide could be on
the day prior, and often is.

Can anyone help? I'd really appreciate it.

Thanks,
cam

 

[Gary Walter]

Hi,
I have data with the following fields at type of info in it.
Date Time TideHt HighLow
7/21/2004 3:19 207.23 H
7/21/2004 3:20 207.23
7/21/2004 3:21 207.23
7/21/2004 3:22 207.23
7/21/2004 3:23 207.2

The HighLow column has either an H or an L in it when it is exactly high
or
low tide. I want to create a field that calculates the time since the
last
high tide. I was able to do this for times between each high tide, but
what
I really need is a value on each line that is the minutes since the last
H.
All the data are in 1 minute increments, BUT the last high tide could be
on
the day prior, and often is.

Probably first thing I would do is
"rewrite" data in a query...

qryCombined
(replace "yurtable" w/name of your table)

SELECT
[Date] As RecdDate,
[Time] As RecdTime,
RecdDate + RecdTime As RecdDtTm,
TideHt,
HighLow
FROM
yurtable;

all we did is combine the date and time
plus alias fields to non-Access-reserved
field names so wouldn't have to keep
putting brackets around them.

then, I think this is what you want...

Select
q.RecdDate,
q.RecdTime,
q.RecdDtTm,
(SELECT
Max(a.RecdDtTm)
FROM
qryCombined As a
WHERE
a.HighLow = "H"
AND
a.RecdDtTm <= q.RecdDtTm) As LastHDtTm,
DateDiff("s",[LastHDtTm],q.RecdDtTm)*60 As MinsSinceH
FROM
qryCombined As q;

the correlated subquery is just "saying":

when I am looking at a record with a
specific q.RecdDtTm in main query,
what was the latest (max) datetime in
the table that had an "H" that happened
earlier (or at same datetime) as q.RecdDtTm.


the "MinsSinceH" could also be something like following
to show time difference in form

hh:nn

but it will be type Text, so cannot do further
arithmetic on results

Format(DateDiff("n",[LastHDtTm],q.RecdDtTm)\60,"00")
& ":"
& Format(DateDiff("n",[LastHDtTm],q.RecdDtTm) Mod 60,"00")
AS TimeSinceLastHighTide

 

[Marshall Barton]

I have data with the following fields at type of info in it.
Date Time TideHt HighLow
7/21/2004 3:19 207.23 H
7/21/2004 3:20 207.23
7/21/2004 3:21 207.23
7/21/2004 3:22 207.23
7/21/2004 3:23 207.2

The HighLow column has either an H or an L in it when it is exactly high or
low tide. I want to create a field that calculates the time since the last
high tide. I was able to do this for times between each high tide, but what
I really need is a value on each line that is the minutes since the last H.
All the data are in 1 minute increments, BUT the last high tide could be on
the day prior, and often is.

I hope the date and time are in a single Date field. If
not, please provide more details about these fields.

SELECT T.DateTime, T.TideHt, T.HighLow,
DateDiff("n", T.DateTime, (SELECT Max(P.DateTime)
FROM table As P
WHERE P.DateTime < T.DateTime
AND P.HighLow = "H")) As Minutes
FROM table As T

 

[Guest]

Marshall,

They are in 2 seperate fields, but I figured that I might have to combine
them to make this work. I think I should be able to that pretty easily
either through a query, or adding another column and performing an update
query to put the time and date fields in one field??

I will try this on Monday and let you know. thanks, cam

 

[Marshall Barton]

Right.

Gary showed how to do that in a query if both fields are
Date/Time fields. If they are Text fields, it would be:
CDate("#" & [Date] & " " & [Time] & "#")

I recommend that you go the Update route to eliminate the
extra query, get rid of the extra field and make everything
more efficient.
--
Marsh
MVP [MS Access]


CAM wrote

 

[Guest]

Marshall and Gary,
THANKS so much! I was easily able to put the date and time fields together
(they were already 'date/time' fields. Then I used Marshall's code and it's
muy excellente. Exactly what I was looking for. Thanks so much for taking
the time.

Cheryl

Right.

Gary showed how to do that in a query if both fields are
Date/Time fields. If they are Text fields, it would be:
CDate("#" & [Date] & " " & [Time] & "#")

I recommend that you go the Update route to eliminate the
extra query, get rid of the extra field and make everything
more efficient.
--
Marsh
MVP [MS Access]


CAM wrote

They are in 2 seperate fields, but I figured that I might have to combine
them to make this work. I think I should be able to that pretty easily
either through a query, or adding another column and performing an update
query to put the time and date fields in one field??

I will try this on Monday and let you know. thanks, cam