Calculate days between dates in a query

D

Dan

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

Allen Browne

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

Dan

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
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.
Click to expand...
Click to expand...
 
A

Allen Browne

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

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
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 said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dan

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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Wow. I have no idea why JET would be considering a date value to be less
than itself.

If there were 2 different fields that displayed the same dates, I could
understand that one could be fractionally different to the other due to
floating point inaccuracy, but any individual floating point value must be
equal to (not less than) itself. What version of Access is this? And what
service pack (see Help | About)?

If your table has a primary key, you might be able to work around the
problem by explicitly excluding the same record in the subquery's criteria:

(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])
AND (Dup.ID <> [All Daily Production Data].ID)) AS PriorDT

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
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 said:
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.

Dan said:
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.
Click to expand...
Click to expand...
Click to expand...
 
D

Dan

Allen,

I'm using Access 2002 SP3. The index field is [Well#]. I tried adding the
code you suggested but the query doesn't return any values for NumDays when I
do.

Dan

Allen Browne said:
Wow. I have no idea why JET would be considering a date value to be less
than itself.

If there were 2 different fields that displayed the same dates, I could
understand that one could be fractionally different to the other due to
floating point inaccuracy, but any individual floating point value must be
equal to (not less than) itself. What version of Access is this? And what
service pack (see Help | About)?

If your table has a primary key, you might be able to work around the
problem by explicitly excluding the same record in the subquery's criteria:

(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])
AND (Dup.ID <> [All Daily Production Data].ID)) AS PriorDT

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dan said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

[Well#] is not unique in this query.
You need a field (or combination of fields) that's unique.
You can then exclude that same field in your query.

Why you get no values returned, I don't understand.
There must be another factor here.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Append Query help needed 7
Date Query 2
Complicated Date-based Query 1
Crosstab column data count 6
Last date of production 1
Query for latest production data 2
Running Count within a Group (Time-Zero Data) 7
New Query 2

Top