Okay, so I may have led you on a little bit in the previous post, expecially with the title. You WILL get some ActionScript. A lot of it. But for now let’s wrap up what we’re doing with Access as a relational database to manage a bunch of data about our curriculum maps.
So, in the previous post, I went through a bit of my rationale behind the architecture I came up with for the database, and how it models the way in which my organization is looking at career learning mapping. With all that data organized in Microsoft Access, everyone with the software in the organization can maintain the database if they understood what I put in the last post. But to use the data in the little Flash browser I built (and you can build after my next post), we still need to spit that data out in a format that makes sense. Flash can’t read an Access database directly (not without some help), but Flash can read XML just fine, and we can export XML from Microsoft Access.
Now, we could do an entire data dump of the database to XML, but that file would be very difficult to work with. By running a query and exporting the results of a query to XML, we can have a little more control over the output (Access doesn’t give you any options for how to format the XML), and when the XML is easier to work with, you’ll have a much easier time building an application to work with it.
So the first thing we need to do is figure out how we want to eventually use this data. The way I envisioned an employee browsing for information about a learning map was like using the browser in iTunes…
Going from left-to-right along the three lists, you can browse iTunes first by selecting a Genre, which then filters the list of Artists down to the artists that qualify in that Genre. When you select the artist you want, you can additionally filter down to the Album you’re most interested in by that artist and go on to select a song from that album. Using the iTunes browser, I can manage my own library of 23,907 songs (66.3 days of music) and find what I want in seconds.
Our learning maps are organized similarly, but the total population is not nearly as large (currently only 300 or so resources) organized into only about 8 defined learning maps. Our browsing dig-down (at the moment) really only needs to go from Learning Map -> Course ID -> Descriptive information. Because of the way we’ve structured the database, we can actually support far more robust searches if we needed them, but it’s admittedly overkill to build extensive search features for a population of data that’s so tiny.
Nevertheless, the query we need to put together to pull information that will format nicely to this (that Access will just natively kick out so a guy like me isn’t always stuck maintaining either the database or the Flash application) is pretty significant.
SELECT tAggregations.learningRoadmapId, tAggregations.cardinality, tAggregations.resourceId, tAggregations.trackId, tAvailability.availabilityId, tAvailability.availabilityStatus, tCategories.name AS tCategories_name, tResources.name AS tResources_name, tResources.description, tResources.link, tTracks.name AS tTracks_name, tTypes.name AS tTypes_name
FROM (tTypes INNER JOIN (tCategories INNER JOIN (tAvailability INNER JOIN tResources ONtAvailability.availabilityId = tResources.availabilityId) ON tCategories.categoryId = tResources.categoryId)ON tTypes.typeId = tResources.typeId) INNER JOIN (tTracks INNER JOIN tAggregations ONtTracks.trackId = tAggregations.trackId) ON tResources.resourceId = tAggregations.resourceId
ORDER BY tAggregations.learningRoadmapId, tAggregations.cardinality, tAggregations.resourceId;
Fortunately, you don’t need to know how to write Inner Joins or SQL-style query language in order to pull good information out of the database. Access, for what it is, does it pretty much all for you.
In Design mode, you can define your relationships like this (note how this looks exactly the same as the relationships in my previous post):
Once you’ve established the relationships, you can then simply select the fields you want a report on based on how some of the fields are related to each other (these are those INNER JOIN statements written in SQL — for example, I JOIN the Tracks table to the Aggregations table where the field trackId in both tables are the same.
When you run this query, you’ll get a whole report based on the data you already entered in the database and the way your Query describes it to be produced. This query can then be exported out of Access directly as XML, and the structure of this particular query will output data that looks like the following:
<tResources_name>Business Conduct Guidelines for Monkeys</tResources_name>