Database Basics

This isn't all inclusive and it's a work in progress, so please bare with me.
Envision your model.


When you start out on creating a database the place to start is with a vision. Lots of people do this without even thinking about it. They already know how they want their forms to look and work and by doing this they are basically envisioning the model. They see a 'Customer' form with a sub form of 'Contacts.' They remember how one application had a certain feature and they want that same functionality. This is basically envisioning.


Some people suggest filling out paper forms and I don't disagree with this but the core need when starting out is to understand the model. That's what the forms are for but for some people, they can see the objects themselves and start building tables. I think the model is the most important foundation of the application. What does your data LOOK like? That's right, what does it look like? Every good application keeps track of 'items' of some sort. Be they 'subjects' or 'actions' or whatever, they record something. These 'somethings' have a basic structure. A 'Customer' has a 'Name', 'Address', 'email', and etcetera. The items may have sub items. 'Orders' may have 'Line Items' 'Contacts' may have 'Calls' or 'Meetings' or 'Engagements' 'Rooms' may have 'Reservations' etcetera. Envisioning these items and how they interact is the basic starting point of a database application.


When I started, I said people do this already. They do it in their mind when they think of the forms and how they work. By understanding the functionality they want they are really building the model. Forms that contain data can represent items and sub items in your database. Drawing forms out on paper can help you identify the topics/items you need to create. Something I do from time to time is create a blank form with no data bound to it and just add controls to make it look like I want. This helps me 'envision' my object. Usually, for me, I can see it in my minds eye but you may want to schetch it out.


The reason I'm talking about forms in the conversation of the database model is because forms are how most people already 'see' data. I think that over the years, especially in the 'internet age', people have become acustom to 'seeing' data via forms. I don't have statistics to prove this but my experience is such and my contact with others in the community seem to bear testimony to the same. This is not a hard and fast rule but if it helps someone, all the better.


The model is important because a good or bad model will eventually make or break the whole application. When building a database, the better the foundation, the much better the application. If the foundation is bad, no matter how pretty the building, it will become shakier and shakier the taller it gets. The further you get down the road, the harder it will be to impliment the desired results. Knowing the end will be the best place to start.

Once you've envisioned the forms and even reports you want to produce, you can step into other questions. Some of these are:
- How do I want to sort my data?
- How would I like to group my data?
- How do I want to filter/find my data?
These questions are important because they help you identify field breakdown and indexing. Fields need to be in the smallest unit needed to sort, group and find. The postal system breaks addresses down into number and street where most people find they don't need this accuracy. Understanding how much accuracy you need identifies what fields you need. For small applications the need is not that great because you can break fields apart manually when querying but for larger ones the need to have information broken up correctly is paramount.

Indexing the fields that will be sorted, grouped and filtered the most will aid in the speed and performance of your application. No one likes waiting around for a form to produce the results they requested. Picking out the items now help solve these issues. Envision how your form sorts it's information. Envision how you group items. What are the major factors of an item? Let's take an example. Imagine you are creating a database to store survey information. You've identified how you want to present this data but what are some of the important sorting, grouping and indexing issues you may have with this data? How is survey data important? When it was collected is of importance. What the results were are important. Demographics i.e. where the data came from is important. So we may start may ensuring collection dates, results, and demographic information are index and uniquely identifiable. What I mean by 'uniquely identifyable' is it has it's own place and type. These fields need to be separated and set aside for these purposes. Say my survey collects numeric results for a person's satisfaction, 'on a scale of 1 to 10 how would you rate...?' I don't want to place any other data in that field other than 1 to 10. I may add a field for a note on this field but I would not store the note value in that field. 'Not applicable', 'Refused to answer', or other textual responses can be represented by a number and identified in a look up table. If identifying street names is important (as in the postal system example) then you ensure that Address number and Address Street are two separate fields enabling you to sort, group and filter easily by that field. (example: 123 Main St would be recorded as Address Number: 123, Street Name: Main St)

Envisioning your model is the starting place for building your application. Knowing what you want data to present and how you want to manipulate that data helps you know how to begin building.

Access Templates

