Don't Use First Last
By Richard Rost
3 years ago
Don't Use First & Last Functions in Microsoft Access
In this Microsoft Access tutorial I'm going to show you why I never use the First or Last functions in aggregate queries or SQL. I always stick to the Max and Min functions. This also goes for DFirst and DLast.
Pre-Requisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, max v last, min v first, first function, last function, max function, min function, last vs max, first vs min, dfirst, dlast, first record, last record
Intro In this video, we talk about why you should avoid using the First and Last functions in Microsoft Access, including DFirst, DLast, and the First/Last aggregate query options. I explain how these functions can give unreliable results when you're trying to find the most recent or earliest data, and why using Max and Min is a better approach. You'll see practical examples that show the difference between these functions, learn how to build queries that return the actual latest record by date, and get tips for keeping your data accurate.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about why I never use the First and Last functions - either DFirst, DLast, or First and Last in an aggregate query or in my SQL. I just do not use them. They are unreliable and I am going to show you why.
If you use the Max function, it gives you the max of a field, for example Max OrderDate. If you use the Last function, it gives you the last record entered into the table, which might not be entered chronologically. Most people, when they say they want the last order or the last contact or the last poll result, whatever, are thinking chronologically - they want the most recent one.
If you enter your orders out of date, out of sequence, the last entered order might be from a date two months ago. For example, you enter something in after the fact. We will talk about that in this video.
Today's video is more of a public service announcement than actually teaching you something, but I get asked this question a lot. It came up in my forums just recently. I want to tell you: do not use the Last function, either in an aggregate query or in your SQL, or even DLast. There is a DLast function. You almost never want it.
The Last function actually reads the last record entered into a table, and it usually ignores any other sorting in my experience. What you generally want is Max, not Last. There is also a First and a Min function. You do not want to use First. Use Min if you care about chronological order.
If you want to save a date value in that record, like an order date for example, use the Max or Min of that, because if records get entered out of order, things will get messed up.
Let me show you an example. Let's create a table here. We will make it an order table: OrderID an AutoNumber, the OrderDate, and we will just put the CustomerName. Yes, I know it is not properly normalized and all that stuff. This is just for an example.
We will save this as MyOrderTable (OrderT) - primary key up. Let's put a couple sample orders in with the order date and the name. We will do 1/1 Kirk, 1/15 Picard, 2/1 Sulu, 2/10 Sisko, and we'll say 3/14 Seven.
This was obviously the last record entered. Usually, it goes by the AutoNumber of the primary key, but not always. Access has a funny way of keeping track of what orders were added in what sequence into the table, even without a primary key. That can sometimes change if you do not have a primary key, for example, and you do some action queries where you append some records, delete some records, append, update, etc. That can actually change the sequence of the records in the table.
You cannot rely on First and Last, but let's go through this example.
Let's save this and make an aggregate query in Query Design. Bring in the order table. I want to bring in CustomerName and I want to see the customer from the last order entered into the table. I will go to Aggregate, change Group By to Last (down here), First and Last. There is a reason why they are at the bottom. Save this as MyOrderLastQ.
If I run it, I get Seven, which is fine - that was indeed the last order entered. But now let's say that you realize you have some orders from the trade show a month ago that you forgot to put in the system. Enter 2/2 Janeway, and on 2/7, Chekov.
Now if I look at MyOrderLastQ, it shows Chekov. That is not the last order chronologically, it is the last order entered into the table. People can get confused; they think First and Last mean the first order and last order chronologically, but no.
If you want to see the most recent one, instead, bring in the OrderDate and change that to Max. That will then tell you the Max order date.
You cannot just bring in the rest of the information here because it is grouped by, and you are going to see everybody. You cannot do it that way.
Now, what we can do is, now that we have our OrderLastQ (which is actually the max), we are going to save this and change the name. Right-click, rename this to OrderMaxDateQ so we know it is the max date, not the last. I try not to use the word "last."
Now we can make another query. It is easier with two queries. Yes, you can do it in one step, but it is easier this way. Go to tables, bring in the Max Date Q and then bring in your table (OrderT). Join this up with OrderDate.
Now we can see the OrderDate, the actual date, and the CustomerName. If I run that, I get one order's worth of information.
If you are only tracking the order date and you have multiple orders on this date, then you will also want a secondary field, maybe the OrderID. You could use that if you want to. If that is the case, I would probably go with date and time in here, but you might not always enter that.
Save this as OrderMaxDate2Q. Let's say we did have two orders on the same date. Let's say Sulu's was also on 3/14.
Now, if I run my OrderMaxDateQ, 3/14 shows up there. If I run this query, I see two of them. If you only want to see one, you can make this query only return one value by changing "Return" here to 1, not 101. Then sort this by some other field. Since the dates are all the same, sort it by OrderID descending. That should be the last record entered on that date, and now you will get Seven, which is Order 5. The other one was Sulu, Order 3, so Seven is definitely the last one in that case.
It is a little bit more work to do it this way, but this way you are guaranteed to get the latest order chronologically, not the last one actually entered into the table.
That is why I do not like the Last function. In my 30 years of working with Access, almost 30, I have never needed the Last function, ever. If you do have a table - a log table or an order table, contact table, or customer table - and you care about what the last record entered into that table was, put a date in there. Even if it is just something as simple as "DateAdded." Make that a date field. That is basically a time stamp. For the default value, use "= Now()". You will get the current date and time to the second that this record was created. I put those in almost every table I make.
That is why you do not want to use the Last or First function. Stick with Max and Min, and they are good for any date type or any field type.
If you want to learn more about this stuff, I have some more videos for you to watch. I have a video on aggregate queries which teaches you how to do all that grouping, sums, max, min, and all that stuff. There is a video showing you how to do that Top X thing - top records, like top 1 record, top 100 records, top 10 percent of records.
There is a DMax function which works just like the Max I showed you in the query a minute ago. Go watch this if you want to learn about that. It is a close cousin to DLookup, one of the most popular functions.
If you want to learn more about this First/Last stuff, Philipp Stiefel over at CodeCabinet has a really good article. He goes into more technical and detailed topics than I do. If you want to read about this, go right ahead. He has a great website. I highly recommend it. I will put a link to it down below.
That is your Fast Tip for today. I hope you learned something and I will see you next time!Quiz Q1. Why does the instructor advise against using the Last function in Access queries?
A. Because it only works with text fields
B. Because it is unreliable and does not return records in chronological order
C. Because it is slower than other functions
D. Because it can only be used in forms
Q2. What does the Max function return when used on a date field in an aggregate query?
A. The date of the first record entered
B. The maximum (most recent) date in the field, chronologically
C. The last record entered into the table
D. The average date of all records
Q3. If you enter records out of chronological order, which function will still return the true most recent record?
A. Last
B. DLast
C. Max
D. First
Q4. How does Access usually determine which record is "last" when using the Last function?
A. By highest value in the primary key
B. By most recently entered OrderDate
C. By the sequence in which records were entered
D. By alphabetical order of CustomerName
Q5. When might the First and Last functions return different results than expected?
A. When all records are entered on the same day
B. When records are entered out of chronological sequence or after appending/deleting records
C. When only one record exists in the table
D. When using only numeric fields
Q6. What does the instructor recommend adding to every table to keep track of when records are entered?
A. An AutoNumber field
B. A DateAdded field with default value = Now()
C. A text field for record status
D. A currency field for transaction amount
Q7. Why do you need a second query to display additional details (like CustomerName) for the most recent order date?
A. Because aggregate queries cannot show fields not in the grouping
B. Because Access cannot handle date fields in queries
C. Because only SQL can accomplish this
D. Because you are limited to ten fields per query
Q8. What is the recommended function to use if you want the earliest (chronologically first) date value from a field?
A. Last
B. First
C. Max
D. Min
Q9. What happens if there are multiple records with the same maximum OrderDate?
A. The query will return just the first one alphabetically
B. The query can be set to return all of them or sorted to pick one based on another field
C. The query will return an error
D. The query will ignore all of them
Q10. Which function is described as a "close cousin" to DLookup in the video?
A. DFirst
B. DMax
C. DSum
D. DMin
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-A; 8-D; 9-B; 10-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on a very common question I get about Microsoft Access: why I never use the First and Last functions. This covers DFirst, DLast, or using First and Last in aggregate queries or SQL statements. I avoid them because they are simply not reliable, and I want to explain exactly why that is.
When you use the Max function, it returns the highest value in a field. For instance, Max on OrderDate gives you the most recent date. On the other hand, using Last does not give you the chronological last item; it gives you the last record entered into the table, and that may not be the record with the most recent date. This is a key distinction. Most people think about the "last order" as being the most recent one by date, but that's not how the Last function works.
If records get entered out of chronological order, perhaps because you are keying in orders from a past event, the last one you enter could have a much older date. So, if you want accuracy based on actual dates, Last will mislead you.
This video might feel more like a public service announcement than a regular lesson, but many students ask me about this topic. I want to be very clear: avoid using the Last function in aggregate queries, SQL, or the DLast function. In almost every scenario, you simply do not want to use it.
The Last function essentially picks the last record added to a table, and usually, it doesn't consider any ordering or sorting that you might expect. When you truly need the most recent or highest value for a particular field, you should use Max. The same goes for the first record: if you care about the earliest one, you should use Min, not First.
For instance, if you have an order date in your table and want to identify the most recent order, use the Max function on the OrderDate field. This is always more accurate, especially if your data isn't entered in perfect order.
Let's walk through an example. Suppose I create a simple order table with three fields: OrderID as an AutoNumber, OrderDate, and CustomerName. This isn't perfectly normalized, but it works for our demonstration.
After adding a few sample orders with various dates and names, it's easy to see how the Last function operates. It's tied to the sequence in which records are added to the table, which isn't necessarily the date order. Furthermore, this sequence can get muddled if you do things like appending or deleting records without a clear primary key.
Let's say I want to build an aggregate query to determine which customer's order is considered "last." Using the Last function in the query returns the customer tied to the most recently entered record, not the one with the latest date. If you add in some older orders after the fact, the results from Last will become even more confusing. The customer that appears isn't actually your most recent order, just the last one you keyed in.
Instead, if your true intention is to find the most recent order, build a query that uses Max on the OrderDate field. Then, to find details about the entire record for that date, it's best to create a secondary query joining this result back to your main table using the date as a link. This ensures you're looking at the latest order chronologically, regardless of input sequence.
If you have multiple orders on the same date, you might want to include another field, like OrderID, as a tie-breaker. In cases like this, you could filter your query to return only one result, typically sorting by OrderID in descending order.
This process might require a couple of steps—using one query to get the Max date, then another query to pull in the full record—but it is a more reliable approach than ever relying on the Last function. This way you always get the actual latest order based on the date, not entry order.
In all my years of working with Access, I have never found a legitimate need for the Last function. If you care about record entry sequence, I strongly recommend including a date field like DateAdded in your table. Set its default value to Now(), so every record is automatically stamped with its creation date and time.
Stick with Min and Max where chronological order actually matters. Avoid using First and Last because they work with entry sequence, not logical data order.
For those interested in learning more, I recommend checking out my videos on aggregate queries, which walk you through how to group and aggregate data using sums, Max, Min, and more. You might also find value in my Top X records video, which shows how to get the top 1, top 100, or top percentage of your data. There is also a DMax function, which works very much like Max in a query but from VBA or within calculated controls.
For a more technical perspective on First and Last, I suggest reading Philipp Stiefel's article on the subject at CodeCabinet. He covers these ideas in more depth and goes into the technical reasons why you should avoid using these functions.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Problems with using First and Last functions in Access
Difference between Last and Max in aggregate queries
How record entry order affects Last function results
Creating a sample order table in Access
Using the Last function in an aggregate query
Issues with chronological order when using Last
Using Max to get the most recent order date
Creating queries to retrieve data for the latest date
Joining a Max date query with the Order table
Handling multiple records on the same Max date
Sorting and returning only one record with the latest date
Using date and time fields to track record creation
Adding a DateAdded field with Now() as default value
Recommendation to use Max and Min instead of Last or First |
