Dalia strikes again with this handy reference guide for the Microsoft Access portion of K201. This is not intended as a complete introduction to using Access. It assumes that you have the basics down, and tries to cover the specific topics where K201 students tend to get stuck the most often.
| Quarter | Months | Expression |
|---|---|---|
| Quarter 1 | January, February, March | Between #1/1/2016# and #3/31/2016# |
| Quarter 2 | April, May, June | Between #4/1/2016# and #6/30/2016# |
| Quarter 3 | July, August, September | Between #7/1/2016# and #9/30/2016# |
| Quarter 4 | October, November, December | Between #10/1/2016# and #12/31/2016# |
| Date | Expression |
|---|---|
| Today's date | Date() |
| Yesterday | Date()-1 |
| Tomorrow | Date()+1 |
| 3 months from now | Date()+90 |
I do the knuckle trick!
Whenever you see multiple rows of potentially the same data you need to turn on your totals row.
Turn unnecessary information into "Where." Unnecessary information could be defined as information that the problem does not ask to see.
Example: Show the customer's first name and last name for all orders where the order date is in the month of October 2005. What if the customer made multiple orders in October 2005?
This problem can be read as "show the customer's first and last name where the date is between October 1st and October 31st of 2005." For this example we would turn on the totals row and group by customer first and last name (because they want it to be shown) and where the order date.
In Queries: Just assign simple numbers to the mental math and correlate that to your formula.
Take the following example from the knowledge checks:
Example: Find all customers who were overcharged.
Think about this from a real life application. If I was overcharged, that means that I paid more for an item than I was supposed to. So if I go into a store and I grab something off the shelf and go up to pay and the cashier says that the item costs $10.50 but the actual price is $10.00, how much was I overcharged?
Well,
\($10.50 - $10.00 = $0.50\).
What if I bought 10 items at $0.50 extra?
Well,
\(10 * $0.50 = $5.00\).
In total, I was charged $5.00 over.
Now the final step, take the math we just did and assign it to fields:
| Value | Description | Field |
|---|---|---|
| $10.50 | Price I was quoted | [Price Quoted] |
| $10.00 | Actual selling price | [Our Selling Price] |
| 10 | The amount I bought | [Quantity] |
So overall our new field shall read:
Overcharge:
([Price Quoted]-[Our Selling Price])*[Quantity]
If you follow these steps all calculated fields will be easy!
In forms:
You have to put an equal sign before any calculated fields. So if we were doing the same problem in a form our answer would look like:
Overcharge=([Price Quoted]-[Our Selling Price])*[Quantity]The revenue formula is and always will be:
\(Price * Quantity\)
All you have to do is assign that formula to the appropriate fields. Using the above example, we can write our revenue formula to be: [Price Quoted]*[Quantity]
If your directions ask for the Total Revenue, you would turn on your Totals Row and sum your Revenue field.
The sales volume formula is:
\(Quantity\)
All you have to do is assign that formula to the appropriate fields. Using the above example, we can write our sales volume formula to be: [Quantity]
If your directions ask for the Total Sales Volume, you would turn on your Totals Row and sum your Sales Volume field.
Example: Add up the total number of Orders by the sales reps for dates in the third quarter of 2008.
We (generally speaking for K201 terms) will always count the primary key. A primary key is a unique identifier - your primary key might be your social security number. For this example our primary key is Order ID.
For our query we will turn on the Totals Row and count the Order ID field. Then we have to reference the third quarter of 2008.
Remember: we cannot have a criteria with a count. We wouldn't put a date criteria under an Order ID field, anyway. We would bring down the Order Date field and place the criteria of Between #7/1/2008# and #9/30/2008#. If you were to leave the Totals Row of the Order Date field as a group by you will see that your data has counted the amount of orders for each date within that quarter. Instead let's change the Totals Row to read where since the Order Date is "unnecessary information." Now you will see that our query contains only 1 row and 1 column with the total number of orders by sales reps during the third quarter in 2008.
Remember, K201 is just a big game of synonyms, especially when it comes to field properties. Here is a chart of keywords to help you differentiate between using an Input Mask or a Format.
| Input Mask | Format |
|---|---|
| Template | Display |
| Data-Entry | Show |
| Mask | Appear |
Example:
If the instructions asked you to make the Hire Date field appear as a "Medium Date", but make it so entry is done in a "Short Date" template. You would change the format to "Medium Date" and apply a "Short Date" input mask.
When editing properties, if you are ever asked to make a certain text or date appear "automatically" or "initially" that is the Default Value property. Again, K201 is a big game of synonyms.
All Concatenation (combining of fields) is done using the ampersand symbol: &
Example: Write a formula to show the customer name as the last name followed by a comma and the first name (e.g. "Willis, Bruce").
[Last Name]& "," &[First Name]
Example: Write a formula to show the customer name as the first name followed by the last name (e.g. "Bruce Willis").
[First Name]& " " &[Last Name]
Example: Display in the Notes Field, the pre-existing Notes and Offer Membership!
[Notes]& " Offer Membership!"
The & is used to connect between the fields and the text. The " " is used to signify that there is a text space - you do not want your first name to run into your last name.
When creating a drop-down there are a few properties that aren't in the wizards:
There are obviously more but these are the two most common. They are found in the lookup tab next to the general tab.
IIF(logical_test, if_true, if_false)
Logical Test: What differentiates between the two outcomes
Example: When a customer buys a Sony product, they are a "preferred customer." Add this to the customer's notes.
Logical Test: Brand is Sony
IIF([Brand]="Sony","Preferred Customer", "")
If a query asks for a percent of some sorts, it's probably a query on query. Part/Whole Queries are all done the same way. First you find the "whole" or the denominator. It's generally the total of whatever you're looking for. Secondly, you run the numerator query or the "part" this will generally be categories, regions, employees, or customers of some sorts. Finally, you create the last query and divide the part by the whole.
Example: Show the percent of sales revenue that each employee is responsible for.
Calculate Total Sales Revenue for the company as a whole regardless of what employee made the sale.
Recall,
Total Sales Revenue: [Price Quoted]*[Quantity]
Turn on the totals row and sum it.
Copy and Paste Query 1.
Add the EmployeeID, First Name, and Last Name (or whatever the problem asks for).
Then run the query and see that the sales revenue is now broken up by the employee. Change the name of the calculated field to Employee Revenue.
Bring down the numerator and denominator queries.
Add the fields EmployeeID, First Name, and Last Name and in a new calculated field divide the two revenues.
% of Sales Revenue: [Employee Revenue]/[Total Sales Revenue]
Format as a percent with 2 decimal places.