|
If you’re trying to learn Microsoft
Access
2007 there are excellent online resources available. I recommend checking
these out:
In Access 2007, the Navigation Pane has replaced the
Database Window as the main way to get around in a database.
A new feature in Access 2007 is the option to use a tabbed interface.
This allows you to open multiple objects (tables, forms, reports, or
queries) at once. Each object is a separate tab so you can easily see the
available objects and click on the one you want. To use this feature:
 |
Click the Office button in the left
corner of the screen |
|
|
Click the Access Options button at the
bottom of the window |
|
|
Select the Current Database in the
left pane |
|
|
Under Document Window Options, select
Tabbed Documents
|
The new Access 2007 Report Layout View is a major
improvement over previous versions of Access. This view allows you to see
the report as it will print (WYSIWYG) and quickly change controls. New
features make it easier to group, filter and sort data. Check out
Quickly summarize group data in Access 2007 reports on the TechRepublic
Web site for step by step instructions on summarizing group data in Access
2007 reports.
If you are upgrading to Access 2007 one of the first hurdles may be
opening your Access file only to find your code won't run. The
solution is making sure your database is in a "trusted" location. To
do this:
|
|
Click the Office button |
|
|
Click the Access Options button |
|
|
Select Trust Center |
|
|
Click the Trust Center
Settings button |
|
|
Select Trusted
Locations |
|
|
Click the Add new
location button |
|
|
Browse and
select the location of your database |
|
|
Click OK
|
In Microsoft Access you can
combine the text in two or more fields into a single text string by using
“concatenation”. The results can be displayed in a field in a query, or in
a control on a form or report.
For example, If you have a
table that contains the fields First and Last for the name,
use the following expression to create a text string that displays the
values of the First and Last fields separated by a space.
=[First] & " " & [Last]
Type this expression in a
text box control on a form or report to display the first and last name
together. (Be sure to put a space between the double quotes in the
expression.)
 |
Open your Access report in Design View |
|
|
Use the Toolbox to add a text box for
displaying the number |
|
|
Select the text box and choose View
Properties from the pull-down menu |
|
|
Click the Data tab |
|
|
Change the Control Source to =1 |
|
|
Change the Running Sum to Over Group |
-
Open a table in
Design view.
-
In the upper portion
of the window, click the field you want to define a default value for.
- In the lower portion of the window on the General tab, click the
DefaultValue property box, and then
type the default value (such as New York) or an expression (such
as Date( )).
Note: Setting the DefaultValue property for a field has no effect on existing data.
However, you can replace a field's current value with the default value
by pressing CTRL+ALT+SPACEBAR.
Here's a quick way to copy an Access table into Excel.
-
In
the Access database window, click the table you want to export.
-
On
the Standard toolbar, click Office Links.
-
Click
Analyze It with Excel.
Excel automatically opens and displays your table in a
worksheet.
Whenever you create a new Access database (.mdb) file or open an existing
one, Access saves it to or opens it from the default working folder. You can
change the default working folder for Access database files to any folder on
your computer by typing the path for the new folder in the Default database
folder box on the General tab of the Options dialog box (Tools menu).
One way to delete
duplicate records in Access is to create a new table which holds the same
records but without the duplicates. Then delete the old table and rename the
new one.
-
Use a make-table query
based on this table only. IMPORTANT - Ensure that you include all of the
fields from your original table in the QBE Grid, otherwise you could loose
data.
-
Open the query's
property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values
property to Yes
-
Because you have
selected the Unique Values to Yes when you run the query, Access creates a
new table without duplicate records. You can now delete the old table and
rename the new one.
Open the query in Design
View. In the sort cell of the relevant field click either DESCENDING or
ASCENDING. Then click the TOP VALUES box that you will find in the toolbar.
Enter either a percentage or the number of highest or lowest values.
For example, by
selecting DESCENDING and then entering 10 in the TOP VALUES box you will get
the top ten values in your query. Or if you enter ASCENDING and 25% you will
get the bottom 25% of values in your query.
You can also input Top
Values by setting the TopValues property in the query's property sheet.
To add the database name to a report
-
Open your database.
-
In the Database window, right-click your report, and
then click Design View.
-
Click the Text Box tool in the toolbox, and then
click in the report header where you want the name to appear.
-
In the label for the unbound text box you just added,
type Database:
-
Right-click the text box, and then click Properties.
-
Click the Data tab, and then type the following text
in the ControlSource property box:
=Left(CurrentProject.Name,Len(CurrentProject.Name)-4)
-
On the View menu, click Print Preview to see the
results
Don't you find it annoying how long it takes aligning
controls to get forms and reports looking tidy? Here are several tips you
can follow that will speed the process.
Aligning controls with menus Highlight the
controls you want to align and use Format, Align and then choose left,
right, top or bottom.
Create alignment toolbar Create a "QuickForms"
custom toolbar that has 4 buttons covering the 4 alignment options mentioned
above. Enable this toolbar when you are looking to tidy your forms and
reports.
Keyboard shortcuts for moving Controls It’s
often easier to align the Controls when you use the keyboard to "nudge" the
object into place. Highlight the Control, hold down the Ctrl key and then
use the navigation arrows to move in the required direction.
A validation rule is an
expression that can define the information that will be accepted in fields.
You can type validation rules in, or you can use the Expression Builder to
create them. At the field level, Access uses the rule to test an entry when
you try to leave the field. At the table level, Access can use the rule to
test the content of several fields when you try to leave the record. If an
entry doesn’t satisfy the rule, Access rejects the entry and displays a
message explaining why.
In the Access table design
window you can add a validation rule to the Phone
field that will prevent the entry of an
area code other than 414 or 262, by selecting the Phone field, clicking in the Validation Rule box,
and typing Like "414*" Or Like "262*". |
 |
Open the Access database that you want to compact
automatically. |
|
|
On the Tools menu, click Options.
|
|
|
Click the General tab |
|
|
Select the Compact on Close check box
|
|
In
Access, you can create a vertical label or text box on a form or report by
setting the Vertical property of the control.
To set a label or text box for vertical display
|
|
Open the form or report in Design view. |
|
|
Click the label or text box. |
|
|
On
the View menu, click Properties, and then click the Other tab.
|
|
|
Set
the Vertical property to Yes. |
For more information about the Vertical property, click
the Vertical property box, and then press F1.
Here are a few keyboard shortcuts you can use to insert the current time and
date in a Microsoft Access table or Microsoft Excel spreadsheet.
|
|
Current date: Press CTRL+SEMICOLON
|
|
|
Current time: Press CTRL+SHIFT+ SEMICOLON
|
|
|
Current date and time: Press CTRL+
SEMICOLON then SPACE then CTRL+SHIFT+ SEMICOLON |
In Access, this keyboard shortcut only works if you are entering data in
the Datasheet or Form view.
Editor's Note: When you insert the date and time using this tip, the
information remains static. To update this information automatically, you
must use the TODAY and NOW functions. To learn how to do this, search for
Insert the current date and time in a cell in Excel Help and then click
Insert a date or time whose value is updated.
This tip comes to us from
www.microsoft.com, courtesy of Kimberly Schenk, Smyrna, Tennesse.
|
drag the edge to increase
or decrease the width.
, or press F11 to toggle the
pane between the open and closed views.