Adding Virtual Columns to an RM4 Table #msaccess #todolist #colorcoding

(This is for the MS Access section of the Wiki. I thought I was putting it there, but it didn’t seem to go there. I guess I’m still figuring out how to work the Wiki. Jerry)

I’m about to describe what I think is a useful approach to some reporting problems that are of interest to me. But I’ll say up front that I’m probably going to abandon this approach because I think I have a better idea moving forward. But in the meantime, I’ll report an what I’ve done so far. Well, I’m going to abandon this useful approach at least in part, but there is at least one case where I will probably continue with this approach.

There are some cases where I want to report on some additional information that is not presently in any of the RM4 tables. So the basic idea is to create a “real” table in MS Access and to think of the “real” MS Access table as if it were a “virtual” SQLite table in the RM4 database. No changes will be made on the SQLite side of the house. I place the additional data on which I want to report in the MS Access table, JOIN the MS Access table with an appropriate SQLite table in the RM4 database, and I can use MS access to report on the JOIN’ed data just fine.

Of course, from the point of view of MS Access, the roles of “real” and “virtual” are just the reverse – the MS Access table is “real” and RM4 SQLite table is “virtual”. And from the point of view of MS Access, you can JOIN a “real” MS Access table with a “virtual” SQLite table in the RM4 database just fine. The idea is to create an MS Access table to contain the additional data, and to be sure that the MS Access table contains a column that can be JOIN’ed with an appropriate RM4 SQLite table.

The first example is an MS Access table that includes a column that has the names of the RM4 colors. I want to be able to display the string “Green” rather than the number 9, for example. And I want to be able sort and filter on the name of the color rather than the number code for the color. I’m sure there is a simple and straightforward SQL way to achieve the required mapping between numbers and colors without having to make a new table, but making a new table is what I know how to do and it is very much in the spirit of how relational databases are usually designed.

To that end, I created the following MyColorTable and linked it to the RM4 PersonTable, with the required JOIN being between PersonTable.Color and MyColorTable.ColorID.

The MyColorTable above is the example I plan to continue using moving forward. The following idea is the one for which I think I have a better approach moving forward. For some number of individuals – 17 individuals or 32 individuals or 103 individuals or whatever – I may have five “to do” tasks that I wish to accomplish for each individual. I wish to create a grid with 17 or 32 or 103 or whatever rows, and with five columns. I want to treat the grid as a checklist where I simply check off the appropriate cell for each individual and for each “to do” task when that task is completed for that individual. In my experience, traditional “to do” management software doesn’t work in this very simple and user friendly fashion, including “to do” management that’s available in RM4 itself and “to do” management available in GenSmarts.

So the obvious solution is to create a ToDoTable in MS Access with one column containing the RM4 Record Number for the individuals of interest and the other columns corresponding to the “to do” tasks to be accomplished. The only data that each cell in the “to do” columns has to contain is a 0 or a 1, or a blank or a 1. The 1 is my check mark denoting that the task is complete

This being a relational database application, I went at the problem slightly differently than having a row in my ToDo table for each individual of interest. I don’t really want to constantly be mucking around with entering the RM4 Record Numbers of the 17 or 32 or 103 individuals that are currently of interest. Rather, I want to automate that process. So I really created my ToDo table in MS Access with a row for every individual in my RM4 database. And to allow for future growth of my RM4 database without having to muck around with my ToDo table, I really, really created my ToDo table in MS Access with many more rows rows than there are currently people in my RM4 database. My RM4 database currently has about 58,000 individuals. So I created my ToDo table in MS Access with 100,000 rows. Row 1 of the RecNo column in the ToDo table contains 1, row 2 of the RecNo column contains 2, and so forth through row 100,000 of the RecNo column contains 100,000. The required JOIN is between ToDo.RecNo and PersonTable.PersonID. Some of the rows will never be used, but that’s fine. At the present time, I only have a column for one of my five “to do” tasks. I’ll add the other four “to do” tasks later.

Finally, I linked PersonTable to NameTable so I could display useful information about the individuals, and I filtered by color. So the standard RM4 color coding could be used to select individuals rather than having to muck around with lists of RM4 Record Numbers in my ToDo table, and the final result is as follows.

I’m using “green people” as my filter, individuals color coded as green in RM4. The PrgraphOk task being complete means that for these particular individuals, I have painstakingly edited all fact notes so that paragraphing and white space looks exactly the way I wish for it to look in a narrative report. So in theory, I never have to perform this task again for those individuals. When I first created this MS Access query, the PrgraphOK column was completely blank. I manually replaced each blank with a 1 as I completed my editing and review of the way those individuals appeared in a narrative report. If I continue in this vein, I will gradually color code more people as green in RM4 to create my expanded “to do” list, and if I wish I can filter my MS Access query not to display the rows where PrgraphOk has already been set to 1.

The only thing I don’t like about this approach is that the information in the PrgraphOk column really isn’t stored in RM4, and I might like it to be stored there. There are number of creative places in RM4 where I could store the fact that I have completed my paragraphing review. And having done so, my query could create the PrgraphOk column by testing the data in RM4 rather than by having the data stored in my own ToDo table separate from RM4. I would still have to store the “I completed my paragraphing review” data in RM4 manually when I completed my paragraphing review, but it would keep the data in RM4.

Jerry

Discussions & comments from Wikispaces site

Inline comments