A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

First and Last Problem



 
 
Thread Tools Display Modes
  #1  
Old May 26th, 2010, 08:46 PM posted to microsoft.public.access.queries
Dan[_33_]
external usenet poster
 
Posts: 2
Default First and Last Problem

For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.
  #2  
Old May 26th, 2010, 09:11 PM posted to microsoft.public.access.queries
ghetto_banjo
external usenet poster
 
Posts: 325
Default First and Last Problem

First and Last do NOT work the way they think you do. I believe they
try to see the first and last records written to disk or something,
but the results are NEVER good. They shouldn't even be an option to
use in Access.


Instead, use Min and Max, they will work in the manner you would
expect.

  #3  
Old May 26th, 2010, 10:58 PM posted to microsoft.public.access.queries
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default First and Last Problem

A table has no intrinsic order whatsoever. It is a set, and by definition
sets are unordered. The order in which the data is displayed can be changed,
but that amounts to exactly that, not a change in any underlying order. So
the concepts of 'first' and 'last' are really meaningless in relation to a
set. The FIRST and LAST operators only provide arbitrary values, and while
they can on occasion be useful in ensuring that all aggregated values in a
grouped query come from the same row, the row used is essentially an
arbitrary one.

You cannot obtain the result you want with a simple grouped query. If you
group the query by Name and Year and return the MIN(Location) you'll get the
location which sorts first alphabetically (A in this case) not the location
from the row with the lowest Stint value. Also you cannot return the Stint
value as if you did so and included it in the GROUP BY clause you'd get both
rows for 1999.

The way to get the result I assume you want is to use a subquery which
returns the lowest (MIN) Stint value per Name and Year and use this to
restrict the rows returned by the query:

SELECT T1.Name, T1.Year, T1.Stint, T1.Location
FROM [YourTable] AS T1
WHERE Stint =
(SELECT MIN(Stint)
FROM [YourTable] AS T2
WHERE T2.Name = T1.Name
AND T2.Year = T1.Year)
ORDER BY T1.Name, T1.Year;

The two instances of the table are here differentiated by the aliases T1 and
T2. This enables the subquery to be correlated with the outer query, so for
each row in the table it returns the lowest Stint value for the Name/Year in
question. Consequently the outer query only returns those rows where the
Stint value is the lowest for that name/year.

BTW I'd recommend against using Name or Year as column names; the first is
the name of a built in property in Access and the latter of a built in
function, so could cause confusion. Alawys use more specific terms like
Firstname, Lastname, CustomerName, ParticipationYear etc. I recall a post
some years ago where in every row of a report the name of the report itself
appeared rather than people's names because Name had been used as a column
name in the underlying table.

Ken Sheridan
Stafford, England

Dan wrote:
For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.


--
Message posted via http://www.accessmonster.com

  #4  
Old May 27th, 2010, 05:19 AM posted to microsoft.public.access.queries
david
external usenet poster
 
Posts: 398
Default First and Last Problem

First and Last work just well enough to be dangerous,
and are never safe.

There are two problems:
1) The default order is primary key order, then any new
records in the order you added them since the last time
you did a compact or repair . That works alright only as
you add records in primary key order. If you add records
out of order, or go back and add change old records,
your order is mixed up.
You can get around that by using a sorted query, but....

2) In a complex Sorted query, the Sort may not be done
in the correct place, and the First and Last selection may
happen using the wrong Sort order.

Access 2.x didn't have problem 1, so naive users didn't
have a problem with First and Last in Access 2.x

Problem 2 is a bug, and didn't emerge until later. When
it did emerge, MS didn't fix it: instead they amended
the help files to say that First and Last did not always
return the First or Last values.

Then MS fiddled with the help files, and made them
unusable, so you have to come here for help. Now MS
is closing these NewsGroups...

(david)

"Dan" wrote in message
...
For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.



  #5  
Old May 27th, 2010, 12:04 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default First and Last Problem

First and Last work as documented and by no mean imply Earliest, Latest, or
anything similar. Their main use is to get an aggregate value by returning
value from a (one) record of the group.

Assuming the fields and values:

f1 f2 f3
10 1 100
10 50 1
10 100 50


then the statement: SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1
returns one of the three record.

On the other hand, SELECT f1, MIN(f2), MIN(f3) FROM table GROUP BY f1
returns 10, 1, 1, which is NOT a 'record' in the original set,
and so does: SELECT f1, MAX(f2), MAX(f3) FROM table GROUP BY f1,
returning 10, 100, 100 also not a 'record' in the original set.
while SELECT f1, LAST(f2), LAST(f3) FROM table GROUP BY f1 would return a
record from the original set (which one is unpredictable, without more
information).


