Book

Suggestions


Enter your email address:

Delivered by FeedBurner


Home Page

Bloglines

1906
CelebrateStadium
2006

OfficeZealot

Scobleizer

TechRepublic

AskWoody

SpyJournal









Subscribe here
Add to My Yahoo!
This page is powered by Blogger. Isn't yours?

Host your Web site with PureHost!


eXTReMe Tracker
  Web http://www.klippert.com


  Sunday, April 06, 2008 – Permalink –

Add a Table

Drag drop trick



While working with a query in Design view, you may find that you need to add a table or query. The "book" way to do it is to click the Show Table toolbar button, drag the appropriate objects from the list, and then close the dialog box.

There is another way to do this.


Drag the table or query object's icon from the Database window/Navigation pane directly to the top half of the query design grid.


You can also use this technique in Access's Relationships window




See all Topics

Labels: , ,


<Doug Klippert@ 6:50 AM

Comments: Post a Comment

  Tuesday, February 26, 2008 – Permalink –

Set Field Defaults

Speed up table creation with default field settings



When you add fields to a table, Access assumes you want to use a 50/255-character Text field by default. However, you may typically use a smaller field size or you may personally use Number fields more often than Text ones.


You can avoid having to change the size and data types for new fields by setting defaults that are appropriate to your own design habits. To do so:

  1. Choose Tools> Options from the menu bar and switch to the Tables/Queries sheet.

  2. Select the data type you use most from the Default Field Type dropdown list.

  3. Set the Text and Number sizes you usually want to use in the Default Field Sizes panel and

  4. Click OK.


In Access 2007 go to Access Options>Object Designers:





See all Topics

Labels: ,


<Doug Klippert@ 6:43 AM

Comments: Post a Comment

  Saturday, February 02, 2008 – Permalink –

Filter Multiple Selections
Table sorts


In a table's Datasheet view, you can filter multiple selections in a few simple ways.


In the first method, you select one of the fields you want to filter by and click the Filter By Selection button on the toolbar. Access filters the records by that selection. Next, select the second field you want to filter by and click the button again. Access filters the records even further by this second selection. Continue to follow these steps until you have filtered by the desired number of selections.


You can also use Filter by Form. Click the icon on the toolbar. In the displayed form, enter the criteria. You can use And/Or statements, such as "Boston" Or "San Francisco".
(Access will enter the quote marks for you.)


For the third option, you use the Advanced Filter>Sort feature. To do so, select Records>Filter>Advanced Filter>Sort from the menu bar. Access displays a grid similar to the Query By Example grid. Now, drag down all the fields you want to filter by from the field list. Then, in the Criteria cells enter the values you want to filter for. When you have finished, click the Apply Filter button to see the results.


Fourth, you could use Filter by input. Right click any entry in a field you want filtered. Enter the value in the Filter For box and hit Enter. You could then choose another field and sort again by another criteria.

Creating a Query would let you save your filter, but these methods can be used to quickly display the desired information in a table.

(In 2007 these options are on the Home tab in the Sort & Filter group)


Florida Gulf Coast University:
Sorting and Filtering


eHow.com:
How to Filter Records in a Microsoft Access Table


Microsoft Kb:
How to filter records in an Access database



See all Topics

Labels: , ,


<Doug Klippert@ 7:21 AM

Comments: Post a Comment

  Thursday, July 26, 2007 – Permalink –

Use a Table Alias

to change names in a query


When you need to change the table name referenced in an existing Access query, it can be a pain. This is especially true if the query contains a large number of fields.

You typically might use an alias when you need to relate a table to itself or tables have long or unwieldy names.

If you make a practice of always using aliases in your queries, you can easily change which table is used by changing the one occurrence of the original table name in the query's FROM clause.
  1. To set a table alias in the query's Design view,
  2. Right-click on the table field list and choose Properties.
  3. Then, enter the alias name you want to use in the Alias text box.





See all Topics

Labels: ,


<Doug Klippert@ 7:36 AM

Comments: Post a Comment

  Sunday, July 08, 2007 – Permalink –

Auto Number

Don't be smart



There should not be any "intelligence" in an AutoNumber field. It is meant as an index field and not anything else.

If the need should arise to reset the field, if your table does NOT contain any records, simply compacting the database again will set the Autonumber field back to 1.

Another way would be to delete the AutoNumber field and re-insert it in the table.

Here's a long way to start at a specific number.

  1. Create your table with an AutoNumber type field, but don't enter any records.
  2. Create another table with only a single Long Integer Number type field.
  3. This field must have same name as the AutoNumber field in the first table.
  4. Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.
  5. Now create an append query to append the record in the second table to the first table and run the query.


You can now delete the second table and begin entering your data into the first table.

Also:

Access AutoNumber Reset

"This is some sample code that shows how to programmatically reset all AutoNumber fields in an Access Database to a correct value (whether it be 0 or the max value + 1). In addition, it contains code for Compacting and Repairing an MS Access Database. This is perfect for people who are working with a complicated Access Database and have experienced AutoNumber bugs!

And:

Creating an AutoNumber field from code



See all Topics

Labels:


<Doug Klippert@ 6:29 AM

Comments: Post a Comment

  Sunday, March 04, 2007 – Permalink –

Automatically Renumber Records

Been looking for this



What happens to a list when one record is deleted?

If you need to correct the numbering, see these instructions.

Automatically renumber the records in an Access table when one is deleted

from Martin Green - FontStuff.com



See all Topics

Labels:


<Doug Klippert@ 7:29 AM

Comments: Post a Comment