Home

Products
>SQL Wizard | Access Add-in
>Data Change Analyzer| Access
Add-in
>Consolidate & Update Wizard | Access Add-in
>Median Finder | Access Add-in
>111+ Power Access Queries/SQLs
>Duplicates Finder | Access Add-in
>Duplicates Filter | Excel Add-in
>Excel Tips & Tricks
>Macros Finder | Excel Add-in

111+ Power Access Query/SQL Examples

[Go To Part 2]

Part 1 | Database A (PowerQueries.mdb) contains 91 power queries

1. Have you ever imagined using a parameter containing multiple default values in a where clause or a having clause or even an order by clause? This special type of parameter can handle multiple parameter values at a time. You can use the default values as provided or enter your own values. You also can change the default values to meet your changing needs. You will find our five query examples demonstrating this technique fascinating.

This example demonstrates how you can use this one of a kind parameter in the HAVING clause of a GROUP BY query. We provide three other examples showing how you can use this parameter in a WHERE or an ORDER BY clause.

_______________

2. Create a calendar using an Access query?
This may sound like a tall order to fill.  However, with several built-in Access functions and a Union query structure, you can pull off what seems like an impossible solution. This parameterized Union query takes two parameters: a month number and a year.  If you provide these two parameters, you can create any monthly calendar. Discover our fascinating
tricks and techniques.

_______________

3. You may find it hard to believe that you can create a calendar for a whole year with an Access query. Even with VBA, this could be a tall order. However, we pulled off what seems to be an impossible task without using a single line of VBA or Macros by working a Union query to the maximum. With this query you can create a calendar for an entire year and display it in a single datasheet view.

Parameter: year(yyyy)

_______________

4. Make a calendar for multiple years

You even can create a calendar for whatever years you want in one shot. Just enter those years in the table provided, which serves as a multiple value parameter.

_______________

5. Let's say you need to change all the manager titles in the contact title field of your Customers table in the Northwind database. There are three different titles containing "Manager": Accounting Manager, Marketing Manager, Sales Manager. You want to change "Manager" into "Executive" for all three titles. To accomplish this, normally you need to run an update query three times. However, you can combine all three queries into one parameterized action query and achieve your goal in one shot. An example query demonstrates how you can update partial field values in one step.

As you can see from this example, the three contact titles containing the word “Manager” now have “Executive” in its place.

_______________

6. Do you need to remove all numeric or non-numeric characters from string values in a column? For example, you have a part number in your column: A1B2C345. You want to end up with ABC by removing all the numeric characters. Or you want 12345 by removing all the non-numeric characters. This can normally be done using a user-defined function (VBA code). Two example update queries demonstrate a technique to accomplish this task without using a single line of code.

_______________

7. You want to get a list of your products whose unit price is equal to or above the average price with the average price shown at the end of the list labeled "Average". You also want the list sorted in an ascending order by product name. This task is easier said than done. But if you use a little ingenuity, you can come up with an amazingly simple solution. An example query provided gets you exactly what you want in a single step i.e., no nested query.

Notice the average price at the bottom of the list.

_______________

8. How do you rank items within each category? For example, you may want
to get top three most expensive products by category.

This query uses no nested queries. Look at the two categories: Produce, Seafood. Notice how the query ranks the same prices within the same category.

_______________

9. How do you create a spreadsheet type of list out of an Access table?

_______________

10. Do you want to make a list of order numbers by customer with each row showing all the order numbers for each customer? We used a derived table and the IIF function in building this unique query.

[click on image to enlarge]

______________

11. Do you know that you can put a certain group of records at the top or bottom of your query results with the rest of them sorted separately?
For example, you may want to show your fast selling products at the top of the list. This custom sort technique involves sorting on a single or multiple expressions. You even can sort your whole list in accordance with a custom list. A dozen query examples demonstrate this technique:

1) Show 1997 orders at top
2) Show average price at bottom
3) Show customers in London at top
4) Show customers who have same address and shipping address
5) Show managers first
6) Show marketing managers & owners at top
7) Show products with IDs 50-60 at top
8) Show selected companies at top
9) Sort on an expression based on the Switch function
10) Sort on custom list
11) Sort on multiple expressions

______________

12. Do you want to get a list of your products with category average price next to unit price for each product?

The database contains two examples taking two different approaches to solve this problem.

______________

13. How about getting running sum within each group?

_______________

14. Do you want to find out how many products are priced below or above the average price and what the respective average price for these two groups is?

_______________

15. Do you want to find out how often your customers order?

Notice that at the bottom of the list there is a row total across the columns.

_______________

16. You may wonder what products are priced so high that they are 2+ times as expensive as average products within each category.

_______________

17. You sometimes want to assign sequential numbers to the results of your query.

The database contains two examples taking two different approaches to solve this problem.

_______________

18. Do you want to find out what products are priced the same within each product category?

_______________

19. Do you need to count your products by groups of price range?

_______________

20. What customers are located in the same city?

_______________

21. Have you ever tried a SQL like this: SELECT COUNT (DISTINCT ContactTitle) FROM Customers?  You are out of luck. MS Access does not support this type of query. However, there is a workaround.  With a little bit of ingenuity you can come up with a single query.

_______________

22. Create random multi-year dates (parameterized)

This select query comes in handy when you are in a hurry to create 100 test dates. This query accepts three parameters (years). You can add or delete your parameters as you wish. If you need more or less than 100 records, then you can modify the underlying single column table by adding or deleting records accordingly.

_______________

23. Create random single-year dates (parameterized)

This make-table query comes in handy when you are in a hurry to create 100 test dates. This query takes one parameter.  If you need more or less than 100 records, then modify the underlying single column table by adding or deleting records accordingly.

_______________

24. Group customers by total sales and count them by group.

This query employs a derived table (based on Orders table and Order Details table) and the IIF function. This powerful combination enables you to pull off what would otherwise be impossible to accomplish in a single step.

_______________

26. Transpose columns to rows.

You may want to convert a table like this:

to a table like this:

_______________

27. Transpose rows to columns.

_______________

28. Average unit price excluding top three and bottom three products

This query uses a derived table, a left join, and Union All to get the average
price of the products from the Products table without the three lowest-priced and the three highest-priced products. You need to be very creative to construct a query like this in a single step!

(includes both Part 1 and Part 2)

[Go To Part 2]


We offer an unconditional 30-day Money Back Gurantee and free e-mail support! Your order is secured via RegNow SSL Secure Server (see RegNow Security/Privacy statement). You will be able to download the product immediately after ordering!