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
- for instance --> DMax("TheDate", "MyData", "TheDate < #" & MyData.TheDate & "#")
7. Windows Region settings not American or ISO format
- criteria can be --> "TheDate < " & Format( [TheDate], sFormat ) where sFormat is:
"\#yyyy-mm-dd\#" for ISO format, or
"\#mm\/dd\/yyyy\#" for American format
8. To enhance performance, use a subquery instead of DMax for criteria under the previous date
- for instance --> (SELECT Max(a.TheDate) FROM MyData as a WHERE a.TheDate < MyData.TheDate )