One of the reason Microsoft Office applications (i.e. Word, Excel, Outlook, etcetera) are so great is because they have such a large support community. There are volunteers who offer support on the forums and you also have Microsoft website support via tutorials, how to's and also templates.
For the most part templates are a great place to start when working on a project. They offer a frame to build on, alter and fit to your personal need. Most Office application templates are easy to alter and fit to your need. I want to focus on Access templates.
Most people in the office world are familiar with MS Office templates. Where Word, Excel, PowerPoint templates are basically 'fill in the blank' type templates MS Access Templates are not. Not only are they not 'fill in the blank' style templates they are in many ways 'inflexable.' You can bend and cut and paste your way through most Office templates but you can't do the same with Access (unless you want an application that does little to nothing.) But the idea of a template is to have a frame to work with, isn't it? I really don't think that's the case with those in Access. These templates work great as long your need very closely resembles the original purpose of the template.
What am I saying? When you go to the Microsoft template site to pick a, say, Word template you can take that template and alter many parts of it to fit your need and never hurt the document as a whole. The document will work just as it did when you loaded it for the first time. If you pick a PowerPoint template, you can cut, paste, delete and move slides around all you want and you will still be able to run the presentation. But, when you get an Access template you have to be very careful what you create, delete, copy or paste because so many pieces of the template relate to so many other pieces. This is the the beauty of Access and catch. To keep everything in line Access has to keep every part working together.
One of these things Access keeps in line are table relationships. Table relationships are the way Access understands how your information works together. This is what allows you to have 'Contacts' with 'Visits' and 'Visits' with 'Notes.' This is what allows you to click a button and have a form open exactly for the related 'Customer.' And keeping things in line goes beyond table relationships.
There's also 'Automation.' Automation is what allows Access to do the mundane repetitive tasks without interference or help from the user. It's what calls the correct form with the correct related data so the user can make alteration easy and quick. Else, the user ends up having to search from form to form, table to table to find the right records. I'm using some very basic examples here and for good reason. Good Access databases (which Access templates are) have lots of complex automation. Moving data between listboxes, having cascading relationships with your combo boxes on forms, Checking the validity of what a user has entered to make sure it matches the businesses rules of doing business, all of these occur in different Access applications with different degrees of complexity.
So with all that said, what are Access templates good for? Well, they're good for many things. First, they're great if your business need matches the intended original purpose of the template. If you have a need for contact management, the 'Contact Management' template is a great place to start. If you have a need for keeping track of inventory then the 'Inventory Management' template is good for that. There are many templates designed for the most common business needs. So if you need matches one of the templates then go for it! Second, they are a great place to learn. Templates offer a great source of 'how to' examples for your own personal use. Do you want to know how to set up a 'Master Detail' situation? Templates are a great place to start. How do code behind events work? Open the templates and start digging behind the code. Is database design something you are learning? Open the templates and look at the table structure and the relationships. Looking for great examples on form design? Open the forms and tear them appart (you can always re-download the template if anything goes wrong.) Third, they are a great place to spark your own ideas. You can look at how the template does a thing and implement it in your own application. If you like the way a form is layed out, you can mimick it (without totally copying it, that's stealing.) There are a lot of great uses for the Access templates and I'm sure others could add much more.
The point I'm trying to make is this. Access templates are much different from most of the familiar Office templates and you have to look at them in a different light than that of the others to get the full value out of them.

Application Databases & Query Databases

In my short exposure to Access databases I noticed something that is somewhat troubling.
In my opinion you can look at an Access database in two frames of referrence:1) As an application2) As a area for obtaining information from data
What normally happens is people use the application database for both. So what's the problem? You get an application database that is cluttered with queries and temporary tables. The major problem with this is that no one can tell what the dependent tables and queries are and what the temporary ones are.
A small suggestion to the Beginner Access community: if you plan to use your database for more than just an application (which is basically the case 99% of the time for people who are not developers) create 2 databases. Create one solely for the application with only the tables, queries, modules, macros that you need to run the application. Create another (with linked tables) for your querying and research. This way, whenever someone else comes in contact with your database (and if it's of any value, someone eventually will.) They will not be confused with all the excess un-dependant stuff that goes along with daily activity.
Thanks,
Your Friend Chris

Advanced Functionality in MS Access

Welcome to the world of MS Access. MS Access is a wonderful program that allows intermediate office users to create simple applications for storing data and manipulating it.

Most users want to do similar things. They want to have combo boxes that filter other combo boxes (commonly called cascading combo boxes.) They may want to filter a form by a list box. Some may want to have special happen when a user clicks a button.

MS Office applications, in general, do not require you to understand programming or a theory of sorts to implement advanced features. But for MS Access it is imperative that beginner users understand that to get the above advanced functionality out of Access they must begin by learning the three basic major keys to Access development.

Three of the major keys to MS Access are VBA [Visual Basic for Applications], SQL [structured query language], and Relational Database Theory (these three not necessarily in this order.)

There are plenty of books on VBA, and SQL. The topic of Relational Databases is peppered throughout these two books when they relate to MS Access.

It's very difficult to expect advanced functionality out of MS Access without trying to grasp these three keys.

For beginner developers/users who desire to get more out of MS Access it would benefit them greatly to investigate these three topics in detail.

Thanks,

Your friend Chris

Donor Management Application

I created a nifty donor management application in MS Access 2003. This is a great tool for managing your donor information and to create thank you's etcetera.

If you're interested in getting a copy just email me.

Replace Spaces With Tabs

Numerous times I have to take a string with values that are separated by spaces and replace them with tabs. Here's an example:

Jane       Doe     Start     Finish

It's usually a field list I dumped from SQL Server and I need to drop it in as cells in spreadsheet

here's the VBA script I used to do this:
In a Word VBA Module (usualy a module for tools in the 'Normal.dot' file)

Public Sub ReplaceSpacesWithTabs()
Dim slct As Selection 'current selection
Dim s As String 'string to manipulate
Dim i As Integer 'counter
'set selection to current selection
Set slct = ActiveDocument.ActiveWindow.Selection
'set string to manipulate
s = slct.Text
'loop through string replacing two spaces with one
'and do this as long as there are two spaces in the string
Do
i = Len(s)'measure string
s = Replace(s, " ", " ")
Loop While i <> Len(s)
'replace the single spaces with tabs 'chr(9)'
s = Replace(s, " ", Chr(9)) '9 = tab character
'reset the selection text with the tabbed text
slct.Text = s
End Sub


I copy and paste the field list and dump it in Word, select all, and then run the script (via a tool bar button.)