Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased, or a down triangle if it decreased ... and stagger the markers for even greater grasp.
This lesson also covers how to handle non-American date formats, and optimize performance with a subquery.
If you like this video, please Like, Share, and Comment ~ thank you
1. Make a new query based on a table (MyData) with a date (TheDate) and a value (Price)
- add date and value fields to the grid
- sort in decending order by date
2. Add another copy of the table to the query
- Access will '_1' to the end of the name of the copy at the top of the fieldlist to make it unique.
- This table will represent the record for 'yesterday', or whenever the previous value was recorded.
- add date and value fields to the grid and give them aliases (for instance, PrevDate and PrevPrice) since field names have to be unique
3. Create a calculated field to show the difference
- for instance --> Diff: CCur( MyData.Price - MyData_1.Price )
- between a value in the reference record and the previous record
- Wrap with function to convert to currency to ensure the result is the correct data type
- the calculated field name (alias) is 'Diff' since it appears before the colon
4. Create a calculated field to show the Unicode symbol corresponding to a Circle or Triangle to graphically represent the difference
- for instance --> UpDown: IIf([Diff]=0, Space(4) & ChrW(9899), IIf([Diff]>0, Space(8) & ChrW(11205), ChrW(11206) ) )
- 4 spaces are added before Same to put it in the middle
- 8 spaces are added before Increase to put it in on the right
- IIf is Immediate 'if', evaluates a condition in 1st argument, and returns result of 2nd argument if True, or 3rd argument if False
- The Space functions creates a string of spaces
- UpDown is the calculated field name (alias)
5. Unicode characters
- ChrW(9899) is a circle, indicating no change
- ChrW(11205) is an upward-pointing triange, indicating an increase
- ChrW(11206) is an downward-pointing triange, indicating a decrease
6. To link the two copies of the same table, use criteria to find record for latest date before the previous date