LAST would return (values from) a different record than FIRST if there is
more than one record in the group.



Vanderghast, Access MVP

"david" wrote in message
...
First and Last work just well enough to be dangerous,
and are never safe.

There are two problems:
1) The default order is primary key order, then any new
records in the order you added them since the last time
you did a compact or repair . That works alright only as
you add records in primary key order. If you add records
out of order, or go back and add change old records,
your order is mixed up.
You can get around that by using a sorted query, but....

2) In a complex Sorted query, the Sort may not be done
in the correct place, and the First and Last selection may
happen using the wrong Sort order.

Access 2.x didn't have problem 1, so naive users didn't
have a problem with First and Last in Access 2.x

Problem 2 is a bug, and didn't emerge until later. When
it did emerge, MS didn't fix it: instead they amended
the help files to say that First and Last did not always
return the First or Last values.

Then MS fiddled with the help files, and made them
unusable, so you have to come here for help. Now MS
is closing these NewsGroups...

(david)

"Dan" wrote in message
...
For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.




  #6  
Old May 27th, 2010, 12:18 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default First and Last Problem

How can you claim with authority that they are not good if you are not even
sure about how they should work in the first place?

Min and Max can replace First and Last if there is just one field aggregated
with First, or Last, but not if there is more than a single field so
aggregated. How could you replace FIRST in the following query, with MIN /
MAX:

SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1

where the goal of that statement is to get a (one) record, for each group
value.
Considering the data:

f1 f2 f3
10 1 50
10 1 50
10 50 1
10 100 50
10 50 100
10 50 1
10 50 100


(just for illustration, but someone should not think that this data
represent all possibilities).

And come with a simple, or not, solution, implying MIN (or MAX)... or change
your claim about FIRST/LAST "shouldn't even be an option to use".



Vanderghast, Access MVP


"ghetto_banjo" wrote in message
...
First and Last do NOT work the way they think you do. I believe they
try to see the first and last records written to disk or something,
but the results are NEVER good. They shouldn't even be an option to
use in Access.


Instead, use Min and Max, they will work in the manner you would
expect.


  #7  
Old May 27th, 2010, 12:56 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default First and Last Problem

vanderghast wrote:
How can you claim with authority that they are not good if you are
not even sure about how they should work in the first place?

Min and Max can replace First and Last if there is just one field
aggregated with First, or Last, but not if there is more than a
single field so aggregated. How could you replace FIRST in the
following query, with MIN / MAX:

SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1

where the goal of that statement is to get a (one) record, for each
group value.
Considering the data:

f1 f2 f3
10 1 50
10 1 50
10 50 1
10 100 50
10 50 100
10 50 1
10 50 100


(just for illustration, but someone should not think that this data
represent all possibilities).

And come with a simple, or not, solution, implying MIN (or MAX)... or
change your claim about FIRST/LAST "shouldn't even be an option to
use".


Well of course, in Access, given this dataset, we would have to advise using
FIRST, since there appears to be no field that can be used to provide the
sort order for this data, besides your whim when you set it up. I would also
have to add the proviso: don't be too shocked if FIRST does not return the
"correct" results someday. There is no guarantee that the database engine
will not choose to return the results in some other order someday.

If there was a field or fields that could be used to provide this particular
order, then I would offer some variation of Ken's advice.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


  #8  
Old May 27th, 2010, 01:21 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default First and Last Problem

It can be done using a variety of methods. Easiest to understand is using
nested queries. Assumption you use Stint to determine first and last.

Query 1: Get first (minimum) stint per name and year. Save as qFirstStint
SELECT [Name], [Year], Min(Stint) as FirstStint
FROM [Your Table]
GROUP BY [Name], [Year]

Query 2: Use query1 and the original table
SELECT [Your Table].[Name], [Your Table].[Year], [Your Table].Stint
, [Your Table].Location
FROM [Your Table] INNER JOIN qFirstStint
ON [Your Table].[Name] =qFirstStint.[Name]
AND [Your Table].[Year]=qFirstStint.[Year]
AND [Your Table].Stint = qFirstStint.FirstStint

Ken Sheridan's posted solution is very similar. It has the advantage of
returning records that can be updated. Its disadvantage is that with large
recordsets it may be considerably slower than the above solution.

Oh! The above can be done in a single query if your table and field names
follow the naming guidelines. The names should consist of at least one
letter, numbers, and the underscore character. No other characters allowed.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Dan wrote:
For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.

  #9  
Old May 27th, 2010, 01:39 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default First and Last Problem

Ken's solution is about returning fields of the record WHERE a minimum value
is found, as well as in http://www.mvps.org/access/queries/qry0020.htm
presenting 4 solutions.

My intervention is about the general claim that FIRST and LAST are totally
useless.


