How to Query the Last Row in a Series in Microsoft Access

I'm your host Sean Mackenzie thank you for joining me once again my channel on data analytics and data engineering this week we're going to return to our Microsoft Access playlist and we're going to talk about how to get the last row for a particular variable or column in your table and a lot of people don't know how to get the last row good examples are how to get the last status if it's a table that's just full of statuses and there's many statuses say for one person and you just want the last one with all of the data or it could be you know orders or anything like that it's a very useful query to to be able to know how to do and as a data analyst you've got to know how to do this one okay let's get to it okay so I went ahead and created a file here called access data doc DB and I created a table in it called visit and what we're going to look at this time is some visitors that have a visit date and mood and clothing in our table and you can see that there's multiple row entries for each person who makes visits so what I'm gonna do here first is I'm gonna just create a new query using query design and I chose the visit table for my query you can also click the close button on the pop-up that comes and you can just drag the table on there but what we're gonna do is we're gonna grab two columns first of all our visitor and our visit date and then we're gonna click the summary totals button at the top and we're going to change our total to max for the visit date and what that's going to do is it's going to give us the highest date or the latest date for each visitor and we're going to make that into one query so you can see Harry see his last date there you can see Sarah's last date Melissa lives in Jim and that's gonna be a nice little query that we're going to save and we're going to save that on its own so that we can use it in another query to get the last records for each person so we saved that query as visit underscore last and we'll use that query in a new query so we'll start this again this time I'll just click close on the pop-up and I'll just drag on the query that I want and I'll also drag on the visit table and then what we're going to do is we're going to do a join between the two and we're going to use two fields the visitor and the visit date so that it'll limit all of the selections to everything that matches on the visitor and visit date and we have specified the last visit date in our visit last query so we'll call our new query visit last row so that we're getting all of the data items in the row you could have a hundred fields in there and as you can see when we open this one we can see that we have each of the people that are making visits and we can see their last visit record which has all of the data for their last visit so what if we wanted to look at a different view of using the last last row say we wanted to look at the last mood that happened when was each mood used the last time or some other variable and we'll do it the same way we'll grab the mood field will add the visit date will click the totals tab like we did before and we'll set it to max so that we have the highest date again and we'll save that as mood underscore last and as you can see here we can see the last time each of the moods was used when we run that query and just the same as the the first query will do the same thing we'll grab our mood last and we'll grab the visit table just like we did and we'll join it this time on the mood and the visit date so that'll give us all the full record detail if we click double click the star there that'll give us the full query detail all of the fields for the last row for each time that the mood was used and we can go ahead and open that query and you can see that this query has a different number of rows than the previous one because there's one row for each of the moods and this includes good angry sad happy nervous and depressed and the associated date that the last time for the last time it was observed and you can see that the users might have more than one entry in this query and that is how you get the last row for any series in Access well I hope you enjoyed our video today on getting the last status of any particular column in your table using our nested query in Microsoft Access if you like what you saw today please give it a thumbs up and subscribe to the channel if you haven't subscribed yet and please click the bellow when you see the bell so that you'll be notified of any new material that I put up on the channel if you have any questions or comments or if you'd like more examples or anything like that feel free to comment in the comment section below and I'll be happy to to respond I hope you're all having a great week and I'll catch you later [Music] [Applause] [Music]

Channel: Sean MacKenzie Data Engineering

Views: 2,224

Keywords: How to Query the Last Row in a Series in Microsoft Access, MS Access Hacks, Microsoft Access Tips, microsoft access database, Data manipulation in MS Access, Sean Mackenzie

Id: 5cnwHyn4dnI

Length: 6min 52sec (412 seconds)

Published: Thu Jul 09 2020