access tips acess tips

Check out those microsoft access tips and tricks: High Performance Use the Performance Analyzer to learn more about how you can speed up the way your database works. Suggestions can range from splitting tables to changing data types. Clean-Up Time If you've made many structural or data-entry changes to a database, select Tools/Database Utilities/Compact Database to clean up, streamline and compact the database. Using Format() with dates You can use the Format() function to display different date components. For instance, to return a date's day, month, or year, you'd use the functions Format([datefield],"d") Format([datefield],"m") Format([datefield],"y") respectively. To display the month, you can use the form Format([datefield],"mmm") to return the abbreviated month or the function Format([datefield],"mmmm") to return the month's full name. You can also combine the formats to create unique formats, as in Format([datefield],"dmmmyy") which would display the day, abbreviated month, and a two-digit year value, with no spaces in between each component. Align with Style On reports that have multiple subreports, the Can Grow and Can Shrink properties will be ignored for any subreports that overlap. For a quick way to ensure that adjacent subreports (or controls of any kind) aren't overlapping, select them as a group and then select Format | Vertical Spacing | Decrease. Instead of overlapping them even farther, Access will set them to be perfectly adjacent to each other. Speedy Selections With a record selected in the Datasheet view, you can select an entire row by pressing Shift+spacebar. Press Ctrl+Shift+spacebar to select the entire datasheet; press Ctrl+spacebar to select an entire column. Change the Case of a Field in Access Did you inherit a database with mixed case typing that needs to be in upper or lowercase? There's a quick and easy way to change a field to this format, and it doesn't even require a macro. Go to Design view in a table or report. Select the field. View the Properties for the field and select the Format Tab. Type a greater than symbol (>) in the format property to make the field uppercase, or a less than symbol (<) to make the field format to lowercase. Keep Good Notes Right-clicking on a table in the Table list opens a Properties sheet. Description, one of the properties, is a free-form memo field that lets you jot down anything you need to remember about that table. Exploding Fields When you're editing a data field, you can get an exploded view of the field by pressing Shift+F2. Data Shortcuts In the Datasheet view, pressing Ctrl+" (quotation mark) in a cell will insert a copy of the contents in the cell above it. Press Ctrl+: (colon) to insert the current time; press Ctrl+; (semicolon) to insert the current date. Excel to Access To bring data from a Microsoft Excel spreadsheet into Access, first open both programs. Highlight the data in Excel and grab the edge of the highlighted data, then drag it to the Windows 95 Taskbar and drop it onto the Access icon. Access will automatically import the data. Speedy Selections With a record selected in the Datasheet view, you can select an entire row by pressing Shift+spacebar. Press Ctrl+Shift+spacebar to select the entire datasheet; press Ctrl+spacebar to select an entire column. Table Settings A new feature in Access 97 is the ability to import HTML tables as data. Select File/Get External Data and Import, and then choose the HTML document to import from. If there's more than one table in the document, the import wizard will ask you to select a table. A Memory for Names Use the Table Analyzer to take redundant information in tables and break it out. For instance, if you have one field in your database that has nothing but a list of names with a fair amount of repetition between records, the Table Analyzer converts that field into a reference field that points to an auxiliary table that holds the names--eliminating redundancy. This saves memory, space and processing time. Quick Cross-Check One common way to cross-reference data between tables in Access is to have a field in a main table use a number to refer to a record in an auxiliary table. This way, you can have the values automatically cross-associated when you build forms or reports. Edit the properties for the table in which you're storing the main data, and select the Lookup tab for the field that stores the reference number. There, you can set which row source type, display control, bound column, or other display and data properties are always associated with that particular lookup field. This removes the need to rebuild the associations every time the field is used in a form or report. Default Value Magic You can define a field in a table or on a form to contain a default value. The result is that, if the user goes to a new record in a form, the default values are already displayed. I know from sitting with users that this can cause confusion. You can get the benefits of default fields without incurring this confusion. A solution I use is to have a duplicate field hidden on the form with the same control source as the field the user is to fill in. Now you can set the default value in the hidden field, not the visible field. On a new record, the default value isn't displayed in the visible field until the user types the first character of the new record. Then, the default value from the hidden field is recorded in the underlying table. As the control source of both the visible and invisible fields is the same, the value now stored in the table will appear in the visible field. To a user, it appears as if the system has automatically completed some fields for him by magic. Unfortunately, if the default value property is set at the table level, this method won't work, but it might be possible to move the default value property from the table to the form. Update and Append Records with One Query Did you know that you can use an update query in Access to both update and add records at the same time? This is useful if you have two versions of a table, tblOld and tblNew, and you want to integrate the changes from tblNew into tblOld. Follow these steps: Create an update query and add the two tables. Join the two tables by dragging the key field of tblNew onto the matching field of tblOld. Double-click on the relationship and choose the join option that includes all records from tblNew and only those that match from tblOld. Select all the fields from tblOld and drag them onto the QBE grid. For each field, in the Update To cell type in tblNew.FieldName, where FieldName matches the field name of tblOld. Select Query Properties from the View menu and change Unique Records to False. (This switches off the DISTINCTROW option in the SQL view. If you leave this on you'll get only one blank record in your results, but you want one blank record for each new record to be added to tblOld.) Run the query and youםll see the changes to tblNew are now in tblOld. This will only add records to tblOld that have been added to tblNew. Records in tblOld that arenםt present in tblNew will still remain in tblOld.