My intervention is not about how to return fields of the record where a
minimum value is found. In fact, at
http://www.mvps.org/access/queries/qry0020.htm, solution number 3 uses FIRST
in the perspective of my intervention: it is a VERY useful aggregate when we
want some values, from fields (more than one) where something occur an which
imply a GROUP BY syntax.


The two interventions, Ken and mine, are distinct.


Vanderghast, Access MVP



"Bob Barrows" wrote in message
...
vanderghast wrote:
How can you claim with authority that they are not good if you are
not even sure about how they should work in the first place?

Min and Max can replace First and Last if there is just one field
aggregated with First, or Last, but not if there is more than a
single field so aggregated. How could you replace FIRST in the
following query, with MIN / MAX:

SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1

where the goal of that statement is to get a (one) record, for each
group value.
Considering the data:

f1 f2 f3
10 1 50
10 1 50
10 50 1
10 100 50
10 50 100
10 50 1
10 50 100


(just for illustration, but someone should not think that this data
represent all possibilities).

And come with a simple, or not, solution, implying MIN (or MAX)... or
change your claim about FIRST/LAST "shouldn't even be an option to
use".


Well of course, in Access, given this dataset, we would have to advise
using FIRST, since there appears to be no field that can be used to
provide the sort order for this data, besides your whim when you set it
up. I would also have to add the proviso: don't be too shocked if FIRST
does not return the "correct" results someday. There is no guarantee that
the database engine will not choose to return the results in some other
order someday.

If there was a field or fields that could be used to provide this
particular order, then I would offer some variation of Ken's advice.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


  #10  
Old May 31st, 2010, 04:10 AM posted to microsoft.public.access.queries
david
external usenet poster
 
Posts: 398
Default First and Last Problem

while SELECT f1, LAST(f2), LAST(f3) FROM table GROUP BY f1 would return a
record from the original set (which one is unpredictable, without more
information)


Which one is unpredictable even with more information.

First and Last work as documented. That is, MS changed
the documentation for A97 to match the behaviour of
First and Last.

(david)


"vanderghast" vanderghast@com wrote in message
...
First and Last work as documented and by no mean imply Earliest, Latest,
or anything similar. Their main use is to get an aggregate value by
returning value from a (one) record of the group.

Assuming the fields and values:

f1 f2 f3
10 1 100
10 50 1
10 100 50


then the statement: SELECT f1, FIRST(f2), FIRST(f3) FROM table GROUP BY f1
returns one of the three record.

On the other hand, SELECT f1, MIN(f2), MIN(f3) FROM table GROUP BY f1
returns 10, 1, 1, which is NOT a 'record' in the original set,
and so does: SELECT f1, MAX(f2), MAX(f3) FROM table GROUP BY f1,
returning 10, 100, 100 also not a 'record' in the original set.
while SELECT f1, LAST(f2), LAST(f3) FROM table GROUP BY f1 would return a
record from the original set (which one is unpredictable, without more
information).


LAST would return (values from) a different record than FIRST if there is
more than one record in the group.



Vanderghast, Access MVP

"david" wrote in message
...
First and Last work just well enough to be dangerous,
and are never safe.

There are two problems:
1) The default order is primary key order, then any new
records in the order you added them since the last time
you did a compact or repair . That works alright only as
you add records in primary key order. If you add records
out of order, or go back and add change old records,
your order is mixed up.
You can get around that by using a sorted query, but....

2) In a complex Sorted query, the Sort may not be done
in the correct place, and the First and Last selection may
happen using the wrong Sort order.

Access 2.x didn't have problem 1, so naive users didn't
have a problem with First and Last in Access 2.x

Problem 2 is a bug, and didn't emerge until later. When
it did emerge, MS didn't fix it: instead they amended
the help files to say that First and Last did not always
return the First or Last values.

Then MS fiddled with the help files, and made them
unusable, so you have to come here for help. Now MS
is closing these NewsGroups...

(david)

"Dan" wrote in message
...
For some reason First and Last are giving the wrong data in a Totals
query. I assume that somehow the data is not ordered correctly in the
main table, but it does show correctly in the datasheet view. For
example if I group the sample database below by Name and Year, First
of Location should be B, unfortunately it shows up in the query as
A.

Name Year Stint Location
Ed 1999 1 B
Ed 1999 2 A
Ed 2000 1 A

Desired Result
Name Year Stint Location
Ed 1999 1 B (But shows up as A)
Ed 2000 1 A

Any ideas from someone who has seen this before? This is the first
time I have seen this--I created the master table under a make tables
query. Could it be that even though it comes out in the right order
in the datasheet view it is actually ordered differently? If so is
there a way to internally reorder a table?

--Dan L.






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:54 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.