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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|