tag:blogger.com,1999:blog-110785012024-03-05T10:17:25.720-05:00Argee's Access BlogGlenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.comBlogger26125tag:blogger.com,1999:blog-11078501.post-81338000884047588972016-06-09T10:56:00.001-04:002016-06-09T10:56:52.902-04:00... GPG On MS Access: Back to Earth for a Moment.<a href="http://gpgonaccess.blogspot.com/2016/06/back-to-earth-for-moment.html?spref=bl">... GPG On MS Access: Back to Earth for a Moment.</a>: Recently, I’ve spent a lot of time thinking about, and working with, Access Web Apps, which is Microsoft’s tentative venture into making MS...Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-65580373754575220712010-03-13T10:24:00.001-05:002010-03-13T10:24:49.105-05:00Office 2010 Set to Launch<p></p> <p> </p> <p><a href="www.toonaday.com"><img style="border-right-width: 0px; margin: 10px 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="ShakySkates Cartoon" border="0" alt="ShakySkates Cartoon" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSuDauyI8198RBPk9B5DUChreDhSaOxDw93j03PIueCz3mIcJC0ZYDfhZixTJCHUrtQxYxFGCIHF3_u5cc-chyphenhyphenKLUsvE7i8LnLQvihhdmf3AFBeJCEUtxkF_e64gq3ptmRoT3QoA/?imgmax=800" width="212" height="214" /></a>The <a href="http://blogs.technet.com/office2010/default.aspx">Microsoft Office 2010 Engineering Blog</a> has announced a May 12, 2010 worldwide launch date for “the 2010 set of products, including Office 2010, SharePoint 2010, Visio 2010, and Project 2010” for business users. Office 2010 will be available for consumers on-line and in retail outlets in June 2010.  If you want to have an sneak preview look at what is coming you can download the beta at <a href="http://www.office.com/beta">www.office.com/beta</a>.</p> <p>I have been poking around the Access 2010 beta for the last while and I think it is going to be a nice version to work with. In case you are wondering, the Ribbon stays. In fact, I believe that the <em>Fluent User Interface </em>as the Ribbon is officially, will now be found throughout the Office suite. The Office Button has been replaced with a File tab on the ribbon. This tab leads to the <em>backstage</em> of each application.</p> <blockquote> <h3>Quote of the Day</h3> <p><em>Perhaps the most valuable result of all education is the ability to make yourself do the thing you have to do, when it ought to be done, whether you like it or not; it is the first lesson that ought to be learned; and however early a man's training begins, it is probably the last lesson that he learns thoroughly.</em></p> <p align="right">- Thomas H. Huxley</p> </blockquote> <p>The single-most exciting new development as far as Access is concerned is that it is now possible to develop Access Web Databases and publish them to SharePoint. Ryan McMinn present this new development in <a href="http://msdn.microsoft.com/en-us/office/ff436079.aspx">this video.</a></p> <p>If you are interested in general Office development, have a look at <a href="http://msdn.microsoft.com/en-ca/office/ff436077.aspx">John Durant's presentation.</a></p> <p>Pej Javaheri and Steve Tullis present Excel and Excel Services 2010 developments in <a href="http://msdn.microsoft.com/en-ca/office/ff436080.aspx">this video.</a></p> <p>All in all , Office 2010 looks pretty exciting with lots of new ‘stuff’ to learn but with big paybacks for taking the time you will spend on learning.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-52060114543964209672010-03-10T10:47:00.001-05:002010-03-10T10:47:07.662-05:00Access Naming Oddity<p><a href="http://toonaday.com"><img style="border-right-width: 0px; margin: 10px 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="MIniBike" border="0" alt="MIniBike" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQrGGP9filUkLN_xJHAXMT5yJX_k4SzRR47R4bBsXAIygXimcqqlPaCAVcvr81WN_RGd7WsN0j-D7NU5Jy1b0oPu1PW0DMMcBPJKR4wfAr6KcbZrJjkzIpUj8weDV6YuzwVc0n4A/?imgmax=800" width="113" height="124" /></a>As a design principle, I apply a naming convention when creating new database objects. For example, field names begin with a lowercase letter. Recently, I was putting together a small application in Access 2010 beta so that  I could gain a little familiarity with the new Access version that will be released later this year. In a weak moment, I accidentally named some of the fields in a new table with uppercase first letters.</p> <p>In the grand scheme of things, this is not a particularly serious problem but inconsistently formed names lend an unprofessional look to an application. When I tried to correct the names I discovered an inconvenient quirk. After replacing the first letter of each field name with its lowercase equivalent, saving and closing the table and then re-opening it again in design view, I found that the first letter of each field name had changed back to uppercase. </p> <p>No matter what I tried, I couldn’t get the lower-case letter to ‘stick.’ That’s when I turned to my favourite forum for help. Thanks to UtterAccess VIP member (and Access MVP) datAdrenaline, I quickly had a reasonably workable solution.</p> <blockquote> <h3>Quote of the Day</h3> <h2><em>I have never been especially impressed by the heroics of people who are convinced they are about to change the world. I am more awed by those who struggle to make one small difference after another.</em></h2> <p align="right">- Ellen Goodman</p> </blockquote> <p>All I had to do is change the first letter of each field name I wanted to modify, save and close the table, re-open the table and replace whatever I had entered as the first letter of the temporary field name back to the lowercase letter with which I wanted to start the name. Step by step this is the method I applied:</p> <ul> <li>open the table in design view </li> <li>pre-fix each field name with a single letter </li> <li>delete and replace the original uppercased letter </li> <li>save and close the table </li> <li>reopen the table in design view </li> <li>remove the leading character for each field to correct name </li> <li>save and close the table </li> </ul> <p>This problem occurred specifically in Access 2010 beta so it may be a non-issue when the new version is released but if you run into similar situations perhaps a similar approach will help you out of a bind “when all else fails.”</p> <blockquote> <h3>New articles in <a href="http://www.utteraccess.com/wiki/index.php/Main_Page">Access Wiki</a></h3> <p><a href="http://www.utteraccess.com/wiki/index.php?title=Links&rcid=439">Links</a></p> <p><a href="http://www.utteraccess.com/wiki/index.php?title=CamelCase&rcid=437">CamelCase</a></p> <p><a href="http://www.utteraccess.com/wiki/index.php?title=Access:_What_Is_It%3F&rcid=433">Access: What is it?</a></p></blockquote> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-58087488371617432952010-03-07T10:27:00.001-05:002010-03-07T10:29:48.547-05:00Access Wiki – 2 Weeks and Counting<p> </p> <h3>Quote of the Day</h3> <blockquote> <p><em>Real, constructive mental power lies in the creative thought that shapes your destiny, and your hour-by-hour mental conduct produces power for change in your life. Develop a train of thought on which to ride. The nobility of your life as well as your happiness depends upon the direction in which that train of thought is going.</em></p> <p align="right">-Laurence J. Peter</p> </blockquote> <h3><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhhQ9j-QAOZ9Wb_Ju31z7h7duVyJTlFOgXYuP8jTWkQFqMpp8dV2OYlEXMeqgGumj9P-KClKgxmPliQxYay9frgJ0S-qj5kjRbUCxrhC4bYB0MOVPQQC5wThBPYysuE2SzLJ4zJ2A/s1600-h/Taco3.gif"></a></h3> <p><a title="taco" href="http://www.toonaday.com"><img style="border-bottom: 0px; border-left: 0px; margin: 10px 20px 20px 0px; display: inline; border-top: 0px; border-right: 0px" title="Taco" border="0" alt="Taco" align="left" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhXNOIsi23FNUxKuDoCdjYDEhigKGtvNMqz5L0nlQlE-1Cbo8S030k1jk-cfCn-DB3m1MiTwSWVfBTJhlz3WhRIwsPPp0cSBBGUP7CIbOHmlJ4mU-HYPcLcdHpQ5z_OVo0TMBR0zQ/?imgmax=800" width="114" height="164" /></a>By the end of the day, the Access Wiki  will have been ‘open for business’ for two weeks. The body of Access knowledge that the site  promises to be is slowly building. As of this moment, there are 26 articles published in the wiki with articles including such topics as <em><a href="http://www.utteraccess.com/wiki/index.php/Normalization" target="_blank">Normalization</a>, <a href="http://www.utteraccess.com/wiki/index.php/Sharing" target="_blank">Sharing</a> </em>(Access Databases through splitting), and <em><a href="http://www.utteraccess.com/wiki/index.php/Error_handling" target="_blank">Error Handling</a>. </em>Access Wiki content is expected to grow as UtterAccess members continue to write and publish articles. The wiki features a <a href="http://www.utteraccess.com/wiki/index.php/Category:Table_of_Contents" target="_blank">table of contents</a> and an <a href="http://www.utteraccess.com/wiki/index.php/Category:Access_Wiki_Index" target="_blank">index</a> if you want to look over a list of topics.</p> <p>Meanwhile, on the forums side of UtterAccess, there is a whole new ‘look and feel.’ The new appearance is similar to what you will see one the Wiki side.</p> <p>If you haven’t yet visited <a href="http://www.utteraccess.com/forum/home.html" target="_blank">UtterAccess.com Forums</a> or the Access Wiki, why not check them out now and see if you don’t agree that UtterAccess is <em>the only source for all of your  Microsoft help needs. </em>The wiki and forums are freely available to anyone on the World Wide Web but only registered members can download code samples from the forums or write and edit wiki articles. Membership is free and confidential. Signing up will give you access to thousands of downloads and the opportunity to contribute your own articles to the Access Wiki</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-34684349156280768962010-02-21T11:13:00.001-05:002010-02-21T14:35:56.659-05:00UtterAccess Launches Access Wiki<table border="0" cellspacing="0" cellpadding="2" width="639"><tbody> <tr> <td valign="top" width="304"> <h3>Quote of the Day</h3> <p> </p> <h3><em>The most important thing in the Olympic Games is not winning but taking part; the essential thing in life is not conquering but fighting well.</em></h3> <h3 align="right">-Pierre de Coubertin</h3> </td> <td valign="top" width="145"> </td> <td valign="top" width="188"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiorM08ExmVpzG1vCtk1vpKMR-ftP7xSY-T8GsvQKdDZEQxAOmIfLBD_Scxn3lJu3ZzjVVUf-0FnbC-in16vyVl5CJvaQ14458GrYe1Xf4kUBiVXkAbeObw2_3YTHbsBkKTCZfObA/s1600-h/Ispy%5B34%5D.gif"><img style="border-right-width: 0px; margin: 10px 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="Ispy" border="0" alt="Ispy" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZElAlhshXYC9SWyucPKWg-ZP-1Q-5leKpvtrJjcFP8zERBCKzVtlZtDOrru2_3rqy-Lsv1jyVVhu6NjwejPu8r9r6MFAZCQSE_bkBHCMoHghyKk8RUM-5x0x9etOi1USuYhHsQQ/?imgmax=800" width="163" height="184" /></a> </td> </tr> </tbody></table> <p>The best forum in the Access community, <a href="http://utteraccess.com" target="_blank">UtterAccess</a>, is about to get even better.  Tomorrow, <em>The Access Wiki </em>will take its place alongside UtterAccess Discussion forums. This is the most significant enhancement to UtterAccess since its inception some eight years ago. In the coming months and years, the wiki is expected be become a treasure house of in-depth information for Access database developers as members contribute and update original articles discussing all aspects of developing and enhancing Access applications.</p> <p>Sometime overnight tonight, UtterAccess.com will go off-line and will be unavailable for a brief period. Then, with little more fanfare than the flipping of a switch, the wiki will be ‘live to air’ and the forums will be back on-line. If you have ever visited the UtterAccess site, you will immediately notice the forum’s new look and feel, even a brand new logo, thanks to UtterAccess member and moderator Mark Davis. The wiki will share the new ‘look and feel’ of the forum. UtterAccess members will be able to log in to the wiki using the same user id and password as they use for the discussion forums.</p> <p>What is a wiki? <em>A <b>wiki</b> (pronounced /ˈwɪki/ <small>WIK</small>-ee) is a website that allows the easy creation and editing of any number of interlinked web pages via a web browser using a simplified markup language or a WYSIWYG text editor. Wikis are typically powered by wiki software and are often used to create collaborative websites, to power community websites, for personal note taking, in corporate intranets, and in knowledge management systems</em>. (<a href="http://en.wikipedia.org/wiki/Wiki" target="_blank">Wikipedia, the free encyclopedia</a>)</p> <p>The UtterAccess Access Wiki and Discussion Forums will naturally complement each other. Discussion Forums provides a question and answer peer support environment where members can raise and respond to specific problem oriented questions. The wiki will provide in depth discussions of Access related topics to which a user may point when responding to questions. Access Wiki readers will turn to the Discussion Forums from time to time when they need help in understanding topics discussed in the wiki. Together Access Wiki and Discussion Forums will be a win-win partnership in Access education, re-affirming that UtterAccess.com <em>is <b>the only</b> source for all of your Microsoft Access help needs...<b>and more!</b></em></p> <p>I’d like to thank UtterAccess.com Owner/Administrator Gordon Hubbell for keeping UtterAccess in the forefront of the Access community over the years and for his tireless efforts along with help from Microsoft’s Clint Covington and Kerry Westphal over the past couple of months to bring the Access Wiki to fruition.</p> <p>As soon as the relevant links are available, I will be publishing a follow up article.</p> <p>Congratulations Gord and UtterAccess!!</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-38591930210643204102010-02-09T04:08:00.001-05:002010-02-09T04:08:17.704-05:00Let’s Split<p><a href="http://www.toonaday.com" target="_blank"><img style="border-right-width: 0px; margin: 10px 25px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="PostalChase2" border="0" alt="PostalChase2" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiBllzEDhMcyIgEvlvofCe-HOQTtMvNbqPcYiUXNnCP6H9rSuBFymEfKmeQM0e-2xd1UCil5_9C6R9wmXdkkDePIQqfG-AWt5Qti5rAa8uZWVdzWqEUs4wVb02YQQTVujOo4YvcHw/?imgmax=800" width="212" height="162" /></a>The term ‘split database’ seems to strike fear and trepidation into the hearts of people just getting to learn about creating Access applications. This article is for Soma, who raised the question privately with me. Thanks for asking Soma, I hope these few notes help you to a better understanding of what a split database is and why you should split it.</p> <h4>What does ‘split database’ mean?</h4> <p>An  Access file (.mdb 2003 and earlier, .accdb – 2007 & 2010) can contain both user interface and the data of the database. Think of the user interface as being everything you need to display data to the user in useful and meaningful ways, everything, in fact, except the actual tables and relationships between them.</p> <p>A split database architecture separates the user interface from the data. In Access, the user interface ‘lives’ in a file known as the frontend. This file contains all of the application’s forms, reports, and queries. The backend, on the other hand, is a second file that contains the tables and relationships of the database.</p> <h4>Why should a database be split?</h4> <p>Separating the user interface and data storage functions into two separate files makes the database easier to manage and greatly simplifies the enhancement and deployment of frontend modifications.</p> <p>In a multiuser environment, a split database is mandatory so that all users can share and work with the same data. That is because their individual copies of the frontend all link to the same backend. To put it in somewhat techy terms, one data source serves all users of the database. If each user had his or her own copy of the data, managing changes to the data and keeping all copies of the data ‘in sync’ adds a whole new layer of complexity to managing the data.</p> <p>However, even if there is only one user, a split database still makes a lot of sense. That is because you can continue to develop and enhance (and replace) the frontend as needed without jeopardized your valuable data. In my own applications, for example, I have a frontend that I use on a day to day basis. I have a second, development, version of the front end where I try out new ideas for form and report design. The development frontend is linked, not to my live data, but to a test dataset that I copy from my live data from time to time. When I am satisfied that the development frontend is working correctly and has been properly tested, I put it ‘into production’ by linking it to my real frontend. (Incidentally, UtterAccess moderator Cybercow has posted a nice utility for switching a frontend’s between test and live data in the <a href="http://www.utteraccess.com/forums/showflat.php?Cat=&Board=48&Number=1328573&Zf=f48&Zw=&Zg=0&Zl=a&Main=1328573&Search=true&where=&Zu=8755&Zd=l&Zn=&Zt=2b&Zs=b&Zy=#Post1328573&Zp=" target="_blank">UtterAccess Code Archive</a></p> <h4>How do you split a database?</h4> <p>Generally, splitting a newly developed Access database is quite straightforward. Access includes a Split Database wizard that will move the original database’s tables and relationships to a new file. There is also the Linked Table Manager to allow you to manually link a frontend to the backend when necessary.</p> <p>This article is intended to be a brief overview of what it means to have a split database. The Access help files contain more complete descriptions of the concept and how to go about splitting your database. You might also what to do an advances search for ‘split database’ at <a href="http://www.utteraccess.com/" target="_blank">UtterAccess</a> where you will find many questions on the topic posted by many new members over the years.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-14475868564105165462010-01-27T10:47:00.001-05:002010-01-27T10:47:13.536-05:00Keep an eye on UtterAccess.com<p><a href="http://www.toonaday.com"><img style="border-right-width: 0px; margin: 10px 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="GeekPride" border="0" alt="GeekPride" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmUHTwbWNOKJiWiZwXqvR6U06Wub41Ifqcl_jPLxv01sYwbVmTma9qWoxPXBosGee2Nv0P2NvdXctWSFdPd_7mo5tJr64ezzTdzqOnf0h93ZQ056r8u-Ho9VPaUqwlarEQoQAcIQ/?imgmax=800" width="216" height="250" /></a></p> <p> </p> <p> </p> <p> </p> <p> </p> <p> </p> <p>Gord over at UtterAccess is putting the finishing touches on an enhancement to the UtterAccess forum that will be as significant for the Access community as was the introduction to the web of UtterAccess itself almost eight years ago. I’m afraid you will have to wait for the public launch to learn more details but I can guarantee that you won’t be disappointed. At UtterAccess, we pride ourselves as <em><b>the only</b> source for all of your Microsoft Access help needs. </em>If every you had any doubt about that claim, this new feature will convince you that UtterAccess is what we clam and much, much more. </p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-32586301446565453562010-01-10T10:56:00.001-05:002010-01-10T10:58:03.344-05:00Access 2010 Forward Compatibility<table cellspacing="0" cellpadding="2" width="723"><tbody> <tr> <td valign="top" width="363"> <h3>Quote of the Day</h3> <p><em>If you have built castles in the air, your work need not be lost; that is where they should be. Now put the foundations under them.</em></p> <p align="right"><em>-</em>Henry David Thoreau</p> </td> <td valign="top" width="131"> </td> <td valign="top" width="227"> </td> </tr> </tbody></table> <p>Clint Covington posted  <a href="http://blogs.msdn.com/access/archive/2010/01/09/access-2010-deprecated-features-and-components.aspx" target="_blank">Access 2010 deprecated features and components</a> in the Access Product Team Blog yesterday that certain features have been deprecated in Access 2010:</p> <ul> <li>Calendar Control </li> <li>Snapshot Format </li> <li>Data Access Pages </li> <li>Paradox, Lotus, and Red2 ISAM’s </li> <li>Replication conflict viewer </li> </ul> <p>If you have applications developed in older Access versions that you want to port to Access 2010, read <a href="http://blogs.msdn.com/access/archive/2010/01/09/access-2010-deprecated-features-and-components.aspx" target="_blank">Clint's article</a> to see what effect these changes will have on your application and some suggested workarounds.You should also keep these deprecations in mind for any new applications you may develop in pre-2010 Access versions.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-22082518460599827282010-01-06T11:45:00.001-05:002010-01-06T11:45:48.423-05:00Date Stamp Access Records<table border="0" cellspacing="0" cellpadding="2" width="595"><tbody> <tr> <td valign="top" width="308"> <h3 align="center"> </h3> <h3 align="center"> </h3> <h3 align="center">Quote of the Day</h3> <p><em>Not only is the universe stranger than we imagine, it is stranger than we can imagine.</em></p> <p align="right">-Sir Arthur Eddington</p> </td> <td valign="top" width="139"> </td> <td valign="top" width="146"><a href="http://www.toonaday.com"><img style="border-right-width: 0px; margin: 10px 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="TorchRelay" border="0" alt="TorchRelay" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiscTjWCKJya4DaZHf9rGXGrUrcWEZY3q34qbAPheoMOm6SC_-wsuDyE-zZhlbSrGt1AAb-zIv8Y6Us646-klz8lW8UMmyt47mKAI6EDIMt29BlJmcI0uyHe383YmXzKy_pr1aQcQ/?imgmax=800" width="206" height="245" /></a> </td> </tr> <tr> <td valign="top" width="305"> </td> <td valign="top" width="177"> </td> <td valign="top" width="146"><a href="http://www.vancouver2010.com/" target="_blank"><img style="display: inline; margin-left: 0px; margin-right: 0px" align="right" src="http://www.canada2010.gc.ca/images/logo-nw-eng.jpg" /></a> </td> </tr> </tbody></table> <p>Using forms to control routine data entry should be standard operating procedure for any non-trivial production Access database. In plain language, don’t give users (even yourself when you acting as a user and not the developer) access to the raw tables. Provide instead forms for entering, viewing, and editing records. In this article we will look at how to use two simple form event handlers to automate recording of when a record is created and when each record was most recently updated.</p> <p>Event handlers are the key to adding custom functionality to applications. In this case, we want to keep track of when a record is created and that date it was last edited so we will need to work with the <em>Before Insert </em>and <em>Before Update </em>events. First, a little background.</p> <p>When you create and edit a new record, the record does not exist in a table until you do something to cause the record to be saved. You are actually editing a memory image of what will become the new record when it is saved. Actions like moving to a different record, or pressing Shift-Enter initiate the save (or insert) process. As its name implies, the <strong><em>Before Insert </em></strong>fires before the record is inserted into the table.</p> <p>To be completely accurate, the Before Insert event fires when the user types the first character of a new record (see <a href="http://msdn.microsoft.com/en-us/library/bb237626.aspx" target="_blank">msdn article</a>). At this point the record does not yet exist. This give you, the developer, the opportunity to take some additional actions by adding appropriate code to the <em>Before Insert</em>  event handler. When the <em>Before Insert</em> handler finishes, the saving process continues. Along the way, the <em>Before Update </em>event fires, offering another opportunity to do something in addition to simply saving the record.</p> <p>The chain of events is quite similar for existing records which are being edited. The only real difference is that the <em>Before Insert</em> event doesn’t fire because we are updating an existing record. The record is already in the table so the memory image of the record doesn’t have to be inserted into the table; the table just has to be updated to reflect the changes you have made to the record’s data. So when you do something to put the save process in effect, the <em>Before Update</em>  event will fire before that table data is actually modified.</p> <p>If you want only a date stamp (without time) the function Date() would do the trick. However if you want the date <em>and </em>time of additions and modifications to be recorded, the Now() function will return a serial date value which it has read from the system clock. The two event handlers are so simple as to appear almost trivial:</p> <h5></h5> <p>Private Sub Form_BeforeInsert(Cancel As Integer) <br />    Me.DateCreated = Now <br />End Sub </p> <p>Private Sub Form_BeforeUpdate(Cancel As Integer) <br />    Me.DateModified = Now <br />End Sub</p> <p>That’s all it takes. With these two form events in place, every time a new record is created using the form, the record’s DateCreated value will be set to the date and time the user started typing data. Similarly, when the form is used to edit data the, the record’s DateModified value will be set to the time that the changes are saved. An interesting side effect of using this approach is that new records have values assigned to both fields. The difference between the DateCreated and DateModified values indicates how long it has take the user to enter data into the new record.</p> <p>Keep in mind that, while this approach might appear to be the basis of an audit trail solution, it only keeps track of record creation and last modification date and time. If you want to maintain modification history data you should use a separate, related table with a new record appended to it for each record creation/modification/deletion action.</p> <p>Download the sample database from <a href="http://cid-b85eaaab59f9c0ea.skydrive.live.com/self.aspx/Examples/DateStamping.ZIP" target="_blank">here</a>. This is a zip compressed file contained the example in both Access 2000 and 2007 format.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-49664921397990940972009-12-21T07:44:00.001-05:002009-12-21T07:44:17.985-05:00Introduction to VBA Debugging<p>Microsoft MVP Walter Niesz has put together a nice FAQ (Frequently Asked Question) article over at UtterAccess.com to introduce new Access programmers to de-bugging VBA Code. The <a href="http://www.utteraccess.com/forums/showflat.php?&Board=53&Number=1916139&page=0&view=collapsed&sb=5&o=&fpart=1" target="_blank">article</a> illustrates three basic debugging techniques that should be in every VBA programmer’s toolkit. If you are new to VBA or Access have a look at <a href="http://www.utteraccess.com/forums/showflat.php?&Board=53&Number=1916139&page=0&view=collapsed&sb=5&o=&fpart=1" target="_blank">Debugging Techniques</a>. While you are at <a href="http://www.utteraccess.com" target="_blank">UtterAccess.com</a> have a look around at the thousands of opportunities to learn from other’s questions and to find answers to your own questions. (disclaimer: as a forum administrator at UtterAccess I enthusiastically support the forum which is, “without doubt <b>is the only</b> source for all of your Microsoft Access help needs...<b>and more!</b> ") </p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-89649491658208812632009-11-14T18:06:00.001-05:002009-11-14T18:06:03.369-05:00Access 2010 Navigation<p>Check out this <a href="http://blogs.msdn.com/access/archive/2009/11/14/build-navigation-ui-with-access-2010.aspx" target="_blank">Access Team Blog article</a> in which <i>Wouter Steenbergen</i> describes new navigation features in Access 2010 in detail </p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-9539838642907670412009-10-29T04:50:00.001-04:002009-10-29T04:50:27.678-04:00John R. Durant's WebLog : Why VBA Still Makes Sense<p>This article, <a href="http://blogs.msdn.com/johnrdurant/archive/2009/09/07/why-vba-still-makes-sense.aspx">John R. Durant's WebLog : Why VBA Still Makes Sense</a> , presents a solid case  for why VBA is the ‘next level’ when it comes to application development in Access and other members of the Office suite. John provides an excellent of VBA since its early days to the present and on into Office 2010 including his perspective on the ‘fit’ between .NET and VBA. <a title="John R. Durant's WebLog : Why VBA Still Makes Sense" href="http://blogs.msdn.com/johnrdurant/archive/2009/09/07/why-vba-still-makes-sense.aspx">John R. Durant's WebLog : Why VBA Still Makes Sense</a> is definitely a worthwhile read.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-15670759968771718662009-10-20T07:36:00.001-04:002009-10-20T08:16:53.229-04:00Breaking News – Major Step Forward in Access 2010<p>Ryan McMinn and Clint Covington demonstrate the most significant new Access feature being introduced in the 2010 version View the latest <a href="http://blogs.msdn.com/access/archive/2009/10/19/access-web-databases-and-the-access-show.aspx" target="_blank">Office Team Blog article</a> and click the link to their video.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-86287581776730848702009-10-10T12:41:00.001-04:002009-10-10T12:41:26.813-04:00We Are Moving<p><font size="3"><strong>to </strong></font><a href="http://www.officetipsandmethods.com"><font size="4"><strong>www.officetipsandmethods.com</strong></font></a> </p> <p>A little over a year ago I started two blogs, <a title="Argee's Office Help Blog" href="http://argeeoffice.spaces.live.com/">Argee's Office Help Blog</a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUvNvZOIN_PSVURkaZusHRtCjMd-_SG7ZxnXyD8O8nsSNGIduOE6Rz-oCVtZhAMKNVB824c6RfrKj7QUj4js0NFPm2EtvJWS4VONEFHbMtdG32isCCAfcHxSpjcmReik6-zyYpIA/s1600-h/Moving72.gif"><img style="border-right-width: 0px; margin: 20px 0px 20px 20px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" title="Moving" border="0" alt="Moving" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhSrHXajQVxQOkuRAQfu-LQXiOlNTpg1GotXEDA8wDmfQO0M94n0L0PMwUx99nvhPetXdiaQt7tIJlG8zAeLGtXUupH-Xv2FN5uaS-HtzZaWjW11eX8y_Vg5rstaVtsusF5SUbLuw/?imgmax=800" width="241" height="216" /></a> and <a title="Argee's Access Blog" href="http://argeedblu.blogspot.com/">Argee's Access Blog</a>. As the titles suggest, the first focuses on entire Microsoft Office applications except for Microsoft Access. The second is aimed strictly at Microsoft Access related topics. I found a number of free blog hosts and started my first OfficeHelp, a Windows Live Site. After publishing a few articles there, I wanted to be able to separate Access oriented articles from those related to the rest of the Office suite. Windows Live blogs are great for personal/social blogs but I couldn’t find a way to classify or categorize articles with more that a single category/tag for each article.</p> <p>Visiting a few blogs by my Access colleagues led me to consider the Blogger site which is hosted by Google. I decided to locate my Access site there. Each article can have multiple tags. That makes it easier to specifically classify articles so that a reader can find specific topics more quickly.</p> <p>Recently, I have come to realize that I really wanted an even more sophisticated classification system than what is possible on the Blogger site. Part of this need stems from the significant change that Microsoft introduced into user interfaces with Office 2010. These changes were particularly significant in Access, my main area of interest. A little Internet searching led me to the WordPress publishing platform. </p> <p>Will WordPress solve my classification problem? It certainly seems so, and then some. I found that I can set up custom categories and sub-categories. That means that I can flag an article as relating to Access generally, to Access 2007, specifically, and that the article is part of a tutorial. I can also tag each article with one or more tags that will aid in subject oriented searching. Best of all, I found I can include a category search capability in a side bar that is always there at the top of the blog page.</p> <p>WordPress may not be the last word in meeting my blogging needs but so far I like the look and feel. Now it’s time to get back to writing. For the time being I will mirror articles that I publish at OfficeTipsAndMethods on my OfficeHelp and Access blogs so if you have subscribed to one or both of those feeds you will still be notified of new articles in those blogs. But if you want a “one feed serves all” feed, visit</p> <p align="center"><strong><a href="http://www.officetipsandmethods.com"><font size="4">www.officetipsandmethods.com</font></a><font color="#336699" size="4"> </font></strong></p> <p>and subscribe to the rss feed there.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-47060763149173981422009-09-12T11:03:00.001-04:002009-09-12T11:03:00.444-04:00Coding Standards Survey – The Importance of Comments in VBA Code<p><a title="Abacus" href="http://www.toonaday.com/" target="_blank"><img style="border-bottom: 0px; border-left: 0px; margin: 5px 10px 5px 0px; display: inline; border-top: 0px; border-right: 0px" title="Abacus cartoon" border="0" alt="Abacus cartoon" align="left" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx_vbwruRh-cEtEu3fYZXXEfCroUc5kv5QLJrEQkuw5RZ9zDz79YZnDIckho05UupHnubmwUcc4W3UrlD3hkXJlfAzl3hQJTpeRt_BjDvhbbG7ZKTEXTrv6dDe7wirYtswtrz4Vg/?imgmax=800" width="218" height="216" /></a> At the end of August, a discussion at my home away from home, UtterAccess.com piqued my interest in the topic of how experienced Access developers viewed the importance of commenting their VBA Code. I decided to conduct a brief survey of two groups, Microsoft MVPs, and UtterAccess VIPs. I specifically notified Access and Excel MVPs as well as the VIP group at UtterAccess.</p> <p>While there weren’t quite as many opinions as their were people completing the survey, there certainly is a broad range of opinions.</p> <p>Dennis, an UtterAccess member who was unable to complete the survey because he was away while it was open for response posted a forum message that pretty well sums up my own position on the question. He said in part,</p> <p>“<em>Commenting code was described by one of my college instructors as one of the most important, but least prioritized steps of the programming process. And I believe he was (and is) 100% correct. <br />One should never assume that the purpose of a block of code is self-evident. Even if you've used the same module over and over, that module should be documented so that someone (even you) in the future will quickly be able to see HOW and WHY a block of code is doing what it does. <br />Commenting costs nothing to compiled code, and is worth it's weight in gold to people charged with improving and re-using code. After all, the cliché says "time is money" and the amount of time saved with a few lines of text within a block of code can be enormous. <br />There, that's about it, in a nutshell</em>.”<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg2wkK5skoE9SLPd4K1uN4Pq-7-ptD38QoCPKaOAzZk8LyochPlk_0zHCrGkNN5fWC8HhQdYxTUZIZLz5CDsgK3afqIQwMJIbUYeXyFFXuqDUEvAVHjkTKdypTjHjDa4vr1RvvdVA/s1600-h/Comments%20Graph%5B3%5D.png"><img style="border-bottom: 0px; border-left: 0px; margin: 0px 0px 0px 10px; display: inline; border-top: 0px; border-right: 0px" title="Comments Graph" border="0" alt="Comments Graph" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwpS0_S1Ays0Hfkm5UCJ29BIAJe-txbvc816b7oE_jbUZmw_66hx7pEVi-xefmwAMU6yBjSKboNaBjt_whlpY7d-S8ibNGYAtHdVoZXASTiRcrhlHuq5Ozybc-sPNXivFhgxYG1Q/?imgmax=800" width="244" height="166" /></a></p> <p>I should mention that I did not submit a response to the survey myself; I wanted to leave the answers to others and let the results fall where they may. The results were not particularly startling. They do reflect a broad range of opinion among the respondents. More than half of the 57 people answering the survey rated commenting as being either very important or absolutely essential. </p> <p>The numerical results, however, only tell part of the story. The survey included two open-ended questions, ‘<em>Briefly describe how you use commenting in your own work and the depth of detail you include in your comments.’ and ‘What would you describe as the single most important reason for including comments in programming code?’</em></p> <p>A common theme for the first set of open-ended responses was, “to help me remember what I did and why.” Some of these comments expanded the theme to include “others who might have to follow-up on my work.”</p> <p>In response to the second open-ended question, one person put it this way, “<em>1stly To remind me of what I wrote and how the code works...I sometimes don't view project for long periods of time. 2ndly so that if or when I expire someone else can take up where I left off.”</em></p> <p>There were a few problems with the survey, some caused by my oversight when I designed it and some caused by the <a href="http://www.survey.com/" target="_blank">the survey software</a> itself. Two people reported either being unable to submit their responses on the the survey site or being unable to edit/include comments that were greater than 255 character. I won’t complain too loudly about the survey site, however, since I used the free survey tool that is available if you register on the site.</p> <p>As to survey design issues, I neglected to include a ‘none’ category in the question about programming languages other than VBA. That cause the ‘other’ response to be somewhat ambiguous.</p> <p>All in all, however, I’d have to say that devising and implementing the survey and analyzing the results has been an interesting exercise. I have a few other ideas that I may try in the future. The full survey report is available in pdf format <a href="http://cid-ae620b14fbb9608f.skydrive.live.com/self.aspx/Public/Survey%20Results.pdf" target="_blank">here.</a></p> <p>I would like to publicly thank the UtterAccess VIPs and my fellow MVPs who took the time to respond to the survey.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-69793438966214432192009-08-27T05:41:00.002-04:002009-08-27T06:02:23.102-04:00Don’t be fooled!!<p></p><p>I was quite surprised yesterday to see reference to an article I wrote several years ago, <a href="http://vb123.blogspot.com/2009/08/data-modeling-for-access-newcomer-part.html" target="_blank">Data Modeling for the Access Newcomer</a> is an internet newsletter. I am not going to name the newsletter here because I don’t want to give it any free publicity. You won’t find it by links that I provide.</p><p>The particular newsletter follows the questionable practice of posting teasers about articles with links that lead to a sign-up page for their paid service. Once you have signed up, chances are all you will find is yet another link, this time to another site where the piece was available to you free of charge all the time. So you have just paid for, or at least signed up for a trial of, a service that frequently simply provides you links to pages and sites that a freely available without ever going near their service. If it was a free trial, you can bet they will then be soliciting you for a paid subscription. Definitely don’t buy the subscription unless you feel you are getting real value for your money.</p><p>In the case of my article, the full text of the article is freely available in Garry Robinson’s excellent VB123 <a href="http://vb123.blogspot.com/" target="_blank">blog</a>. Garry recently purchased rights to the best years of Smart Access magazine (the years when Peter Vogel was the editor.) You can purchase electronic versions of sets of the magazine from Garry’s <a href="http://www.vb123.com/smart/" target="_blank">vb123 site</a> if you are interested in a set of excellent articles that spanned the years from October 1996 to April 2006. That is almost 10 years of monthly issues with articles by hundreds of Access experts.</p><p>If you are just interested in my article, this link <a href="http://vb123.blogspot.com/2009/08/data-modeling-for-access-newcomer-part.html" target="_blank">Data Modeling for the Access Newcomer</a>, will take you to the full text of the article. Garry is very generously publishing the full text of individual Smart Access articles in his blog. He has a convenient <a href="http://www.vb123.com/kb/related_topics.htm">index</a> to the Smart Access articles.</p><p>There is an old expression that says, “Why buy the cow, when you can get the milk free?” While the expression can have a somewhat off-colour meaning, depending on its context, I think it deserves a paraphrase here, “Why pay for a link when you can get to the real article free?” If someone is trying to sell you links, use your favourite search engine to find your own links. It won’t cost you a cent.</p>Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-71536100485666688242009-07-26T09:58:00.002-04:002009-07-26T13:11:46.520-04:00Creating Tables<p> </p><p>In the <a href="http://argeedblu.blogspot.com/" target="_blank">last installment</a> we explored the many to many relationships that exist between our various table. At that point we had not yet created the tables. This article will deal with the nuts and bolts of actually creating a table.</p><h4>A word about names</h4><p>The names you use when you create your database objects are important and should follow certain guidelines. Access reserves certain words for its own use. Logically these words are known as <em>reserved words</em>. Unfortunately some of these words are the first that comes to mind when you are trying to name fields for example. In <a href="http://argeedblu.blogspot.com/2009/04/tables-for-ice-cream-parlor.html" target="_blank">this article</a> I discussed at a conceptual lever, the tables we need in the Ice Cream Parlour database. If you look back to that article you will see names like ‘Name”, “Description”, and “Type”. These words all have special meaning within Access and should not be used to name Access objects.</p><p>So you will notice that the field names in the relationship diagrams in my <a href="http://argeedblu.blogspot.com/2009/07/connecting-dots-well-main-tables-anyway.html" target="_blank">last article</a> are different from those I used in the conceptual diagrams. So, for example I have a field ‘strSundaeName’ rather than simply ‘Name’. What about the ‘str.’ That part of the name comes from a convention I follow to add a prefix to the name that indicates the datatype of the field. Many Access developers do not use prefixes for field names. So <em>SundaeName</em> would be perfectly acceptable and, in fact, preferred by many developers.</p><p>On the other hand, many developers do follow a naming convention that prefixes the names of Access objects like tables, queries, forms and reports, with three characters to signify the type of object to which the name pertains. So, for example, the Sundaes table is actually called ‘tblSundaes’.</p><p>Notice also that tables are usually plural. That is because the table contains records where each record is one example (or instance) of the subject of the table. So, tblSundaes has fields with names like <em>strSundaeName </em>(or <em>SundaeName, </em>if you prefer.)</p><p><strong>Creating a table</strong></p><p>There are several ways to create a table in Access. Although I don’t recommend it, you can design a table by entering data into a datasheet. Access will determine the data type for each field based on the data you enter. And unless you are careful to name each field, Access will assign such helpful field names as <em>Field1, Field2, </em>etc.</p><p>In Access versions prior to 2007 there are table creation wizards and in 2007 there are table templates to help you out. Unfortunately, tables created by wizards or from Access 2007 templates have field names that do not follow accepted naming standards. They include spaces in the field names. Now, while this may seem perfectly natural to you if you are an Access beginner, those spaces will come back to haunt you when you decide to enhance your work using advanced features like Visual Basic for Applications (VBA).</p><p>I prefer to create all tables using the Table Design View. Before we go there, however, we should map out the fields we will need and determine what their respective data types should be. For the sake of illustration I am going to deal with creating one of the tables we have decided we need. If you are following along and creating your own ice cream parlour database, the best way for you to learn will be for you to create the other tables on your own. I will be providing a link to my version of the database with all tables created.</p><p>Before we look at the table designer, let’s summarize the information we will need for each table. I am indicating two possible names for each field. The first name follows the naming convention I prefer to use. The second is a valid name in a format that omits the data type prefix. Whichever style you choose, it is important to be consistent throughout the database.</p><p><strong><strong>Sundaes - </strong>tblSundaes </strong></p><table border="0" cellspacing="0" cellpadding="2" width="658"><tbody><tr><td valign="top" width="91"><p align="center"><strong>attribute<br />(conceptual name)</strong></p></td><td valign="top" width="129"><p align="center"><strong>field name</strong></p></td><td valign="top" width="139"><p align="center"><strong>alternate name</strong></p></td><td valign="top" width="81"><p align="center"><strong>data type</strong></p></td><td valign="top" width="58"><p align="center"><strong>data size</strong></p></td><td valign="top" width="158"><p align="center"><strong>notes</strong></p></td></tr><tr><td valign="top" width="91">Primary Key</td><td valign="top" width="129">idsSundae</td><td valign="top" width="139">SundaeID</td><td valign="top" width="81">Autonumber</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Name</td><td valign="top" width="129">strSundaeName</td><td valign="top" width="139">SundaeName</td><td valign="top" width="81">Text</td><td valign="top" width="58">25</td><td valign="top" width="158"> </td></tr></tbody></table><p> </p><p><strong>Dishes - tblDishes</strong></p><table border="0" cellspacing="0" cellpadding="2" width="658"><tbody><tr><td valign="top" width="91"><p align="center"><strong>attribute<br />(conceptual name)</strong></p></td><td valign="top" width="129"><p align="center"><strong>field name</strong></p></td><td valign="top" width="139"><p align="center"><strong>alternate name</strong></p></td><td valign="top" width="81"><p align="center"><strong>data type</strong></p></td><td valign="top" width="58"><p align="center"><strong>data size</strong></p></td><td valign="top" width="158"><p align="center"><strong>notes</strong></p></td></tr><tr><td valign="top" width="91">Primary Key</td><td valign="top" width="129">idsDish</td><td valign="top" width="139">DishID</td><td valign="top" width="81">Autonumber</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Name</td><td valign="top" width="129">strDishName</td><td valign="top" width="139">DishName</td><td valign="top" width="81">Text</td><td valign="top" width="58">25</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Type</td><td valign="top" width="129">lngDishType</td><td valign="top" width="139">DishType</td><td valign="top" width="81">Number</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158">foreign key – used to find the dish type description in the types lookup table</td></tr></tbody></table><p> </p><p><strong>Sizes - tblSizes</strong></p><table border="0" cellspacing="0" cellpadding="2" width="658"><tbody><tr><td valign="top" width="91"><p align="center"><strong>attribute<br />(conceptual name)</strong></p></td><td valign="top" width="129"><p align="center"><strong>field name</strong></p></td><td valign="top" width="139"><p align="center"><strong>alternate name</strong></p></td><td valign="top" width="81"><p align="center"><strong>data type</strong></p></td><td valign="top" width="58"><p align="center"><strong>data size</strong></p></td><td valign="top" width="158"><p align="center"><strong>notes</strong></p></td></tr><tr><td valign="top" width="91">Primary Key</td><td valign="top" width="129">idsSize</td><td valign="top" width="139">SizeID</td><td valign="top" width="81">Autonumber</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Name</td><td valign="top" width="129">strSizeName</td><td valign="top" width="139">SizeName</td><td valign="top" width="81">Text</td><td valign="top" width="58">25</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Type</td><td valign="top" width="129">lngSizeType</td><td valign="top" width="139">SizeType</td><td valign="top" width="81">Number</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158">foreign key – used to find the dish type description in the types lookup table</td></tr></tbody></table><p> </p><p><strong>Flavors - tblFlavors</strong></p><table border="0" cellspacing="0" cellpadding="2" width="658"><tbody><tr><td valign="top" width="91"><p align="center"><strong>attribute<br />(conceptual name)</strong></p></td><td valign="top" width="129"><p align="center"><strong>field name</strong></p></td><td valign="top" width="139"><p align="center"><strong>alternate name</strong></p></td><td valign="top" width="81"><p align="center"><strong>data type</strong></p></td><td valign="top" width="58"><p align="center"><strong>data size</strong></p></td><td valign="top" width="158"><p align="center"><strong>notes</strong></p></td></tr><tr><td valign="top" width="91">Primary Key</td><td valign="top" width="129">idsFlavor</td><td valign="top" width="139">FlavorID</td><td valign="top" width="81">Autonumber</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Name</td><td valign="top" width="129">strFlavorName</td><td valign="top" width="139">FlavorName</td><td valign="top" width="81">Text</td><td valign="top" width="58">25</td><td valign="top" width="158"> </td></tr></tbody></table><p> </p><p><strong>Ingredients - tblIngredients</strong></p><table border="0" cellspacing="0" cellpadding="2" width="658"><tbody><tr><td valign="top" width="91"><p align="center"><strong>attribute<br />(conceptual name)</strong></p></td><td valign="top" width="129"><p align="center"><strong>field name</strong></p></td><td valign="top" width="139"><p align="center"><strong>alternate name</strong></p></td><td valign="top" width="81"><p align="center"><strong>data type</strong></p></td><td valign="top" width="58"><p align="center"><strong>data size</strong></p></td><td valign="top" width="158"><p align="center"><strong>notes</strong></p></td></tr><tr><td valign="top" width="91">Primary Key</td><td valign="top" width="129">idsIngredient</td><td valign="top" width="139">IngredientID</td><td valign="top" width="81">Autonumber</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Name</td><td valign="top" width="129">strIngredientName</td><td valign="top" width="139">IngredientName</td><td valign="top" width="81">Text</td><td valign="top" width="58">25</td><td valign="top" width="158"> </td></tr><tr><td valign="top" width="91">Type</td><td valign="top" width="129">lngIngredientType</td><td valign="top" width="139">IngredientType</td><td valign="top" width="81">Number</td><td valign="top" width="58">long<br />integer</td><td valign="top" width="158">foreign key – used to find the dish type description in the types lookup table</td></tr></tbody></table><p> </p><p>Those are the basic definitions of our five central table. As already noted, we will need additional tables in the database to allow us to connect these central tables together so that the database reflects the one to many relationships between them. We will also need a lookup table for <em>types</em> the type descriptions used by tblDishes, tblSizes, and tblIngredients.</p><p>Coming next: Using the table designer.</p>Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-79406337905290097742009-07-23T05:20:00.001-04:002009-07-23T05:20:06.089-04:00Access 2010 Preview<p> </p> <p>While an actual release date is still to be announced, the next version of Office and Access are looking pretty exciting. For an overview of what’s coming in Access, have a look a the <a href="http://blogs.msdn.com/access/archive/2009/07/22/access-2010-the-10-000-ft-view.aspx" target="_blank">Access team blog</a>.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-60995028978468629072009-07-09T05:49:00.001-04:002009-07-09T05:49:15.564-04:00Connecting the dots – well the main tables, anyway<p> </p> <p>Thanks to grovelli who prodded my about the next installment in the Ice Cream Parlour tutorial, here it is.</p> <p>When we <a href="http://argeedblu.blogspot.com/2009_04_01_archive.html" target="_blank">last looked</a>  at the ice cream parlour database, we had identified the main tables of the database:</p> <ul> <li><strong>sundaes</strong> </li> <li><strong>dishes</strong> </li> <li><strong>sizes</strong> </li> <li><strong>flavors</strong> </li> <li><strong>ingredients</strong> </li> </ul> <p>For now, let’s focus on how to connect these tables together rather than on the contents of each field. You may recall that we identified the relationships between most of the pairs of tables turned out to be many to many. That is, for example, one sundae may use many ingredients and each ingredient can be used in more than one sundae.</p> <p>To keep it simple I am going to focus on just this one pair of tables, sundaes and ingredients. Let’s say, for the sake of argument, that we have come up with a recipe for what we are going to call <em>Chocoholic’s Mega Gooey Sundae Delight. </em>Because we have chosen to use autonumber primary keys when we enter this recipe title in the Sundaes table access assigns 621 as the primary key. Now, if we need to refer to our Mega Gooey Sundae Delight outside of the table all we (or Access) need to know is that this particular Sunday is number 621 in our table.</p> <p>Actually the end user does not need to know the value of the primary key at all. There are ways of getting the value and causing it to be entered in other tables without actually having to actually know the number.</p> <p>So far, so good. Now the Mega Gooey Sundae Delight recipe calls for these ingredients </p> <ul> <li>Chocolate Ice Cream (832) </li> <li>Chocolate Sauce (365) </li> <li>Chocolate Sprinkles (798) </li> <li>Marshmallow Sauce (922) </li> <li>Toasted Almond Slivers (305) </li> </ul> <p>After each ingredient name, I have shown for the sake of this illustration, the primary key that Access assigned to each of these ingredients when they were added to the ingredients table. The actual number doesn’t matter. What is important is that no two ingredients share the same number. To put it in Access’s terms, the values are <em>unique</em>. That means that we can be confident that whenever we refer it item 922 in the ingredients table, we are talking about Marshmallow Sauce.</p> <p>Here is the relationship diagram for these two tables:</p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeVZi6zCh-LghAKSRMPvfmQw7hZOEETgd58CU0P0LGU9C_wlaiurh9GOZEFpzjflHQXOKU7wJ7YUyr8dV0zj2p9kpQ2DyqWgdH7k9e1GOOjd2fCYLRynX4qR7LyHEeIM7FtMK-mw/s1600-h/image%5B3%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh31qk00WBHwNCtAKy9LU03i1PakVxp9TU1tqGOmcRriOOhUSSGycietLYLFarlojAQ7UWX8VIeX4lx7tDLX27diAJjHpaaW7YZB5HPoDQ0srCA1KJKTPPVP75M3Ju4ynAxsM4YaQ/?imgmax=800" width="356" height="193" /></a> </p> <p>The junction table I have been describing is tblSundaeIngredients. The infinity symbol indicates that that side of the relationship is ‘many’, meaning that each sundae in the tblSundaes can have many corresponding records in the junction table. Similarly, each ingredient can have many corresponding records in the junction table as well.</p> <p>I will go through the process of actually creating the tables and relationships in later installments of this series of articles. For now, I just want to focus on what things look like when we bring together a sundae with its ingredients using a junction table.</p> <p></p> <p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi5KcWM5cjSI4greR2k7Pg9Zf0b3EDERkgtm-TFBteyNvFMA4JbgK3tcFSzQntm90wOvuDEfxnSS1EN3dZbgkeL11IxzRTQ1d2r3tWHIXRNQo2WZ886WuhP7_-zFOgiIufMx6efdQ/s1600-h/image%5B7%5D.png"><img style="border-bottom: 0px; border-left: 0px; display: inline; border-top: 0px; border-right: 0px" title="image" border="0" alt="image" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgzqySQYdUQMvxihl6JAY8q8p5MkBrO7mFhJRr1IEJAetZYZK20QvQ6hCZi8xyfiLhV_f0MpK_UWDi_lLz11oGomn5GtJ7hwJLOL7CUFykt2SJnAjCq3dMMSHIxMwSbekf4KFTtlw/?imgmax=800" width="479" height="224" /></a> </p> <p>To keep things simple, I have included in the diagram only the fields and records necessary to illustrate the point. Notice how the junction table includes only numbers and those numbers are the same as the primary keys of each of the ingredients in tblIngredients. This is typical of tables that are related. The fields lngIngredient and lngSundae are known as <em>foreign keys. </em>In other words the values in those fields tell us what values we are talking about in the other (or <em>primary</em>) table.</p> <p>Next time, we will take a look at actually creating these Access tables. Following that, we will look at the process for creating relationships.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com1tag:blogger.com,1999:blog-11078501.post-6750919253672763902009-07-02T15:47:00.003-04:002009-07-04T07:51:21.779-04:00Great News - I have been recognized as an MVP for another year<a href="http://tkfiles.storage.msn.com/y1pKB0PZru5wNau9bxcUvYwegvVTeNnzxChvd4_e_tZtPKjI67ns0k5kA?PARTNER=WRITER"><img style="MARGIN: 0px 10px 10px 0px" border="0" alt="MVP_FullColor_ForScreen" align="left" src="http://tkfiles.storage.msn.com/y1phL7R9cnOxrEinTZbZdW6jMxOfeTSxixSgt2KE5-KR7kvMEpOjaKSc-qGSB4uWigfHEJXIZe53hI?PARTNER=WRITER" width="79" height="122" /></a> <p>Yesterday, July 1, I received the notification that I have been recognized as a Microsoft Most Valuable Professional for the third year in a row. This is indeed a great honour, considering that there are fewer than 5,000 MVPs world-wide. One of the benefits of being an MVP is that we have a designated person (our MVP lead) at Microsoft) with whom we can correspond on a personal basis. You can read more about the MVP program on the <a href="http://mvp.support.microsoft.com/">MVP website.</a></p>Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com5tag:blogger.com,1999:blog-11078501.post-74528235264272865332009-04-01T10:44:00.001-04:002009-04-01T10:44:29.614-04:00Tables for the Ice Cream Parlor<p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOWqnt4VSqUHmJuOVTRkGgQaiKzHeNx5A3hsXl-JfDxSB300sz8PV8fdUSE2zS-pvC5gYY_eQqdnNSTRqv11pZIfQqKk8eRSuF8fy3tL_XMSY-zj5nSGBDVEsoB-xEZ-FOZF_yQQ/s1600-h/image3.png"><img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="image" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhqmak1gFJVhhdO03HMYQlEhBUlHGNSUgArrgqG8Lx_JL__So8Xw1JKS4coooKJMayvHg6_d2ys_giT3FdlIyA232aooI3dfsIHFmflE8guAJ86UThgrZXwn7fRN6YSp_eQMzr2qA/?imgmax=800" width="244" height="244" /></a> </p> <p> </p> <p>Just as a real ice cream parlor needs tables where our patrons can sit and enjoy out products, our database model of ice cream sundaes will need tables in which to store information about our products.</p> <p>In <a href="http://argeedblu.blogspot.com/2009/02/let-build-some-tables.html" target="_blank">the previous installment</a> of this series, we fleshed out the list of tables database will need. These are our main tables:</p> <ul> <li>ingredients </li> <li>dishes (containers) </li> <li>sizes </li> <li>sundaes </li> <li>flavors </li> </ul> <p>Because we will be combining ingredients in various ways with various container sizes and ingredients actually come in several flavors, we will need some additional tables to help us model the combinations. These tables will include:</p> <ul> <li>sundae ingredients </li> <li>ingredient flavors </li> <li>sundae sizes </li> <li>container sizes </li> <li>sundae containers </li> </ul> <p>Now it is time to determine what fields to include in each table. You may be starting to find this list just a little overwhelming. Try not to panic. It is really not all that bad as long as you tackle things a little bit at a time. So we will move ahead, slowly but confidently, one table at a time. For now, let's focus on the five central tables mentioned above. </p> <p>Table definition refers to identifying the fields that are needed to describe the subject (or domain) of each table. So when I think about a table, I start to think about the kinds of information I need to describe the subject of the table.</p> <p>We will keep these basic guidelines in mind:</p> <ul> <li>each table describes only a single subject (entity) </li> <li>the fields included in each table should describe the subject of the table (attributes). </li> <li>data will be subdivided across multiple tables in order to <ul> <li>avoid duplication of data, and </li> <li>avoid repeating groups of columns </li> </ul> </li> </ul> <p>I find that, in a database as focused on a single business solution as the ice cream sundae database is, a single table emerges as the central table at the heart of the application. Other tables in the database are needed only because they will have information that pertains to the central table. </p> <p>So, for us, the central table appears to be the <em>sundaes</em> table. Let's start there. What information do I need to store to describe each sundae? Obviously each sundae needs a name. It will also need ingredients and, since I plan to offer each sundae type in a variety of sizes, it will need size information.</p> <p>However, storing size and ingredient data in the main sundaes table will lead to immediate design problems. While I might be certain ahead of time exactly how many sizes I will be offering and the maximum number of flavors might be used in a sundae, including that information directly in the sundaes table will break one of our basic guidelines. I would have to have fields like <em>flavor1, flavor2, flavor3, ingredient1, ingredient2, ingredient3.</em> In database terms these would be examples of <strong>repeating columns.</strong></p> <p>So, what's the problem? Although not apparent at the outset of planning, repeating columns make retrieving useful information from stored data, unnecessarily complex. Even more important, repeating columns put serious limits on the future use of the database. No matter how certain you are ahead of time that you are describing how something is now and will ever remain the same, it is just not that easy to predict the future. If I "know" that I will only ever offer three sizes of sundae, small, medium, and large, the database (if I have included three size fields in the sundaes table) would not be able to handle changes I might have to make in order to remain competitive. Say, for example, that upstart competitor down the street starts stealing my customers because she has added a fourth or fifth size.</p> <p>I have tried to describe the process by which I have determined what fields belong in the sundaes table. The process for deciding what fields to include each of the other main tables is the same. The following table shows the results of this analysis:</p> <table border="0" cellspacing="0" cellpadding="2" width="436"><tbody> <tr> <td valign="top" width="91"><strong>sundaes</strong></td> <td valign="top" width="83"><strong>dishes</strong></td> <td valign="top" width="81"><strong>sizes</strong></td> <td valign="top" width="88"><strong>flavors</strong></td> <td valign="top" width="91"><strong>ingredients</strong></td> </tr> <tr> <td valign="top" width="90">Name</td> <td valign="top" width="82">Name</td> <td valign="top" width="80">Name</td> <td valign="top" width="88">Name</td> <td valign="top" width="91">Name</td> </tr> <tr> <td valign="top" width="90"> </td> <td valign="top" width="81">Type</td> <td valign="top" width="79">Type</td> <td valign="top" width="88"> </td> <td valign="top" width="91">Type</td> </tr> <tr> <td valign="top" width="89"> </td> <td valign="top" width="81">Description</td> <td valign="top" width="79">Description</td> <td valign="top" width="88"> </td> <td valign="top" width="91"> </td> </tr> </tbody></table> <p></p> <p>You may find that the list of fields in each table is somewhat short. Each table includes a name field. The sundaes and flavors tables don't have any other fields. That's because most of how we describe sundaes, for example, is stored in other tables. Flavor names, on the other hand are pretty much self-descriptive.</p> <p>The dishes, sizes and ingredients tables have type fields. We will use these fields to categorize dishes, sizes and ingredients, respectively. Dishes may be either edible, disposable (plastic for takeout) or reusable (glass or metal for eat-in.) Sizes may refer, for example to quantity when referring to ingredients, to capacity when referring to dishes, or to the end-product size description (small, medium, large, for example) when referring to sundaes. Ingredients may be principal ingredients, toppings, or garnishes, for example.</p> <p>Now we have a working definition of our main tables. In the next article we will define the additional tables we will need to flesh out the connections needed between the main tables.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-62294515668626153882009-02-15T08:29:00.001-05:002009-03-25T17:00:14.733-04:00Let's Build Some Tables<p><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWnDlxyzhyphenhyphenuqazyh_MFjxfQVMLVNklFz-yP59oiUeqxA0K7xrzaXxremYd6LglQl2ffqCnU5FZgSp0TwzveHLZ9grsYITZfDEfZ3NsDYvD9pR7O2EDh_-3CE5xR50X_PIupQgWZw/s1600-h/image%5B1%5D.png"><img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="image" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTbmbYjKHybASpGLYeQ6nDNH8w921cNQTO3-lIXZ5DR2ra4CIEX8w2GwrnwZQVo3lZFDMa5S8jEX1POzlx_3PL8ypf3dCfjQ13jAOVFrIyx35LSH5dk0hbEint4y4tgwVooYZtQA/?imgmax=800" width="191" height="109" /></a>In the <a href="http://argeedblu.blogspot.com/2009/02/dr-phil-time-relationships.html" target="_blank">last article</a> we looked at the relationships that exist between the various  products we will be using to create ice cream sundaes. These 'real world' relationships dictate the relationships we will need to establish between the tables of the database.</p> <p>In this article we are going to look at what information we should be including in each table. Before doing that however, there is at least one more table that we should include in the database, <em>flavors. </em>Since each flavor can apply to many ice creams and some ice creams have multiple flavors, the <a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4HVssThoqjggqCYuehR64cssZ4oN4VYR71zcVlUB4cwfDeXNujMeZyHLbh0rg1sV8ICRsxkM1V4R3-pqkqmAmMY_IyHjN6q-vOkwmWP_Ik_f9LvhDU2PC_4YtvdbhvB9XO02oSQ/s1600-h/image7.png"><img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="image" align="left" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmRAgPd1g_dFEBDy_P8739lRo3GMMu6XpgEtmYbeLBrg6LmKoGs_xQ7m6r8Pkhcc9EtmAbKRFYfFA1C_taC75dfzQEIGFKsQ-sIu1RBazrFqKZsjy89pH9yPljziLau-DM77-nFg/?imgmax=800" width="114" height="88" /></a>relationship between ingredients and flavors is many to many. It is possible that some other ingredients may be multi-flavored as well but one multi-flavored ingredient is enough to make the relationship many to many.</p> <p>It's important to define the relationships before finalizing table design because one to many and many to many relationships require different table structures. For a one to many relationship, you include one field in the 'many' table to identify the 'one' table record to which each 'many' record belongs. Many to many relationship require this information to be stored in an separate table with one field for each of the partner tables.</p> <p>Guess what!! We have just have just identified the need for several additional tables in our 'simple' ice cream sundae database. We will need one table for each of the many to many relationships. One reason for doing detailed planning and design before actually beginning to build the actual database is to try to uncover and resolve any possible data management problems and to ensure that all the database will include all information necessary to fulfill the database's stated purpose.</p> <p>In <a href="http://argeedblu.blogspot.com/2009/01/know-your-date-part-3.html" target="_blank">Know Your Data (Part 3)</a> we defined the purpose of our ice cream sundae database:</p> <p><em>This database stores information about the quantity of contents used to make ice cream sundaes. It assists with the design of new sundaes and provides reports of the ingredients required to make each type of sundae. </em></p> <p>We have also defined a list of tables that we will need to manage the data:</p> <ul> <li>ingredients </li> <li>dishes (containers) </li> <li>sizes </li> <li>sundaes </li> <li>flavors </li> </ul> <p>Now it is time to decide what data needs to be included in each table. Before we do that we should review some terms and principles required for good relational table design.</p> <ul> <li>Each <em>table</em> should contain information about only one topic or subject (formally known as an <em>entity.</em> </li> <li>All examples of a topic should be included in one table (if you have are dealing with people and occupations, you would have one people table, not separate tables for each occupational group, for example.) </li> <li>Each table has one or more <em>fields</em> that describe the subjects or topics stored in the table. </li> <li>Each table should have a <em>primary key</em> that uniquely identifies each<em> record</em> in the table. (I am going to leave for another time the discussion of the alternative types of primary keys.) </li> <li>A <em>record</em>  is the collection of fields that describe one of the topics of the table. </li> <li>Data should be stored only once. </li> </ul> <p>There is a set of rules known as the <em>forms of normalization</em> that are the guiding principles for relational database design. If you a new to database design, your initial efforts will most likely run afoul of normalization so don't be surprised when the response to questions you may ask an experienced developer about designing forms or reports refers you back to normalization issues in your basic database design.</p> <p>In case you haven't noticed, we have yet to do anything with Access itself. That is because knowing how to use Access is only part of the story in building a successful database for personal or small enterprise use. We will be using Access to build and run the database but we can't build it until we have a solid design.</p> <p>In the next article we will (finally) design our the tables that we will have Access manage for us.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-59527910190773356372009-02-01T06:57:00.001-05:002009-02-01T07:04:50.438-05:00Dr. Phil Time - Relationships<p>In this simple example, we have been developing a database to assist with the design of ice cream sundaes.</p> <p>In the <a href="http://argeedblu.blogspot.com/2009/01/know-your-date-part-3.html" target="_blank">last article</a> in this series, we developed the statement of purpose for the database:</p> <p><em>This database stores information about the quantity of contents used to make ice cream sundaes. It assists with the design of new sundaes and provides reports of the ingredients required to make each type of sundae.</em></p> <p>We also determined that we will need at least four tables if the database is to be properly structured:</p> <ul> <li>ingredients </li> <li>dishes (containers) </li> <li>sizes </li> <li>sundaes </li> </ul> <p>I am going to save deciding the contents of each of these tables for the next article and focus now on how the tables <em>relate</em> to each other. (After all, we want this to be a <strong><em>relational database.</em></strong>)</p> <p>The process to determine the type of relationship that exists between any two tables is quite straight-forward and perhaps a bit tedious. Try not to let the tediousness of the task of getting in the way of doing it. Correctly defining the database's relationships is absolutely essential!!</p> <p>Unlike real-world relationships, in Access there are always exactly two partners in any relationship. You define each relationship by considering one pair of tables. By filling in the blanks in two simple statements, you can make a simple conclusion about the type of the relationship.</p> <p>So we are going to look at pairs of tables until we have determined what the relationship is between each possible pair of tables using these three statements.</p> <ul> <li>Each record in table A can have (zero/one/many) records in table B </li> <li>Each record in table B can have (zero/one/many) records in table A </li> <li>Therefore this is a (one to many/one to one/many to many) relationship. </li> </ul> <p>So here are the pairs of tables and their relationships.</p> <p>With four tables there are six possible relationships but not all tables will share a relationship.</p> <p><strong>sundaes <---> dishes </strong></p> <p>(Just to be clear, the dishes table stores information about types of dishes so the relationship definition deals with how each sundae will relate to dish types.)<img style="border-right-width: 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="image" align="right" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEircS8L5V1j5z838HABs53aymYSSjDmMgmZD2FBAHnV6ndcTw0MWdQd0pR9ZIoAHNddG1tl0rVp4oxnh4xBdRVzPWgKlUe1DbUMAgTDAoAZRQYmiveJUAF8yn6kam7TRKn3WYz7mQ/?imgmax=800" width="75" height="77" /></p> <ul> <li>Each sundae can have <strong>one</strong> dish </li> <li>Each dish can have <strong>many</strong> sundaes </li> <li>Therefore this is a <strong>one to many</strong> relationship </li> </ul> <p><strong>sundaes <---> sizes</strong></p> <ul> <li>Each sundae can have <strong>many</strong> sizes </li> <li>Each size can have <strong>many</strong> sundaes </li> <li>Therefore this is a <strong>many to many</strong> relationship </li> </ul> <p><strong>sundaes <---> ingredients</strong></p> <ul> <li><strong><a href="http://lh5.ggpht.com/_ZR934FKqCZg/SYWOOYLaCVI/AAAAAAAAAB8/0WZI0Uni9Rw/s1600-h/ingredients_image7.jpg"><img style="border-right-width: 0px; margin: 0px 20px 0px 0px; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px" border="0" alt="ingredients_image" align="left" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0JCXAXAwDG5SMc9jAp7En4PxUJgbE03mNqxXrgJmXd0mi11ybOnSb14VCXVuiMkJraQZVo_lxzFviGPZkxQmWUQ_sdurFx7fjoXzKmE93wCMBbMdWqmXLaq6qF4eTq75dwLJAyQ/?imgmax=800" width="68" height="40" /></a></strong>Each sundae can have <strong>many</strong> ingredients </li> <li>Each ingredient can be used in <strong>many</strong> sundaes </li> <li>Therefore this is a <strong>many to many</strong> relationship </li> </ul> <p><strong>ingredients <---> dishes</strong></p> <ul> <li>Each ingredient can have <strong>no</strong> dishes </li> <li>Each dish can have <strong>no</strong> ingredients </li> <li>Therefore there is <strong>no relationship</strong> between ingredients and dishes </li> </ul> <p><strong>ingredients <---> sizes</strong></p> <ul> <li>Each ingredient has <strong>no</strong> records in the sizes table </li> <li>Each size has <strong>no</strong> records in the ingredients table </li> <li>Therefore there is <strong>no relationship</strong> between ingredients and sizes </li> </ul> <p><strong>dishes <---> sizes</strong></p> <ul> <li>Each dish has <strong>one</strong> size </li> <li>Each size could be applied to <strong>many</strong> dishes </li> <li>Therefore there is a <strong>one to many </strong>between sizes and dishes. </li> </ul> <p>Remember that when you are describing relationships, you are not defining the relationship. A relationship is what it is; calling it by another name will not change its essential nature. Your job is to identify accurately the essential nature of each relationship in the database.</p> <p>A couple of things have come to light in this relationship analysis. First, <em>sizes</em> are used in two somewhat different contexts. Sundae sizes refer to the size (small, medium, large) in which the sundae will be marketed. Dish sizes on the other hand refer to the capacity of the dish, how large a sundae it can comfortably contain. The two uses may or may not similar descriptions. A sundae might be described as small medium or large. So might a dish but dishes could alternatively be described in terms of units of measure (grams, ounces, etc.)</p> <p>So it might be worthwhile thinking about an additional table to store the possible units of measure (metrics.) Whether this would be necessary or merely a possibly good idea would depend in part on how many possible units of measures apply.</p> <p>Including a metrics table requires further relationship analysis. Before we can describe the relationship between sundaes and metrics in particular, it is important to be clear what constitutes a sundae. So far it looks as if we have defined a sundae as the combination of ingredients and a particular container. But there is one more factor that distinguishes one sundae from another: size.</p> <p>So a banana split consists of vanilla, chocolate, and strawberry ice cream, banana, chocolate syrup, crushed pineapple, walnuts, whipped cream, and cherries, regardless of whether the sundae is small medium or large. For data management purposes, a small banana split is different from a medium or large banana split and it is the size that distinguishes them from each other.</p> <p><strong>metrics <---> sundaes</strong></p> <ul> <li>Each metric applies to many sundaes</li> <li>Each sundae has one metric</li> <li>Therefore this is a one to many relationship</li> </ul> <p><strong>metrics <---> ingredients</strong></p> <ul> <li>Each ingredient has many metrics</li> <li>Each metric apples to many ingredients</li> <li>Therefore this is a many to many relationship</li> </ul> <p><strong>metrics <---> dishes</strong></p> <ul> <li>Each metric applies to many dishes</li> <li>Each dish has one metric</li> <li>Therefore this is a one to many relationship</li> </ul> <p><strong>metrics <---> sizes</strong></p> <ul> <li>Each metric applies to many sizes</li> <li>Each size has one metric</li> <li>Therefore this is a one to many relationship</li> </ul> <p>Now, you might be asking how can a metric apply to many dishes. That is a perfectly legitimate question simply because I haven't fully defined what I mean by a dish. </p> <p>For this definition, let's look to the real world as it applies to ice cream shop. We have already seen that dishes come in several sizes. In my shop, I intend to have both edible and inedible dishes for each of the sizes. Perhaps, in the future, I might have several types of edible dishes (waffle or chocolate, for example.) It is the combination of size and dish type that distinguishes one small dish from another.</p> <p>Raising that point means that we must think about yet another set of relationships. I'm going to leave that decision aside for the moment.</p> <p>Next time we will take a look at table definitions, what fields each of our tables need.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-69086167309952150862009-01-26T05:43:00.002-05:002009-02-01T07:04:50.439-05:00Know Your Data (part 3)<p>After an unintentional hiatus, I'm back with part three of this discussion of Access database design considerations.</p><p>The first two articles (Know Your Data!) discussed planning for the data you need your database to manage. The third essential component in knowing your data, is understanding how data in the various tables of the database relate to data in other tables (that's why we call it a <em>relational</em> database.)</p><p>However, it's a bit premature to talk about relationships. First we need to have a simple database example and an understanding of the tables it requires. Once we have an understanding of the tables, we can then look at their relationships.</p><p>I'm going to build this example around a simple single purpose database but, regardless of the size and complexity of the database the same basic design principles apply .</p><p>Let's say, for the sake of discussion that I own an ice cream shop.I have decided that I want to use a database to help me design ice cream sundaes that will show me the kinds and quantities of ice cream and other ingredients each particular sundae type requires. This information will help me determine how much of each to stock when I start to sell each sundae.</p><p>The first step in designing any database is to devise a simple yet clear 'statement of purpose' for it. As you build the database keeping the statement of purpose in mind will keep you on track so that the end product will do everything it was supposed to do while, at the same time, does not just grow beyond recognition to do things you did not originally intend. In other words, the statement of purpose helps you focus your work and keep it 'in scope.'</p><p>I like to play a little trick with language when I write a statement of purpose by stating it as if the database already exists. So the statement of purpose for my ice cream sundae database might go something like this: <em>This database stores information about the quantity of contents used to make ice cream sundaes. It assists with the design of new sundaes and provides reports of the ingredients required to make each type of sundae. </em></p><p>Now I can use that simple statement of purpose to begin to decide exactly what kind of data I will need to include in the database. Here's a simple 'brain dump' of what goes to make up an ice cream sundae:</p><table border="0" cellspacing="0" cellpadding="2" width="400"><tbody><tr><td valign="top" width="200"><ul><li>one or more ice cream flavors</li></ul></td><td valign="top" width="200"><ul><li>one or toppings</li></ul></td></tr><tr><td valign="top" width="200"><ul><li>a dish or container</li></ul></td><td valign="top" width="200"> </td></tr></tbody></table><p>While thinking about this list, I realize that I need to include the possibility of having various sizes of sundaes. Since a database is really just a model of the real world, I will need to include sizes in the basic information about each sundae I design. Even though I intend to have a number of sizes, I realize that to keep sundae management simple, I should limit the number of sizes available to three or four.</p><p>I'm now at the third stage of my design where I need to decide on what tables I need. Since I want to restrict the variety of sizes I sell, a table that describes each size might be useful. A table of dishes or containers might also be useful. And what about a table for ice creams and one for toppings.</p><p>Now that just might be one table too many. Aren't ice creams and topping just different ingredients? So let's just have a single table for ingredients. Here is my initial table list:</p><ul><li>ingredients</li><li>dishes</li><li>sizes</li></ul><p>One important table is missing however. That is the sundaes table that brings together all of the information that describes an ice cream sundae, our final product. So we will add one more table:</p><ul><li>sundaes</li></ul><p>In the next article, we refine the database design by determining the relationships between the tables.</p>Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0tag:blogger.com,1999:blog-11078501.post-58839454736828982762008-10-15T05:43:00.001-04:002009-02-01T07:04:50.439-05:00Know Your Data! (Part 2)<p>In my last article, I discussed the idea of recognizing 'things' that are similar (and therefore require their own specific table. The second aspect of database design involves distinguishing things from values that describe things. This part of the analysis helps you decide what fields each table will require.</p> <p>After you have made extensive lists of what you want and need to include in your database, the basic question to ask about each item  one your lists is, "Is this a thing, or the description of a thing?" Just to keep the whole process interesting, many of the descriptions will have their own lists of values which (can you guess?) belong in their own table.</p> <p>Back to the ice cream, meat, and potatoes analogy, food groups are one of the characteristics that can be used to describe foods. Food groups are categories to which each food belongs. I am not a nutritionist or dietitian so at this point I am assuming that each particular food belongs to a specific food group and that no food can belong to more than one food group.</p> <p>If I were designing an actual database of course I would have to verify with a food expert or other research whether my assumption is correct. Is there any food that belongs to more than one food group? The answer to that question is at the heart of the next stage of design, determining the relationships between tables.</p> <p>Staying with determining the values that describe things for the moment (technically these values are known as 'attributes' or 'properties'), clearly understanding and grouping these descriptions is one of the key points in making your database as powerful and flexible as you want and need it to be.</p> <p>For example, if the furniture table described included the name of each type of furniture in the store, you would only be able to search for tables, chairs, sofas, beds, etc. Adding fields to the furniture table that describe each furniture piece will make the database a far more useful tool in the business.</p> <p>So you might include fields like typical room assignment, color, price range, style, and so on. Keep in mind that this understanding is vital to your coming up with a solid design and a database that will stand the test of time.</p> <p>At this point, you might be asking yourself, 'If I need all this information to know my data, where and how do I learn it?' Sure, you may have some general knowledge about the subject of the database is supposed to be dealing with, but how do you ever get to know enough so you can get on with building the database?</p> <p>I gave a clue to how you go about it a few paragraphs back. Ask the experts. The people for whom you are creating the database most likely do not understand data structuring (that's your job and should be your expertise) they do understand their business and what they want to do with the data associated with the business.</p> <p>Whether you are working as an independent database developer or are creating databases as part of your day to day employment, the person or people who requested the database or who will be working with it after you build it are your clients. Recognize that your clients are the subject matter experts for the database. They know the 'what', what the data is about and what they want to do with the data.</p> <p>Your job as the database designer is to come up with the 'how.' How can all this data be best organized so that mountains of raw data can be turned into gems of information that will help your clients manage and grow their business. So look to you clients for the 'what' but do not let them dictate the 'how.' </p> <p>Data in its raw state is essentially useless. Distilling and refining data by organizing it into a database is a key step in turning raw data into a wealth of business information. Understanding the data opens the door to getting it organized.</p> <p>The next article will discuss the third aspect in understanding your data from a relational point of view: determining the relationships.</p> Glenn Lloydhttp://www.blogger.com/profile/12693122866947596164noreply@blogger.com0