<

Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

display Triangles! and Circles! in a Microsoft Access Query -- Get Previous Record too

Posted on
6,717 Points
217 Views
Last Modified:
Experience Level: Intermediate
11:34
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
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

Video Steps

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 )
0
Comment
2 Comments
 

Expert Comment

by:Andy Brown
Nice work Crystal - thank you for sharing.
1
 
LVL 22
thank you, Andy

Unicode:

Note: Some fonts have more, and better, Unicode representations than others. For Windows standard built-in fonts, Arial Unicode MS and Lucida Sans Unicode have fair coverage.  Common fonts such as Arial, times New Roman, and Calibri can be okay too.

If you cannot show the Unicode characters used to demonstrate, try these instead:

filled circle  --> 9679

down-pointing triangle --> 9660

up-pointing triangle --> 9650
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Join & Write a Comment

New style of hardware planning for Microsoft Exchange server.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month