Join Adam Wilbert for an in-depth discussion in this video Previewing the course, part of Access 2010: Queries in Depth.
Learning how to effectively leverage queries in Access is learning how to pull information out of large data sets, but in the end, queries are simply a middleman between raw data and your analysis. Throughout this course, we will be looking at a lot of little pieces that can go into your queries but the real power in Access comes when you combine flexible query with other objects such as forms and reports. Towards the end of this course, we will be doing just that by building a reporting system that leverages queries, forms, and reports and merges them into a single analysis tool. I wanted to preview that tool upfront, so we can have a better understanding of where this course is headed.
So, right now, I have got a form open that we will be building later in the course. I provided four dropdown menus, so that the end user can input some parameters that they're interested in reviewing a report for. I have got a parameter for Geographical Division. So for instance, I could say I am interested in finding information about our New England locations. And I have a dropdown menu for Year, so let's say I am interested in their 2009 reporting year. I can compare these two options with another location and year or the same location in a different year or a different location in the same year.
So, this is really flexible. Let's go ahead and choose our South Atlantic states for the same reporting year. Incidentally, each one of these drop- down menus is using a query to generate this list of options. Normally, the end user will press this Preview Report button to generate the report. So, let's go ahead and step through this manually. I am going to run the query instead. The query is going to look to the form and find the values that the end user selected. When I run this query, I'm only getting information about the year 2009 and then the division New England and South Atlantic.
From here, this data table will get fed into the report for the final formatting. Let's go back to the form and take a look at what that report looks like. I will go ahead press Preview Report and this is the final formatted report. If I zoom in here, we can see I have got my New England states for the year 2009 and I have got the information from them. If I scroll down at the bottom of the page, I have got my South Atlantic states. Same year, 2009, and I have got those states. Now, there is also a couple of other features on here. I have got Division Sales, which adds up the total sales for all the states in that division, and I have got the average for the division.
So, each of these states, we have the average value. Each of these boxes uses query code that we will be looking at in the course. Now, at the bottom of the report, we also have a line that shows you the date that the report was run. This also uses a function that we will be looking at in the course to give you the current date. So, between the end user entering information on the form here and the report popping up on the screen here, there are several queries running in the background, performing all the grunt work of collecting relevant data and serving it to the appropriate places. So, that's a quick peek at what we are going with this course.
So, let's start digging into the details.
- Naming conventions and best practices
- Working with joins
- Using comparison operators
- Defining criteria for select queries
- Creating parameter queries
- Creating calculated fields
- Working with dates and times
- Using the Expression Builder
- Creating conditional statements
- Making, deleting and appending records
- Building reports