If you’re having trouble normalizing empty tables, fill each table with five to ten records. Viewing the tables with data usually makes it easier to spot normalizing issues. Show
Here’s how to get started normalizing your tables:
You may find that splitting one table into two still does not eliminate all redundancy in a table. In that case, keep splitting the tables until all redundancy is gone. You type 73.725, but Access changes it to 74Automatic rounding can frustrate the living daylights out of you, but correcting it is easy. By default, Access sets all number fields to accept long integers. As you may remember from your high school math days, an integer is a negative or positive whole number. To accommodate decimals, you change the field-size setting so it accepts decimals. Here’s how:
For details about the difference between Single, Double, and Decimal field sizes, press the F1 key while in the Field Size property box. The Help screen gives a detailed description of each field size, the numbers it will hold, and the amount of space reserved for that size. Access requires an Internet connection to use its help system. The words they are a-changing—problems with Access AutoCorrectSometimes those “helpful” features in Access can become a nuisance. One such feature is called AutoCorrect. You may be familiar with it from Microsoft Word, where it is often a great thing. Databases, however, often contain acronyms, part numbers, and the like. AutoCorrect can have a field day with such “words”. You may not even realize it as you enter your data. You have two choices to resolve this problem.
1. Click the File tab in the upper left corner of the Access screen. 2. Click the Options button in the menu down the left side of the screen. The Access Options dialog box appears. 3. Click Proofing from the list on the left. Your proofing choices appear. 4. Click the AutoCorrect Options button. The AutoCorrect dialog box appears. 5. Uncheck some or all of the check boxes in the AutoCorrect dialog box. You can disable some or all of the AutoCorrect features, depending on what AutoCorrect is doing to annoy you at present. Uncheck the Replace Text as You Type option if you no longer want Access to “fix” your “spelling errors” for you. 6. Click OK two times to save your changes. You can now type your problem text correctly, without AutoCorrect’s interference, and have it stay as you typed it. Was there and now it’s gone—accidental data deletions in AccessYou might’ve heard this one a lot over the years: “The database deleted my record!” Well, I’ve got news for you: The database doesn’t do anything without us humans commanding it. And humans can make a couple of mistakes:
The Undo command (Ctrl+Z) will not reverse the deletion of a record.
UndoDon’t panic. Before doing anything else, press Ctrl+Z. That’s the Undo command. If the record comes back, you’re in luck. Undo reverses data-entry errors that may cause the record to appear deleted. However, this will work only if you Undo right after the data-entry error takes place. Search for the missing recordIf you try the Undo command and the record doesn’t come back, there’s still a chance that a data-entry error is hiding it by putting it where you don’t expect it to be. Open the table that contained the record and search for it in some way other than you normally would. Look for anything out of the ordinary on similar records. Here are some examples:
Backup recoveryIf you can’t find the record anywhere, copy the record from a backup of the database file. This solution works only if you’ve backed up your database since the record was originally added. If you back up at night and the record was entered during the same day it went missing, that record will not be in your backup. You run an Access query, but the results are unexpectedQuery-writing is an art form. Even the experts mess up every now and then. Here are some common solutions to unexpected query results:
1. Open the problem query in Design view. The Design tab on the Ribbon appears. 2. Click the Property Sheet button from the tab’s Show/Hide Ribbon group. The Property Sheet window opens to the right of the query grid. 3. Click in the gray area between the field lists in the top half of the query grid. The Property Sheet should now display Query Properties. (Look right under the Property Sheet’s title bar to confirm this.) 4. Click in the Unique Values row of the Property Sheet. A drop-down list arrow appears at the end of the Unique Values row. 5. Select Yes from the drop-down list and run the query. The doubling should disappear.
1. In Design view, right-click the join (the line connecting the two tables) and choose Join Properties from the menu that appears. 2. Examine the types of joins offered and choose the one that says something like “Include ALL records from ‘Customers’ and only those records from ‘Orders’ where the joined fields are equal”. The actual text you see differs according to the names of your tables. To query aficionados, this is called an outer join. Very cool. 3. Click OK and run the query. You should now have all records from the Customers table whether or not there are corresponding records in the Orders table. If your query involves several criteria, some calculated fields, and numerous relationships, try breaking the task into several smaller steps instead of trying to solve the problem all at once. The step-by-step approach lets you focus on each piece, one at a time, making sure each works perfectly before moving on to the next one. If your query still doesn’t work no matter what you do, ask someone else to take a look. I’ve often worked on a tough query problem for hours, shown it to someone else, and heard those magical words: “That’s simple. Just do this.” And the problem is solved. Getting a fresh pair of eyes on the problem often solves things fast. The dreaded Parameter dialog box in AccessAt some point, when opening a query, form, or report, you’ll see a Parameter dialog box when you don’t want to see a Parameter dialog box. Do you throw your hands in the air and curse the universe? Of course not! Whenever you see a Parameter dialog box unexpectedly (you can set them on purpose), it means that Access can’t find a field referenced by either the form or report or the query behind the form or report. Say that the problem is with a report. To troubleshoot, start with the query behind the report. Open that query in Datasheet view and see if you get the parameter. If you do, what field is it asking for? That field is the one Access can’t find. So, switch the query to Design view and find the column with the field that Access can’t find. The problem field is usually a Calculated field that references other fields. Is each field and table name spelled correctly? If not, correct the spelling errors. Is each field in the table it’s supposed to be in? For example, if your reference reads Orders.LastName and the LastName field is in the Customers table, correct the error by typing Customers.LastName. If the query runs without a parameter, then the problem is on the report. So, open the report in Design view and check each control on the report that is bound to a field. If Access can't find one of the fields the control is supposed to display, it’ll put a green triangle in the upper left corner of the control. Check each one for the green triangle. If you find the green triangle, check the spelling of the field referenced by the control. For example, if the control is supposed to display LastName (no space) and the reference in the control says Last Name (space), then remove the space so that the control on the report matches the field name from the query. Also check the report’s underlying query to confirm the problem field is selected in the query. The slowest Access database in townAn Access database may end up on the shared drive of a business so it’s available to everyone who needs it. The problem with placing the entire Access database on the shared drive is that it often runs slowly on each user’s workstation (that’s a fancy word for an individual computer). You’ll also likely run into errors if multiple people attempt to use the database at the same time. The complaints start rolling in, and you don’t know what to do. The solution to this problem lies in splitting the Access database file into two separate files:
The front end resides on the user workstation.
The back end resides on the shared server. The front end is linked to tables in the back end. All you’re really sharing is the data — so the data is all that should go on the shared drive. By setting things up this way, the only information that must travel across the network is the data requested by the user. Such a setup dramatically speeds database performance and allows multiple users to enter and edit data at the same time. Splitting the dataset is not as hard as you might think. Access makes it a snap with the Database Splitter Wizard. Follow these steps to split your database:
Don’t have a shared drive or want to get rid of your share? You can still have multiple users in your database at one time by placing your data in the cloud. Your Access database file is as big as a houseAs time goes by, you find your database file growing larger and larger. This is a result of deleting objects and records over time. If, for example, you create a query and then later delete it because it’s no longer needed, Access doesn’t automatically remove the space occupied by that query from the database file. The same is true for records. As you delete records from a table, the space that those records occupied in the database file remains. Eventually, the file can become four or five times the size required to hold the data and objects within it. Why should you care if the file size increases? Here are two reasons:
Want a database file to compact each time you close it? Follow these steps:
Compact on close is used best on the front-end file only. Compacting the back end on close may cause corruption of the back-end file should another user be in it when you close out of it. Do not turn on Compact on Close on the back-end file. You get a mess when importing your spreadsheet ito AccessIt’s common practice to upgrade a collection of spreadsheets to an Access database after the spreadsheet solution no longer suits your needs. It’s also common to find the imported spreadsheet (now table) data in a state of disarray. The easiest way to solve this problem is by cleaning up the spreadsheet before you import it. Here are a few tips for a tidy import:
We’re sorry; your Access database file is corruptIt started out as a day just like any other. However, on this day, you are getting an error when you open the front end of your split Access database. You can’t seem to open any forms or reports. It’s funny how a few little messages can ruin your day. You start wondering if you backed up the data file last night and when the file was actually corrupted. Then you start wondering how you’ll get out of this mess. Fear not. There is a simple solution to a corrupt database. Here are the steps:
If, after following the preceding directions, the corrupted file still doesn’t open, you have a serious problem that could take some effort to clean up. The next step is to resort to a backup copy of the database. Check what data is missing between the backup and your recollection of the corrupted file. Yes, you’ll have to reenter any missing data. Sorry! |