Difference by Min & Max Date

[David127]

I've got a historical table of individuals weights. I'd like the query to
calculate the weight change from the earliest date to the most current date.
Below is example Data. Thanks!

EmpId First Name Last Name Weight Date
1068 Jack Hendricks 110 10-Nov-08
1068 Jack Hendricks 115 01-Jan-09
1069 Sam Fredericks 175 01-Oct-08
1069 Sam Fredericks 185 05-Jan-09
1237 David James 135 01-Jan-08
1237 David James 155 05-Oct-09
1237 David James 199 13-Jan-09

123769 David Slack 199 1/13/2009

 

[John W. Vinson]

I've got a historical table of individuals weights. I'd like the query to
calculate the weight change from the earliest date to the most current date.
Below is example Data. Thanks!

EmpId First Name Last Name Weight Date
1068 Jack Hendricks 110 10-Nov-08
1068 Jack Hendricks 115 01-Jan-09
1069 Sam Fredericks 175 01-Oct-08
1069 Sam Fredericks 185 05-Jan-09
1237 David James 135 01-Jan-08
1237 David James 155 05-Oct-09
1237 David James 199 13-Jan-09

123769 David Slack 199 1/13/2009

A couple of Subqueries in a query can do this:

SELECT EmpID, [First Name], [Last Name], (SELECT Weight FROM tablename AS X
WHERE X.EmpID = tablename.EmpID AND [Date] = (SELECT Max([Date]) FROM
tablename AS Y WHERE Y.EmpID = tablename.EmpID)) - (SELECT Weight FROM
tablename AS W WHERE W.EmpID = tablename.EmpID AND [Date] = (SELECT
Min([Date]) FROM tablename AS Z WHERE Z.EmpID = tablename.EmpID)) AS
WeightChange;

Untested air code... you may need to tweak it.

One suggestion: don't use Date as a fieldname, it's a reserved word and can be
mistaken for the Date() today's-date function.

 

[David127]

Hi John, thanks for the speedy reply. I get a reserved error (-3025). I
tweaked the table & query to match as follows:
Table name is "table1" & "Date" is now "Dt"

Query is:
SELECT EmpID, [First Name], [Last Name], (SELECT Weight FROM table1 AS X
WHERE X.EmpID = table1.EmpID AND [Dt] = (SELECT Max([Dt]) FROM table1 AS Y
WHERE Y.EmpID = table1.EmpID)) - (SELECT Weight FROM table1 AS W WHERE
W.EmpID = table1.EmpID AND [Dt] = (SELECT Min([Dt]) FROM table1 AS Z WHERE
Z.EmpID = table1.EmpID)) AS WeightChange;

I've got a historical table of individuals weights. I'd like the query to
calculate the weight change from the earliest date to the most current date.
Below is example Data. Thanks!

EmpId First Name Last Name Weight Date
1068 Jack Hendricks 110 10-Nov-08
1068 Jack Hendricks 115 01-Jan-09
1069 Sam Fredericks 175 01-Oct-08
1069 Sam Fredericks 185 05-Jan-09
1237 David James 135 01-Jan-08
1237 David James 155 05-Oct-09
1237 David James 199 13-Jan-09

123769 David Slack 199 1/13/2009

A couple of Subqueries in a query can do this:

SELECT EmpID, [First Name], [Last Name], (SELECT Weight FROM tablename AS X
WHERE X.EmpID = tablename.EmpID AND [Date] = (SELECT Max([Date]) FROM
tablename AS Y WHERE Y.EmpID = tablename.EmpID)) - (SELECT Weight FROM
tablename AS W WHERE W.EmpID = tablename.EmpID AND [Date] = (SELECT
Min([Date]) FROM tablename AS Z WHERE Z.EmpID = tablename.EmpID)) AS
WeightChange;

Untested air code... you may need to tweak it.

One suggestion: don't use Date as a fieldname, it's a reserved word and can be
mistaken for the Date() today's-date function.

 

[John W. Vinson]

Hi John, thanks for the speedy reply. I get a reserved error (-3025). I
tweaked the table & query to match as follows:
Table name is "table1" & "Date" is now "Dt"

Query is:
SELECT EmpID, [First Name], [Last Name], (SELECT Weight FROM table1 AS X
WHERE X.EmpID = table1.EmpID AND [Dt] = (SELECT Max([Dt]) FROM table1 AS Y
WHERE Y.EmpID = table1.EmpID)) - (SELECT Weight FROM table1 AS W WHERE
W.EmpID = table1.EmpID AND [Dt] = (SELECT Min([Dt]) FROM table1 AS Z WHERE
Z.EmpID = table1.EmpID)) AS WeightChange;

It may be restructuring the query to use brackets around the subquery - and
gagging on the brackets inside it. Try replacing all the [Dt] with just plain
Dt.