Re: Calculate days between dates in a query
- From: Dan <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 27 Feb 2008 11:19:06 -0800
The [Date-Time] field is a Date/Time data type. I tried your query and got
these results;
WELL# WELL NAME DATE-TIME STATUS STCODE PriorDT
11826-9 A Little Tr1 #9 3/8/2007 8:32:00 AM P
11826-9 A Little Tr1 #9 4/2/2007 3:16:00 PM P 4/2/2007 3:16:00 PM
11826-9 A Little Tr1 #9 6/4/2007 8:37:00 AM N FL 6/4/2007 8:37:00 AM
11826-9 A Little Tr1 #9 8/8/2007 9:22:00 AM P 8/8/2007 9:22:00 AM
11852-1 A Little Tr2 #1 11/8/2005 10:26:00 AM P
11852-1 A Little Tr2 #1 3/20/2006 1:29:00 PM P 3/20/2006 1:29:00 PM
11852-1 A Little Tr2 #1 5/15/2006 10:41:00 AM P 5/15/2006 10:41:00 AM
11852-1 A Little Tr2 #1 6/24/2006 8:38:00 AM N BP 6/24/2006 8:38:00 AM
11852-1 A Little Tr2 #1 6/26/2006 2:45:00 PM P 6/26/2006 2:45:00 PM
It's not returning the prior date, it's returning the current date which I
guess is why I'm getting zeros in my query.
"Allen Browne" wrote:
That sort of thing looks right..
We are assuming here that the field named [DATE-TIME] is actually a
Date/Time field (not a Text field).
Perhaps you could try this, and see if it is returning the correct date:
SELECT [All Daily Production Data].[WELL#],
[Base Wells].[WELL NAME],
[All Daily Production Data].[DATE-TIME],
[All Daily Production Data].STATUS,
[All Daily Production Data].STCODE,
(SELECT Max([All Daily Production Data].[DATE-TIME])
AS PriorDateTime
FROM [All Daily Production Data] AS Dup
WHERE [All Daily Production Data].[Well#] = Dup.[Well#]
AND Dup.[DATE-TIME] <
[All Daily Production Data].[DATE-TIME]) AS PriorDT
FROM [Base Wells] LEFT JOIN [All Daily Production Data]
ON [Base Wells].[WELL#]=[All Daily Production Data].[WELL#]
WHERE (([All Daily Production Data].STATUS IN ("P", "N"))
AND ([Base Wells].CLASS = "W"))
ORDER BY [Base Wells].[WELL NAME],
[All Daily Production Data].[DATE-TIME];
Once you get that working, you can use DateDiff() to calculate the number of
days.
And once you get that working, you can refine it with your special
requirements to identify the last record for each well and treat it
differently.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:066DBDC8-6B84-48E6-8DA3-4A95C0EBBB6E@xxxxxxxxxxxxxxxx
Allen,
I tried implementing a subquery as the following;
SELECT [All Daily Production Data].[WELL#], [Base Wells].[WELL NAME], [All
Daily Production Data].[DATE-TIME], [All Daily Production Data].STATUS,
[All
Daily Production Data].STCODE, [DATE-TIME]-(SELECT Max([All Daily
Production
Data].[DATE-TIME]) FROM [All Daily Production Data] AS Dup WHERE [All
Daily
Production Data].[Well#] = Dup.[Well#] AND Dup.[DATE-TIME] < [All Daily
Production Data].[DATE-TIME]) AS NumDays
FROM [Base Wells] LEFT JOIN [All Daily Production Data] ON [Base
Wells].[WELL#]=[All Daily Production Data].[WELL#]
WHERE ((([All Daily Production Data].STATUS)="P" Or ([All Daily Production
Data].STATUS)="N") AND (([Base Wells].CLASS)="W"))
ORDER BY [Base Wells].[WELL NAME], [All Daily Production
Data].[DATE-TIME];
But, something is wrong in my NumDays calculation. The number of days
between records is not correct. The output looks like this;
WELL# WELL NAME DATE-TIME STATUS STCODE NumDays
11826-9 A Little Tr1 #9 3/8/2007 8:32:00 AM P
11826-9 A Little Tr1 #9 4/2/2007 3:16:00 PM P 0
11826-9 A Little Tr1 #9 6/4/2007 8:37:00 AM N FL 0
11826-9 A Little Tr1 #9 8/8/2007 9:22:00 AM P 0
11852-1 A Little Tr2 #1 11/8/2005 10:26:00 AM P
11852-1 A Little Tr2 #1 3/20/2006 1:29:00 PM P 0
11852-1 A Little Tr2 #1 5/15/2006 10:41:00 AM P 0
11852-1 A Little Tr2 #1 6/24/2006 8:38:00 AM N BP 0
11852-1 A Little Tr2 #1 6/26/2006 2:45:00 PM P 0
11852-2 A Little Tr2 #2 11/10/2005 11:58:00 AM P
11852-2 A Little Tr2 #2 11/11/2005 10:32:00 AM P 0
11852-2 A Little Tr2 #2 3/20/2006 1:29:00 PM P 0
11852-2 A Little Tr2 #2 5/15/2006 10:41:00 AM P 0
Also, I realized that for the last record for each well I need to show the
number of days since the date of that record and the current date.
Can you help me out again? Thanks.
"Allen Browne" wrote:
See:
Subquery basics: Get the value in another record
at:
http://allenbrowne.com/subquery-01.html#AnotherRecord
The subquery will need to return the Max([All Daily Production
Data].[DATE])
where it is the same well and a lesser date.
For this to work reliably you might need an index that prevents 2
readings
from the same well on the same date.
BTW, DATE is a reserved word and could cause problems. Use square
brackets
around the name here and it should work; in other contexts Access might
misunderstand and use the system date instead of the field if you don't
rename it.
"Dan" <Dan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:450166A5-A7D0-4F56-A7FC-333DD75CEA28@xxxxxxxxxxxxxxxx
I have a query that lists dates when an oil well was either put into
production or taken off production. There are several wells in the
database
and the query retrieves all the wells. The SQL for the query is as
follows;
SELECT [Base Wells].[WELL NAME], [All Daily Production Data].DATE, [All
Daily Production Data].STATUS, [All Daily Production Data].STCODE
FROM [Base Wells] LEFT JOIN [All Daily Production Data] ON [Base
Wells].[WELL#] = [All Daily Production Data].[WELL#]
WHERE ((([All Daily Production Data].STATUS)="P" Or ([All Daily
Production
Data].STATUS)="N") AND (([Base Wells].CLASS)="W"))
ORDER BY [Base Wells].[WELL NAME], [All Daily Production Data].DATE;
I would like to add a field to the query that calculates the number of
days
between each record for each well. The calculation would have to
recognize
when the 'Well Name' has changed so it would not use the Date from the
previous record.
Is this possible? Thanks in advance for anyones help.
- Follow-Ups:
- Re: Calculate days between dates in a query
- From: Allen Browne
- Re: Calculate days between dates in a query
- References:
- Calculate days between dates in a query
- From: Dan
- Re: Calculate days between dates in a query
- From: Allen Browne
- Re: Calculate days between dates in a query
- From: Dan
- Re: Calculate days between dates in a query
- From: Allen Browne
- Calculate days between dates in a query
- Prev by Date: Date Limits Query
- Next by Date: Re: Appending Only New Data to a current Table
- Previous by thread: Re: Calculate days between dates in a query
- Next by thread: Re: Calculate days between dates in a query
- Index(es):
Relevant Pages
|
