| I like using databases for any kind of list that I keep. | | | | you next access it. |
| I have databases for products that I sell on eBay, | | | | - If you use the text name to index on, then you will |
| our contacts, our web site logins, our software | | | | have to rename every one and you will be like a |
| registration info, and our financial information including | | | | spreadsheet. |
| credit cards and utilities. | | | | Use default values if the majority of entries will be |
| I like keeping these lists in relational databases | | | | one likely value. |
| because they are so flexible and your data can be | | | | - If you have a country field and it will usually be |
| consistent. If you are storing company names and | | | | USA, then have a country lookup table and set USA |
| have one table for them, then you can use a drop | | | | as the default value. |
| down box in your form populated with names from | | | | - If you have a state field and it will usually be your |
| the company lookup table. Having one standard name | | | | own state, then have a state lookup table and set |
| for each company makes your data uniform and | | | | your state as the default value. |
| more useful because you can sort it and search for | | | | Use memo fields if you will have a lot of information |
| it. | | | | in a field. A comment field is a good example. |
| Some people use a spreadsheet for this. A | | | | If you want (I do not), you can prefix field names |
| spreadsheet is equivalent to a flat database. In a flat | | | | with their data type, such as txtTelephoneNumber |
| database, there are no separate tables; there is just | | | | for a text field or boolHasTelephoneNumber for a |
| one table. If you have fifty records that include the | | | | Yes/No, or Boolean, field. |
| same company name then each company name | | | | Avoid words that the database reserves, such as |
| appears fifty times in a spreadsheet. As people enter | | | | name, but CompanyName, or even txtName will be |
| data in the spreadsheet, they might enter AGC, | | | | okay. |
| sometimes, AGC Company, sometimes, AGC Corp, | | | | Add separate fields for data that may have more |
| and American Gadget Company other times. You will | | | | than one possible entry. |
| have a mess on your hands and you will not be able | | | | - Telephone numbers are good examples of more |
| to sort or find records well. | | | | than one entry: Use VoiceNumber1, VoiceNumber2, |
| You can fix spreadsheets by going through the | | | | FaxNumber, MobileNumber, and so forth as you need. |
| periodically and renaming variant names to the one | | | | If you want, you can get fancy and have another |
| name that you want to use, but you will always be | | | | lookup table that refers to a lookup table for |
| behind in this reactive approach. I can almost | | | | repetitive data. |
| guarantee you will last no more than two weeks | | | | - For example, telephone numbers can have a |
| doing this unless you are very devoted. | | | | separate lookup table. |
| The relationship between data, the way it is stored, | | | | - Each entry could have: |
| and the way it is displayed is very inflexible in a | | | | - A primary index |
| spreadsheet, but it is very flexible in a database. You | | | | - A numerical field to associate this entry to the |
| can store information in a database and then access | | | | index of a company |
| it by query, form, or report in a different order. You | | | | - The telephone number |
| can show just what you want to show, some or all, | | | | - Its extension if any |
| at any time. What you see is not tied to how your | | | | - A description (Voice, Fax, Mobile, and so forth). |
| data is stored. | | | | - You would need a subform to display all the |
| When you look at a spreadsheet, you see the entire | | | | telephone numbers for a company. Subforms are not |
| row in the order it is in and with everything showing. | | | | part of this article. |
| Yes, you can collapse some fields but then you have | | | | - This gets complicated. In addition to using a |
| to open them up again. You sort the data by | | | | subform, you also need to define a relationships |
| selecting it and applying a sort. If someone only | | | | between the tables. But for now, lets keep things |
| selects the first half of each row and sorts them, | | | | simple. |
| say just what shows on the screen when there is | | | | Queries: |
| information offscreen, then your data will be | | | | - Always create a query to match each table. |
| hopelessly scrambled. | | | | - Name each query using the prefix qry. |
| Unless you use a form, it is easy to get lost in a long | | | | - Use qryContacts and qryCompanyLookup for |
| spreadsheet row and enter information in the wrong | | | | example. |
| cell above or below where you should be. Even if you | | | | - Most queries will first use the include all fields |
| use a form in a spreadsheet, you see all the fields | | | | symbol of *, but you can use just specific fields if |
| and in the order it is saved in the spreadsheet. | | | | you wish. |
| When you use a database, you can have the system | | | | - Assign a sort order to one of the fields. |
| sort it for you correctly behind the scenes, and you | | | | - If you use the *, then drag the field you want to |
| control what fields you show and in what order. | | | | sort on from the field list to the query grid and select |
| Tips For Designing Database Tables and Queries | | | | the sort order. |
| Parts of these tips assume you use MS Access. I still | | | | - You do not need to check the show box for this |
| use version 2003. | | | | field because it is included to be shown when you |
| Tables: | | | | use the *. |
| - First, separate repetitive information into separate | | | | - Assign special query names that you will define as |
| lookup tables. | | | | an expression. |
| - If you have a contacts database and want to | | | | - For example, you may want to have fields for first |
| record what company they work for, use a | | | | name, last name, and perhaps middle initial, title, or |
| company lookup table. | | | | suffix. |
| - Other candidates are for a contacts database are | | | | - You can add a query field called FullName as |
| company, county, country, and state lookup tables. | | | | Fullname:[FirstName] & " " & [LastName] |
| - The state lookup table is interesting because you | | | | - You can add a field called LNFirst as |
| can set it up with the full, properly spelled state | | | | LNFirst:[LastName] & ", " & [FirstName]. |
| name in one field and the proper post office | | | | - The expression name is followed by a colon and |
| abbreviation in a separate field, and then chose which | | | | then text that uses field names in brackets and |
| to show. You won't end up with Mass., MA, | | | | perhaps operators such as the & text string |
| Masachusets, and Massachusetts for example. | | | | operator. |
| Name your table with a tbl prefix. | | | | - & " " & adds a space between [FirstName] and |
| - Use tblContacts and tblCompanyLookup for | | | | [LastName]. |
| example. | | | | - & ", " & adds a comma and a space after |
| - This tells you that you are looking at a table later | | | | [LastName] and before [FirstName]. |
| when you need to get or save information when | | | | You can get fancy and test for a field having a value |
| programming. or selecting a source for a drop down | | | | by using an ifexpression, but let's keep it simple for |
| box on a form. | | | | now. |
| Use the suffix Lookup for tables that are there for | | | | Make sure your query works as expected and check |
| relational reasons as lookup tables. | | | | that your expressions work the way you want. |
| - If the main table is for your contact information, call | | | | Defining good tables is a fundamental step to |
| it tblContacts. | | | | creating a useful database. You can come back and |
| - If you have a company lookup table, call it | | | | add fields later, but it is best to get the right fields in |
| tblCompanyLookup. | | | | the beginning. |
| - This helps identify the use of the table, a main table | | | | As an example of having the right fields, it's usually |
| or a subordinate, supporting one. | | | | better to separate data into separate fields and use |
| Always have a primary key that is an automatically | | | | a query expression to put them together in useful |
| generated number, an Access Autonumber type. | | | | ways. An example is to have separate first name |
| Name the primary key your root name of your table | | | | and last name fields and have a query expression put |
| name followed by ID or IDX for index. | | | | them together in first name space last name form |
| - Use CompanyID or CompanyIDX for example for | | | | and in last name comma space first name form. |
| tblCompanyLookup (Company is the root name).. | | | | There is no penalty for naming fields descriptively and |
| - Then you will know that you are referring to the | | | | doing so will make understanding your design much |
| important primary index field later. | | | | easier in six months when you have to come back to |
| Always refer to the lookup table by its index number. | | | | it and fix something. |
| - Then you can change the text while the index | | | | I urge you to break the spreadsheet habit and go |
| number that finds it is unchanged. | | | | for a good database design when the data call for it. |
| - All of your text will change to the new value when | | | | |