 |
|
|
|
|
|
|
|
|
Part 2 | Database B (PowerQueries) contains 61 other power queries (59 main queries and 2 nested queries)
1. Have you ever imagined using a parameter containing multiple default values in a where clause or a group by clause or a having clause or even an order by clause? This special type of parameter accepts multiple values. 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 the four query examples demonstrating this technique fascinating.
2. Let's say you need to change all the manager titles in the contact title field of your Customers table in the Northwood 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.
3. 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 queries demonstrate a technique to accomplish this task without using a single line of code.
4. 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.
5. 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.
6. How do you create a spreadsheet type of list out of an Access table?
7. 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.
8. 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.
9. How about getting running sum within each group?
10. 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?
11. 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.
12. You may wonder what products are priced so high that they are 2+ times as expensive as average products within each category.
13. 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.
14. Do you want to find out what products are priced the same within each product category?
15. Do you need to count your products by groups of price range?
16. What customers are located in the same city?
17. Average price for all products from selected suppliers
18. Average unit price excluding top three and bottom three products
19. Double unit prices for top three selling products by category
20. How many orders each employee took from each country?
21. Largest order for each product in terms of quantity ordered
22. Products purchased by customers
23. Products excluding top three and bottom three
24. Top salesperson to each country
25. Three top orders ever placed for each product
26. Three top selling products by category in terms of quantity?
27. What is the biggest order by product in terms of quantity?
28.Who is the best salesperson?
29. Who ordered the most products?
30. Combine two tables to make a single table in a single step
31. Who never ordered three products: 1,7,10 (product IDs)?
32. Find average of distinct unit prices in a single step
33. How many items of each product were sold?
34. Select every other record
35. Who never ordered any product?
36. Who ordered more than once on the same day?
37. Get employee's age as of today
38. Product variance report
39. Quarterly order count by customer for 1997 orders
End of example 2.
111+ Power Access Queries/SQLs
$39.95 Payment
Options/Problems
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $70.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
See Also: Excel Add-ins Index & Excel Templates Index or, All Software
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft