On Thu, Jun 21, 2018 at 10:31 AM, Ken Dibble krdibble@stny.rr.com wrote:

Hi Folks,

I have to admit that I've never been able to figure out how to get MAX() to work correctly in queries.

Given the following tables (simplified):

Consumers

PK 1 2 3

Status

PK ConFK Date Type 1 1 {^2018/1/15} Pending 2 1 {^2018/4/15} Approved 3 2 {^2018/6/1} Closed 4 2 {^2018/4/15} Approved 5 3 {^2017/11/22} Pending 6 3 {^2018/2/3} Approved 7 3 {^2018/3/12} Suspended 8 3 {^2018/5/11} Approved

Hours

PK ConFK Date AppHours 1 2 {^2017/7/3} 10 2 2 {^2018/3/12} 14 3 3 {^2018/4/1/} 20 4 3 {^2018/5/1} 12

Query: How many Consumers have a most recent Status of Approved, and what is the sum of those Consumers' Approved Hours?

Answer: 2 Approved Consumers, having a total of 12 Hours.

Not at a machine with VFP, so I can't test this, but it looks good in Google Mail :)

SELECT Consumer.*, Status.*, SUM(Hours.AppHours) AS TotalHours FROM Consumer JOIN Status on Consumer.ConPK=Status.ConFK JOIN Hours on Consumer.ConPK=Hours.ConFK WHERE Status.Date = (SELECT MAX(Stat2.Date) FROM Status Stat2 WHERE Stat2.ConFK = Consumer.ConPK) AND Status.Type = "Approved"