Modify a Query in Microsoft Access 2013
OK so we just created a query to return all customers in our database who have ordered a product. Now we'll see if we can tweak the query a little bit here and there to see what other results we can return.
The marketing department wants to see who, if anyone, ordered their most expensive space ship. So, let's return a list of all customers who purchased the most expensive space ship - the "Venus Carrera ET".
- From Design view, in the bottom pane, enter "Venus Carrera ET" (double quotes included) into the Criteria field of the ProductName field:
- Click the Run button in the Ribbon to view the results of the query. Alternatively, you can just click the Datasheet View icon. And here's the result:
- Once you're satisfied with the results, return to Design view and remove your modification (i.e. remove "Venus Carrera ET" from the Criteria field).
You could also modify your query so that it returns customers who purchased a product worth over a certain price.
So let's construct a query that returns all customers who purchased any product with a price over $80,000.
- From Design view, in the bottom pane, add (>80000) into the Criteria field of the Price field:
- Click the Run button in the Ribbon to view the results of the query. Alternatively, you can just click the Datasheet View icon. And here are the results:
Price Using Totals
The previous query is all good and well, but there's one (potential) problem with it. While it certainly allows us to see all customers purchased a product over $80,000, it doesn't really paint the full picture. It doesn't display those customers who may have purchased more than one product, each of which is less than $80,000, but the total of which is more than $80,000.
Here's how we can capture that one.
- From Design view, click the Totals button in the Ribbon:
- You will now see a Total row in the bottom pane. Each field will have Group By. In the Price field, click Group By and select Sum.
- Click in the next row down (i.e. the Sort field) and select Descending. Keep the >80000 criteria in the Price field.
- You will also need to delete the ProductName column by selecting it and clicking Delete Columns (next to the Totals button on the Ribbon). It should now look like this:
- Run the query. And here are our big spenders:
If you get an error that reads Cannot group on fields selected with '*'., try setting Output All Fields to No in the Property Sheet.
You could spend a lot of time tweaking this one simple query. For example, here's some ideas:
- You could replace >80000 with >=80000
- You could remove the >80000 altogether
- You could sort by CustomerId instead
- You could use >50000 And <80000 (or any other price range)
- And much more...
Once you're done, you can either save your new query or keep the original query. For the purposes of our macro lesson, you might like to keep the original query. However, you can always create and save multiple queries if you're so inclined.
So as you can see, queries can be very powerful. Imagine what you could do if you had more tables (and data) in your database.
Just make sure your query names are descriptive of what the query is doing. Otherwise you might find it difficult to distinguish between queries once you have a good number of queries within your database.
So far, all our queries have been constructed using Design view (and then checking them in Datasheet view). There is actually another view that we can use to create and modify queries. That view is SQL View.
There's also a tutorial for Access 2016.