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,
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.
:
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.
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.