Microsoft Access Table Tips

When and why to use Indexes

How can I change the starting value of a AutoNumber type field?

Referential integrity, cascading updates and cascading deletes - what does it all mean?

Hiding Rows and Columns in Tables

How to delete duplicate records in your table

How can I find all of my overdue accounts?

How to remove tables from the Relationships window

When and why to use Indexes
You should use an index on any field that you will want to sort, search, or join to another table. They will speed up these tasks. However, each index makes the database file larger and can slow it down, so only use them when you need to.

How can I change the starting value of a AutoNumber type field?
First of all you need to create your table with an AutoNumber type field. Do not enter any records. Then create another table with only a single Long Integer Number type field. Ensure this field has the same name as the AutoNumber field in the first table. Enter one record in the second table that is a number one less than the required start of the AutoNumber for the first table.

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.

Referential integrity, cascading updates and cascading deletes - what does it all mean?
When you create related tables in Access and check the Enforce Referential Integrity box, Access will not allow you to add records where there are no related records in another table, make changes to records, or delete records in one table where there are other related records in another table. This would cause you to break the relationships between the tables that you have defined.

However, by selecting to Cascade Update Related Fields after you have enforced referential integrity, Access will automatically update all related records if you amend or add data. For instance if you change a customer's reference number Access will update all occurrences of that number in related records.

Similarly, by selecting to Cascade Delete Related Fields, Access will delete related records in related tables. You may wish to delete an employee from the database. All related references to that employee can then be removed from the data. Access will ask for confirmation before deleting records.

Hiding Rows and Columns in Tables
You can reduce the width of a column in table by using the mouse to drag the column edge to the desired width. Position your mouse pointer at the right of the field selector for that column and drag it to the left. If you drag it beyond the left hand side of the column you will hide the column.

You can decrease the height of a row in a table in a similar way. Simply position the mouse between the two record selectors at the left side of the table and drag to the required height. When you change the height of the row it will make all rows for that table the same. For this reason you cannot hide the row by dragging the bottom above the top of the row as it would hide all rows for the table.

How to delete duplicate records in your table
Access will not actually allow you to delete records from a table but you can create a new table to which holds the same records but without the duplicates. Then delete the old table and rename the new one.

1. 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.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. 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.

How can I find all of my overdue accounts?
You need to create a filter that compares today's date with the InvoiceDate in your table. To do this you select that field for your filter and in the criteria row enter:

<Date()

This filter will return records where the InvoiceDate is before today's date.

You can then manipulate this if, for instance invoices are due 14 days after the invoice date, the code would look like this:

<Date()-14

This filter will return records where the InvoiceDate is 14 days before today's date.

How to remove tables from the Relationships window
You don't have to delete your table. Simply select the table in the relationships window, then Right Clik and REMOVE TABLE. This does not change any relationships the table has and you can always add the table again if you need to by using ADD TABLE from the relationships menu.

Click the link if you need to Repair Access database. Fast service, no obligation, free quote!