|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Calculations on Aggregate Results By Richard Rost 
6 years ago
Calculations on Aggregate Query Results In this video, I'll show you an easy trick to make complex query calculations simpler by using multiple "nested" queries. Michael from the UK (Expert Student since 2014) asks: I'm having trouble with calculating the number of years between two dates. In my aggregate query I have a field alias FirstDate set Min and second field alias LastDate. Both are from a field named PaymentDate. I've watched your tutorials and have tried all ways but have been unable to get it to work. It keeps rejecting the field name. Can you help? MembersI'll show you how to do the same thing, but with only one query. It's a little more complicated, but if you want to cut down on the number of queries in your database, it's a great solution. Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! LinksAggregate Queries: http://599cd.com/aggregate
IntroIn this video, we talk about how to perform calculations on the results of an aggregate query in Microsoft Access. We'll look at a common problem where you want to calculate the difference between two fields, such as the earliest and latest dates returned by aggregate functions like Min and Max. I'll show you why calculations involving those field aliases don't work inside the same query, and I'll demonstrate a solution using multiple queries to get the results you need.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, we're going to talk about performing calculations on the result of an aggregate query.First, if you don't know what an aggregate query is, look in the description below the video. I'll post a link to my other video talking about aggregate queries. It's where you can do sums, totals, max, min, average, and so on, of a set of data. That's called an aggregate query. Aggregate queries can be very difficult to put other calculations inside of based on the results of those aggregate queries. So let's talk about that example in this video. Michael from the UK, one of my expert students since 2014, writes: I'm having trouble with calculating the number of years between two dates. In my aggregate query, I have a field alias first date set to min, so the min of first date, in other words, the oldest of the dates, and a second field alias last date. That's probably set to max. Both are from a field named payment date. So we want to see what the first payment date was and the last payment date was and calculate the years between those two dates. I get it. I've watched your tutorials and have tried always, but have been unable to get it to work. It keeps rejecting the field name. Can you help? Michael, I know exactly what's happening. The problem is that your query doesn't understand what first date and last date are when you're trying to do the calculation on them. Let me show you. All right, here's my sample database template. Let me open this up. I've got customers. Customers have orders. Right now, I only have order ID, customer ID, and order amount. Let's throw order date in that table. OrderT, design view, right here. I'll put order date. You're using payment date. I'll use order date. It's the same thing. All right, this will be a date/time value. Save that. Let's put some data in it. All right, order date. We'll just put in here one one, one fifteen, one thirty. I put one five, that's fine. It doesn't matter. Two one, two five, three one, three fifteen, four one. Mix it up a little bit. One two. Two one. So we got a bunch of dates in there. Now, for each customer, here's customer one, customer five, and so on. I want to see their first date and their last date. So let's go create an aggregate query. Create, query design. Bring in the order table. Close that. I want to see, let's bring in the customer ID, the order date. Now if I just turn it into a total query now, an aggregate query right now, this is what I got. It groups by each of these. That's not what I want. I don't want to group by the order date. I want to see, let's say, the first order date. So we'll go to min. There we go. That's the first order for each customer. Customer one, two, three, four. See that? It's grouped by customer, and I'm getting the min of order date. Now, I don't want to call it min of order date, because that's what you see in the table here, min of order date. So we're going to alias that, like you did. We're going to call this first date. Let's call it first order. That's how you do that--the first order in my results. Now we'll do the same thing again. Bring in order date a second time. We'll set this one to max. And then I'll come in here and go last order. Now I'll get the last order date. I'm going to save this real quick. Let's just call it orderQ. That's fine. Run that. And there we go. There's the first order and the last order. Now you want to figure out the difference between these dates. You want to do years. I'll just do days. You can use the date diff function if you want to. I'll put a link in the description below the video for the date diff function. But remember, in Access, a unit of one is a full day. So I just want the number of days between these, since I didn't use very big dates here. So I'll come right over here and say days diff is last order minus first order. And now I'll run it. And this is what's happening to you. It says last order. It has no idea what last order is. Why? Because it hasn't evaluated this yet. This doesn't get figured out until the query is done running. It doesn't know what last order is because it's still got to calculate it. Hit OK now. It's going to give you first order. It doesn't know what those fields are. It works. You still see values in there. That's what you want, but it can't evaluate that at first. So there are two ways to fix this. There's a simple solution, which I'll show you right now in this video. Then there's a little more complicated solution that takes a little more work. I'll show you that in the members extended edition. Let's go in here. Here's the way you fix this. Take this out of this query. Delete. Save your orderQ. Now make a second query. Create query design. Bring in your orderQ. Now this query is going to run first. Once this is done, now you've got all three of these values in the query itself. And you can come right over here and say, days diff is going to be last order minus first order. Now when I run the query, you get it without any parameter prompts. Because first order and last order are evaluated by the first query. When that is done, you can now pull those values into this query. And I can save this as order2Q. You've got to have orderQ for order2Q to run. If you want to use date diff, you can put date diff in here as well. You could say the years diff. That would be date diff, yy, last order, first order, like that. I'm going to get all zeroes. Yeah, because these aren't very big dates. See, I can't change these. Now this is not an updateable record set. That's one of the things that you have to deal with. It comes from the other query. If I come back over to the table and make a change, let's go to the order table. Let's change this to 1/1/1990. And we'll change customer 5, this to 1/1/1980. Now if I run order2Q, you can see there's your differences. Oh, I got them backwards. Same thing with date diff; I always get these backwards. This should be first order. So if you do the same thing, don't feel bad--first order and last order. I see the point now where I just absolutely value this every time I run it. So that's the simple solution: just make two queries. Now, if you don't want a million queries in your database taking up all kinds of space, I can show you how to do it with one query. It's a lot more work. I'll do it for the members in the extended cut. Join the channel and you'll have access to that one. If not, this is a working solution for you. How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the join button, you'll see a list of all the different perks that are available: Silver, Gold, Platinum, and Diamond. But don't worry--these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making them, and they'll always be free. If you enjoyed this video, please give me a like and share. Click on the subscribe button to subscribe to my channel and be notified of any new releases. Check for additional resources down below the video. Click the show more button. You'll see a list of other links to other videos, downloads, resources, lessons, and lots more. If you have not yet tried my free Access Level 1 course, it's three hours long. You can find it on my website or on my YouTube channel. And if you like Level 1, Level 2 is just $1. And that's free for my members. Want to have your question answered in a video just like this one? Visit my TechHelp page, and you can post your question there. Also, be sure to stop by my Access forum on my website, and also look for me on Facebook, Twitter, and of course, YouTube. Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me, and I'll see you next time. QuizQ1. What is an aggregate query mainly used for in Microsoft Access?A. To perform calculations such as sum, average, min, and max on a set of data B. To filter records by specific criteria C. To create user forms D. To design table relationships Q2. What is the main issue Michael from the UK encountered when trying to calculate the difference between two dates in an aggregate query? A. Incorrect data types in his tables B. The query did not recognize his field aliases (first date and last date) when calculating C. Syntax errors in his SQL statement D. Lack of a primary key in his table Q3. In the video example, what table field was used to replicate Michael's "payment date" for demonstration? A. Invoice date B. Purchase date C. Order date D. Ship date Q4. When creating an aggregate query to get the first and last order dates for each customer, which functions should be used respectively on the "order date" field? A. Sum and Count B. Max and Min C. Min and Max D. Avg and Sum Q5. Why does Access prompt for a parameter when trying to calculate the difference between "last order" and "first order" within the same aggregate query? A. The fields need to be indexed first B. The calculation is not supported in aggregate queries C. The aliases have not yet been evaluated at that query stage D. The calculation needs to be placed in a macro Q6. What is the recommended simple solution to perform calculations on the results of an aggregate query? A. Use VBA code instead of queries B. Split it into two queries: a first query for aggregation and a second for calculation C. Only use one query with complex expressions D. Perform calculations in the table directly Q7. When is it acceptable to use the DateDiff function in this scenario? A. When comparing text fields B. Only in reports, not queries C. In the second query, after the aggregate fields are available D. Before the aggregate query is created Q8. What is a potential downside to using the two-query solution mentioned in the video? A. Queries cannot be saved in Access B. The results will not update automatically C. It may create many queries in your database D. It requires programming knowledge Q9. What does Richard Rost mention about the ability to edit records in the final calculated query? A. The records are fully updateable B. Only primary key fields can be edited C. The recordset is not updateable due to the underlying aggregate query D. All fields can be edited if you use SQL view Q10. For users who want to see a solution using only one query instead of two, what does Richard suggest? A. It involves VBA programming only B. It is not possible in Access C. Watch the extended edition available for channel members D. Just ignore the calculation altogether Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-B; 7-C; 8-C; 9-C; 10-C 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. SummaryToday's video from Access Learning Zone covers how to perform calculations on the results of an aggregate query in Microsoft Access.To begin, an aggregate query is a type of query where you use functions like Sum, Min, Max, and Average to analyze a group of records in your database. For instance, you might want to know the earliest and latest dates for a particular field, such as payments, for each customer. One of my students wrote in with a common issue. He was trying to calculate the number of years between two dates in his aggregate query. Specifically, he created aggregate fields for the minimum and maximum dates from a collection of payment dates, calling them 'first date' and 'last date.' His goal was to find out how many years had passed between the first and last payment for each customer. However, Access was not recognizing the names of these new fields when he tried to perform the calculation within the same query. This is a tricky problem because Access does not process those calculated field names until after the query runs, so you cannot reference them directly at that stage. To illustrate this, I set up a sample database with customers and orders. I added an Order Date field to the orders table and entered several different dates for various customers. The objective was to produce a query that shows the first and last order dates for each customer, then calculate the difference between those dates. First, I created an aggregate query that groups data by customer and uses the Min and Max functions to get the first and last order dates (renamed as First Order and Last Order). At this stage, the query successfully displays the earliest and latest order dates for each customer. However, if you try to add a calculated field in this same query that subtracts First Order from Last Order (for example, to count the days between them), Access returns a prompt for a parameter. This happens because the query engine does not yet 'know' what First Order and Last Order are when processing your calculation--those fields have not been created yet within the context of the query. To solve this, the best solution is to use two separate queries. In the first query, you get your First Order and Last Order fields using the aggregate functions. Then, you create a second query that pulls in the results of the first query. In the second query, you can now safely perform calculations on First Order and Last Order because those values already exist by the time this query runs. For example, you can subtract the two dates to find the difference in days, or use the DateDiff function to find the difference in years or any other time unit. If you'd like to clean up your database and do this calculation using only one query, there is a more advanced way to make it work, but it requires additional steps. I cover that more complex technique for members in the Extended Cut of this lesson. To summarize, if you want to perform calculations on fields that are the result of an aggregate operation, use two queries: one to create the aggregates, and a second to work with those results. For more details, and to see a complete video tutorial with step-by-step instructions on everything discussed here, visit my website at the link below. Live long and prosper, my friends. Topic ListCreating an aggregate query in AccessUsing MIN and MAX to find first and last dates Aliasing fields in a query for readability Calculating date differences after aggregation Using DateDiff function in Access queries Resolving parameter prompt errors with calculated fields Building a second query to use results from an aggregate query Understanding updateable vs non-updateable recordsets Fixing field order in date difference calculations |
||||
|
| |||
| Keywords: TechHelp Access aggregate query result calculations PermaLink Calculations on Aggregate Results in Microsoft Access |
