Creating Schema
One of the most basic tasks in Access is creating somewhere to put data - designing schema. My goal for today was to run through the basic schema creation tools, and to leave the new "complex data" schema creation for next time, but in looking at the last post, much of the most basic ground was already covered, so we'll go further in this post and cover the complex data features as well.
Schema from Datasheet
As described in the last post, Access 12 can create schema by example, where the user simply starts typing into a grid (the "datasheet") and we guess the data types automatically and the product just works. This is simple for beginning users, and Access 12 does automatically create an ID column for each table, so there's a unique index for the table, and the user has something to build joins on as she gets more sophisticated. The basic schema by datasheet experience looks like this:
The user simply starts typing in the header row to set column names, then types data in the rows below to fill-in her table. Access guesses the data types as she does this. For more details, please look at the Starting From Scratch post.
Table Templates
One of the key problems that new users have in Access is creating well-structured databases. The tracking apps will help by creating normalized schemas for the most common applications, but many users will need to build their own databases without starting from the tracking apps. Table Templates provide a way for users to get well-structured single tables that they can easily add to their own applications. There's a simple entry point on the ribbon:
The entry provides a list of 5 basic common table types that the user can simply drop into her application and extend or use as is. An example would be the Issues table, which looks like this:
The full list of fields is easier to see in the table designer:
SharePoint List Templates
Access 12 provides a set of templates for SharePoint lists similar to that for local tables. This list is available from a button in the ribbon right next to the Table Templates:
It has a similar list of tables, with the exception of Custom (which creates a simple SharePoint list that you can then extend) and it also provides an alternate way to link to an existing SharePoint list. Selecting one of these lists brings up a prompt for the appropriate server and a name for the list:
The schemas for these lists are similar to those for the local tables, the only difference being that the SharePoint lists are created on the server and linked to Access.
Field Templates
Users who want to create their own tables can still get help building well-structured fields (i.e. with appropriate data types and lengths) from the Field Templates. Here the user selects the field templates button on the ribbon and gets a list of potential fields. This list contains a set of generic fields, and then all the individual fields from the Table Templates above. Click the image below to see the list of available fields for Issues.
(Click image to enlarge)
The user then simply drags and drops the appropriate field into her table.
(Click image to enlarge)
Lookup Fields and Complex Data
Access has supported "Lookup Fields" for several versions and has added power to the feature for Access 12 by allowing for multi-valued lookups. Lookup fields are simply fields that present a different value in their control than they store in the table. In the Issues example we've been using, "Assigned to" and "Opened by" are both lookups. The Issues table stores the ID of the entry in the Contacts table, but whenever the user sees the Issues table, she sees the Contact Name rather than the number. This is simply good database practice (normalized data, joined on unique identifiers), but made easy enough for non-developers. The last post showed creating a normal lookup column using the Lookup Wizard.
Access 12 extends the lookup concept by enabling multi-valued lookups. Where a lookup is really just a join made easy, a multi-valued lookup is simply a many-to-many join made easy. In XML terms this would be represented as an optional repeating value, which is why we think of this internally as support for complex data. SharePoint uses these data structures in a number of places (e.g. support for multiple attachments for each item in a list), so Access needed to add similar support to provide schema symmetry, but the concept is broadly useful outside the context of SharePoint. In our Issues example, the user may want to assign an issue to more than one person. This was do-able before, but required a lot of work (building the join table, setting up the relationships) and a lot of knowledge (i.e. how to build the join table, etc.). The Lookup Wizard now does all this for the user.
The user simply starts by clicking the Lookup Column button on the ribbon (with a column selected in the datasheet):
(Click image to enlarge)
Then she goes through the wizard as she did before:
(I skipped a couple of steps here, since they're the same as the ones shown before.) On the last pane of the wizard, she selects "Allow Multiple Values" and hits finish. This automatically builds (and hides) the join table and sets up the relationships, so the experience is just the same as for a standard lookup.
The key difference, though, is that the user can now select multiple people in the Assigned to column:
Next Time
The next post is a little up in the air. I'll hopefully be able to talk about the new Ribbon UI. However, we're doing some polish on how the UI is laid out and I'd like to be done before I explain how the whole thing works, so I don't then have to explain why it is all changed! We're pretty close to nailing that down, so hopefully next week I'll be able to describe it all. If not then, the week after. If I can't talk about the ribbon, I'll cover the new report designer, which is super cool too.
