Last date of production

  • Thread starter Thread starter KERRY J
  • Start date Start date
K

KERRY J

I have a table (tblWellDaily) that contains the daily production volume
for a group of wells. The important fields are:
wellID (primary key)
date (primary key)
volume

What I would like to do is determine the number of days that each wel
has been off production (ie. volume=0 ) based on the last date o
input.

The query contains parameter and expression...
LastInput: [Enter Date of Last Data Input (mm/dd/yy):]
NumDays: DateDiff("y",[date],[LastInput])

Example data would be...
wellID_____date________volume
well01___11/15/03________0
well01___11/14/03________0
well01___11/13/03______100
well02___11/15/03_______50
...

The query would return wellID (in this case well01), volume (from las
date of prod; in this case 100) and NumDays (in this case 2). The las
date of input in this case would be 11/15/03. If a well is producin
on 11/15/03 (volume>0) such as well02, the well would be excluded fro
the query results.

I have struggled with this one for a while without achieving th
desired results. Thanks in advance for any help
 
PARAMETERS [Enter Date of Last Data Input (mm/dd/yy):] DateTime;
SELECT WellID, [Date], Volume, [Enter Date of Last Data Input (mm/dd/yy):]
As LastInput,
NumDays: DateDiff("y",[date],[LastInput]) As NumDays
FROM tblWellDaily
WHERE [Date] =
(SELECT Max([Date]) FROM tblWellDaily AS W2
WHERE W2.WellID = tblWellDaily.WellID)

You can also do this as an INNER JOIN on the subquery to run faster, but the
result won't be updatable.

BTW, when you use a field name that's also a reserved word (Date), you must
always enclose the field name in brackets.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
 
Back
Top