Query SQL assistance

[vbnetman via AccessMonster.com]

Could someone assist with the SQL for a query. Here’s some info;

Form name: frm_viewMeters
Table name: TblMeterReading
Fields: replacementNumber, unitID, meterRead.

I’m looking for the highest meter reading (MAX) for the highest replacement
number (MAX) for the same unitID that matches Forms!frm_viewMeters!unitID.
Either that or a Dlookup….not sure of the syntax or even which way is better.

For example, if unit 1 has;

meter reading replacement number
100 1
200 1

and

meter reading replacement number
75 2
95 2

the result of the query or dlookup should be 95


Thank you in advance

 

[David F Cox]

Air code, some portions may be copyright Alzheimer :

SELECT unitId, MAX(meterread)
FROM TblMeterReading
WHERE unitID = Forms!frm_viewMeters!unitID AND replacementNumber =
(SELECT MAX(replacementNumber)
FROM TblMeterReading
WHERE TblMeterReading.unitID = Forms!frm_viewMeters!unitID )

 

[vbnetman via AccessMonster.com]

Hi David,
That does it !. I need a bit more practice here...thank you kindly for your
time.

Air code, some portions may be copyright Alzheimer :

SELECT unitId, MAX(meterread)
FROM TblMeterReading
WHERE unitID = Forms!frm_viewMeters!unitID AND replacementNumber =
(SELECT MAX(replacementNumber)
FROM TblMeterReading
WHERE TblMeterReading.unitID = Forms!frm_viewMeters!unitID )

Could someone assist with the SQL for a query. Here's some info;

[quoted text clipped - 23 lines]

Thank you in advance