Query to find max date IF criteria is met

[macbooknick]

I have a table that has 4 columns.

Wellbore	Date	Kind	Chlorate
1	10/8/2014	D
1	6/23/2010	S	1952
1	8/22/2011	S	3420
1	12/25/2012	S	2840
1	10/14/2013	S	2127
1	10/25/2013	S	2836
1	10/25/2013	S	2836

I want to query the most recent date and corresponding chlorate value so I made this query.

SELECT Wellbore, Chlorate, Date
FROM Table 1
WHERE Date =
(SELECT Max(Date)
FROM Table 1 as Temp
WHERE Temp.Wellbore = Table 1.Wellbore)

Problem is that date doesn't have a value. I need to filter out the kind = D to find the max date of kind =S.

I tried this query and it will only provide a value if the max date = an S entry. Can someone help me out?

SELECT Wellbore, Chlorate, Date, Kind
FROM Table 1
WHERE (((Table 1.[Date])=(SELECT Max(Date)
FROM Table 1 as Temp


WHERE Temp.Wellbore = Table 1.Wellbore)) AND ((Table 1.Kind)="S"));

[orange]

A few things:

Date is a reserved word in Access. You ruse of Date could confuse/lead to unexpected results.
If you insist on using Date as a variable name, then enclose it in [ and] eg [Date]

Variables and field names should not have embedded spaces in Access. Again,if you isnsist on such naming then again use [ and ] eg. [Table 1]

Much easier, and you'll have fewer syntax errors by using names that are alphanumeric and "_"(underscore) only.

I don't understand this comment:

Problem is that date doesn't have a value.

I see date values for each record in your sample data.

In a normalized design you would not have 2 records with same values in all fields

1     10/25/2013     S     2836
1     10/25/2013     S     2836

What exactly do you want returned? Can you show us from the data provided, what would the result be?

SELECT ChlorateByWell.wellbore
, ChlorateByWell.Mdate
, ChlorateByWell.mtype
, ChlorateByWell.chlorate
FROM ChlorateByWell
WHERE
 (((ChlorateByWell.mtype)="S") AND 
((ChlorateByWell.Mdate)=(select max(mdate) from chlorateByWell as XXX
                                       where mtype = "s")));

which returns

wellbore
Mdate
mtype
chlorate

1
10/25/2013
S
2836

1
10/25/2013
S
2836

[macbooknick]

Thanks for the reply, apologize for the clarity, first post. Below is a larger sample of a much bigger database. There are detailed (D) samples which have other values but empty chlorate values so I need to filter those out.

Wellbore	Date	Chlorate	Kind
5040	02-Sep-10	24850	S
5041	07-Dec-10		D
5041	08-Nov-10	850	S
5042	03-Jun-10	25270	S
5042	11-Jun-10	31950	S
5042	16-Jan-13	19497	S
5042	28-Jan-13	18434	S
5043	29-Jun-10	24850	S
5043	27-Jul-10	27690	S
5043	27-Oct-10	27245	S
5044	21-Jun-10	2480	S
5044	23-Jun-10	560	S
5044	19-Jul-12	483	S
5044	08-Nov-12	2268	S
5045	08-Oct-14		D
5045	23-Jun-10	1952	S
5045	22-Aug-11	3420	S
5045	25-Dec-12	2840	S
5045	14-Oct-13	2127	S
5045	25-Oct-13	2836	S
5046	08-Oct-14		D
5046	29-Jun-10	2485	S
5046	10-Oct-10	4615	S
5046	12-Dec-11	19170	S
5047	10-Oct-10	8875	S
5048	10-Oct-10	8875	S
5048	21-Aug-11	20235	S
5049	12-Oct-10	15260	S
5049	22-Aug-11	28400	S
5049	25-Dec-12	26600	S
5049	12-May-14	22156	S
5050	12-Oct-10	10650	S

The result I'm looking for is below. I'm running into trouble with my query when the max date has a Kind column of (D) with an empty chlorate value.

Wellbore	Date	Chlorate	Kind
5040	02-Sep-10	24850	S
5041	08-Nov-10	850	S
5042	28-Jan-13	18434	S
5043	27-Oct-10	27245	S
5044	08-Nov-12	2268	S
5045	25-Oct-13	2836	S
5046	12-Dec-11	19170	S
5047	10-Oct-10	8875	S
5048	21-Aug-11	20235	S
5049	12-May-14	22156	S
5050	12-Oct-10	10650	S

[orange]

Try ( i imported your latest data into a new table)

SELECT A.wellbore, A.mDate, A.chlorate, A.mtype
FROM NewChlorate AS A
WHERE A.mDate 
In (SELECT max(mdate)                            
   FROM newchlorate AS B                              
   WHERE 
   b.Wellbore = a.wellbore      
  AND (A.mtype)="s")
ORDER BY A.wellbore, A.mDate;

[macbooknick]

I tried your suggested code and returned this, it's only returning values if the max date is also Type S. So wellbore's 5041, 5045, 5046 aren't getting a value. I can't figure it out but would it be an IIF mtype is S then find max date.

wellbore	mDate	chlorate	mtype
5040	9/2/2010	24850	S
5042	1/28/2013	18434	S
5043	10/27/2010	27245	S
5044	11/8/2012	2268	S
5047	10/10/2010	8875	S
5048	8/21/2011	20235	S
5049	5/12/2014	22156	S
5050	10/12/2010	10650	S

[orange]

Yes, I specifically used where mtype = "s", since your sample data had no values with mtype = "d".

You can try
[code]SELECT A.wellbore, A.mDate, A.chlorate, A.mtype
FROM NewChlorate AS A
WHERE A.mDate
In (SELECT max(mdate)
FROM newchlorate AS B
WHERE
b.Wellbore = a.wellbore
AND (A.chlorate) is not NULL)
ORDER BY A.wellbore, A.mDate;

[code]