The Access Wizard Newsletter Tips, Tricks, and Traps for Access Users and Developers
February 2009

The Flash Card Program Data Structure: The Success Table

We're in the heart of winter here in New England. It's cold! This is the time of year you either bundle up and venture out into the cold weather, or batten down the hatches and stay inside. If you are staying inside, it's a good time to learn new stuff. I'm not a winter person, so I've taken on the task of learning a new language - Italian.

Last month, we continued the process of building a Flash Card Program to help us learn vocabulary and analyzed the Flash card table. This month, we continue with our analysis of the flash card success table.

In this Issue
  • Tip of the Month - How To Pull The Date Portion Of A Date Time Field
  • Recap
  • The Flash Card Success Fields
  • Putting the Tables Together - How Do They Talk To Each Other
  • The Foundation Is Built - Time To Look Forward

  • Recap

    We're in the process of building a program to help us learn vocabulary for the Italian language. In the last two issues, we've laid out the requirements for the program and have begun to analyze the tables in the application, as shown below.

    In our last Wizard, we delved deeply into the flash card table. This month, we'll do a thorough analysis of the flash card success table. At the end of this process, we will have our tables understood and in place.

    The flash card success table is designed to capture each attempt at learning a word. The thought is that when a user attempts a word, we record either success or failure. If the user gets the word correct, it is recorded. If the user fails to get word correct, we record the unsuccessful response.

    We get three important outcomes from this table.

    First, we get an overview of how difficult each word is to learn.

    Second, by analyzing each failed response, we get a sense of the type of mistake that is being made.

    Third, by analyzing incorrect responses, we can detect patterns that, when understood and corrected, can lead to tactics that can help our users become more efficient at learning the language.

    The Flash Card Success Fields

    There are five fields in the table.

    FlashCardSuccessKey - This is our auto number field; it is a primary key whose job is to uniquely identify the record in the Success table.

    FlashCardKey - This is the identifier of the word being learned. It is a foreign key that hooks into the primary key in the flash card table.

    TestSet - This is a grouping mechanism; it is used to identify which attempt the flash card record is associated with.

    AttemptDate - This is a DateTime field that records the date and time that the user makes an attempt. This defaults to Now() so that it automatically will put a time stamp on the record.

    Success - This is a true/false field that indicates whether or not the particular attempt was successful.

    IncorrectResp - This field is used to record the response that was attempted only when the response was incorrect. There is no need to record the correct response, because we know it matches whatever was expected as recorded in the Flash Card table.

    Putting the Tables Together - How Do They Talk To Each Other

    You'll notice that there is a link between the Flash Card table and the Flash Card Success table. This link is between the FlashCardKey in the two tables. The names, by intent, are the same in both of the tables. The idea here is to make it easy to analyze the foreign key when you're looking at the Flash Card Success table.

    You can see this link in the diagram above with the line going from the fields FlashCardKey in both tables. When you look at the diagram, you'll note a small digit 1 next to the Flash Card table and an infinity symbol next to the Flash Card Success table. These indicate that for every record in the Flash Card table there is one or more records in the Flash Card Success table.

    The LookupMaster and LookupType tables play a supporting role. They're also joined with the same field in each table. As I mentioned last month, these tables, although not critical to the Flash Card Program, are standardized in all my applications. You could easily get to the same functionality of these tables with individual lookup tables as needed.

    The Foundation Is Built - Time To Look Forward

    At this point, all the critical pieces are in place. We know what we want the application to do and we've designed our tables.

    Keep in mind that, even though we've spent a fair amount of time analyzing and building the tables, in practice we'll likely make changes to the fields and tables as we begin to find various needs that we had not anticipated, or as we make decisions that will help us get greater speed in either development time or in application response. Our goal is to discover these changes as early as possible. Since these are structural issues, the further downstream we are in application, the more expensive it becomes to make a fundamental change to our table structures.

    Next month, we'll begin to design the forms with which the users will interact. This tends to be a key part of the application and the most fun to build.

    Tip of the Month - How To Pull The Date Portion Of A Date Time Field

    This month, we used a date time field to record when a user makes an attempt at learning a word. By default, a date time field will record both a date and a time. For example, 11/29/2008 10:07:47 AM would be recorded on the weekend after Thanksgiving on Saturday morning. If we want to get just the date portion of that into a query, report, or form, there is a handy function called DateValue.

    To use this function, type DateValue followed by the date and time that you want to get the date for, enclosed within parentheses.

    For example, if we type DateValue(now()), it returns just the date. If you are using it within a query, then you just reference the field that contains the date and time for which you want the date.

    If you are trying to manipulate an actual date and time it's just a tad trickier. Access identifies a date and time with the pound symbol "#". Therefore, when you're entering in an actual date and time, you have to begin and end the date and time with #. For example, you enter datevalue(#11/29/2008 10:07:47 AM#) and that would resolve to November 29, 2008.

    Quick Links...

    Custom Software Home

    Access Wizard Archives

    Our Services

    Join our mailing list!

    Forward email

    Safe Unsubscribe
    This email was sent to jim@custom-software.biz by jim@custom-software.biz.

    Custom Software | Copyright Custom Software All Rights Reserved | Westford | MA | 01886