Get FIRST() over data set not GROUP BY

  • Thread starter Thread starter Art Marks
  • Start date Start date
A

Art Marks

I'm trying to create a chart in Access that graphs change in body weight
(and other fields that I've left out for simplicity) from a certain start
date. I had originally hard coded the start weight, but for flexibility as
to the start date I tried using First([Weight]). Problem is that I'm
grouping by date so instead of returning the weight on the date in the WHERE
clause, I'm getting the first weight per day. How do I correct the SQL?

Thanks
--Art

SELECT (Format([Date],"DDDDD")), Avg([Weight])-First([Weight]) AS [Change in
Weight]
FROM [logWeight]
WHERE [Date] >= #10/10/04#
GROUP BY (Int([Date])),(Format([Date],"DDDDD"));
 
I'm trying to create a chart in Access that graphs change in body weight
(and other fields that I've left out for simplicity) from a certain start
date. I had originally hard coded the start weight, but for flexibility as
to the start date I tried using First([Weight]). Problem is that I'm
grouping by date so instead of returning the weight on the date in the WHERE
clause, I'm getting the first weight per day. How do I correct the SQL?

Thanks
--Art

SELECT (Format([Date],"DDDDD")), Avg([Weight])-First([Weight]) AS [Change in
Weight]
FROM [logWeight]
WHERE [Date] >= #10/10/04#
GROUP BY (Int([Date])),(Format([Date],"DDDDD"));
Click to expand...

The First() aggregate operation is pretty much useless. It returns the
first item *in disk storage order* - and you have NO control over disk
storage order.

You'll need to use a Subquery or a DLookUp to find the value of Weight
associated with the minimum date in the range.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John Vinson said:
I'm trying to create a chart in Access that graphs change in body weight
(and other fields that I've left out for simplicity) from a certain start
date. I had originally hard coded the start weight, but for flexibility as
to the start date I tried using First([Weight]). Problem is that I'm
grouping by date so instead of returning the weight on the date in the
WHERE
clause, I'm getting the first weight per day. How do I correct the SQL?

Thanks
--Art

SELECT (Format([Date],"DDDDD")), Avg([Weight])-First([Weight]) AS [Change
in
Weight]
FROM [logWeight]
WHERE [Date] >= #10/10/04#
GROUP BY (Int([Date])),(Format([Date],"DDDDD"));
Click to expand...

The First() aggregate operation is pretty much useless. It returns the
first item *in disk storage order* - and you have NO control over disk
storage order.

You'll need to use a Subquery or a DLookUp to find the value of Weight
associated with the minimum date in the range.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...


Not sure about DLookUp (am looking it up now), but I got it to work with a
Subquery:

....(Avg([Weight])-(SELECT Avg([Weight]) FROM [logWeight] WHERE [Date] =
#10/10/04#)) AS [Change in Weight]...

Thanks,
--Art
 
The First() aggregate operation is pretty much useless. It returns the
first item *in disk storage order* - and you have NO control over disk
storage order.
Click to expand...

I noticed this also.
The query ignores the sort order.
I even tried querying the Group By, First() on a pre-sorted query, and the
original table order is still what I got.
Still trying to find an easy work around.

I wonder if this has been fixed in any newer versions of Access ?

Mike Schlosser

John Vinson said:
I'm trying to create a chart in Access that graphs change in body weight
(and other fields that I've left out for simplicity) from a certain start
date. I had originally hard coded the start weight, but for flexibility as
to the start date I tried using First([Weight]). Problem is that I'm
grouping by date so instead of returning the weight on the date in the WHERE
clause, I'm getting the first weight per day. How do I correct the SQL?

Thanks
--Art

SELECT (Format([Date],"DDDDD")), Avg([Weight])-First([Weight]) AS [Change in
Weight]
FROM [logWeight]
WHERE [Date] >= #10/10/04#
GROUP BY (Int([Date])),(Format([Date],"DDDDD"));
Click to expand...

The First() aggregate operation is pretty much useless. It returns the
first item *in disk storage order* - and you have NO control over disk
storage order.

You'll need to use a Subquery or a DLookUp to find the value of Weight
associated with the minimum date in the range.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
OK. I got a quick fix.
Don't use FIRST, use MIN.
Don't use LAST, use MAX.

MIN and MAX work on strings also.
AVG does not.(had to try it).
Apparently the query goes thru a bit more processing and gets it right.

Mike Schlosser
 
I noticed this also.
The query ignores the sort order.
Click to expand...

The query returns records in sort order; but the First() and Last()
operators indeed do ignore the sort order. It's *disk storage order of
the table* pure and simple.
I even tried querying the Group By, First() on a pre-sorted query, and the
original table order is still what I got.
Still trying to find an easy work around.
Click to expand...

I see that you have... right?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
(e-mail address removed) brazil imag sex
John Vinson said:
The query returns records in sort order; but the First() and Last()
operators indeed do ignore the sort order. It's *disk storage order of
the table* pure and simple.


I see that you have... right?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Back
Top