8 Tips to Create Flexible Tables in Microsoft Access

1
8 Tips to Create Flexible Tables in Microsoft
Access
2
Tables should be responsive, optimised
and efficiently designed for your Microsoft
Access database.   Databases are all
about tables and how they hold and then show the
information stored in them. It is vital for them
to be flexible, dependable and informative.
Thats the reason why it is fundamental to create
and assign the appropriate properties to such
tables.
3
The following 8 simple tips will help to
plan and build your MS Access tables   1. Naming
of the fields The name of a field is its
identity, so it should give a reasonable idea
about the fields function and data type.
Arbitrary and inept name fields make your
database pointless and confusing. Microsoft
Access allows up to 64 charactersfor afield name
which can contain letters, numbers, and spaces.
However, good practice is to not include spaces
in the field name as this can cause issues later
on for more advanced functionality especially
when working with VBA and SQL codes.   2. The use
of field properties Microsoft Access
provides you with the option to assign properties
to the fields such as format, caption,
description, validation rule, and validation text
(to name a few). Each property will improve in
some cases, performance as well as change the
look and feel of data values. This is the first
level of changes that can be applied and act as
defaults when working with related objects used
later on in your design process with the likes of
queries, forms and reports.
4
3. Data type effort It is suggested to use
only text-based data type (as the default) and
not necessarily number-based regardless of
whether its for numeric data storing. It
preserves a bit more of memory and is more
advantageous with overall performance. Only
consider non-text based values if there is a real
need for it namely, to calculate values elsewhere
in the database.   4. The use of field size
Selecting a suitable field size and data type
makes the table adaptable and leaves little room
or space for incorrect values. Ideally pick the
smallest field size and data type as it helps in
the keeping your tables optimised and perform
better. Note that only Text and Number data
types can have their fields sizes adjusted and
that all other data typesare fixed which can be a
waste of memory.
5
5. Enhance performance through indexing
Indexes sort and prioritise the data sensibly
according to its data type and if it is applied
well, then it can significantly enhance
theoverall performance. developers should not
just set multiple indexes because can hamper the
data-processing in general. Users still need to
knowthat a Primary Key index willautomatically
prioritise the data without having to manually
work on it and that most of the tables in your
Access database should usethePrimary Key.   6.
AutoNumber restrictions If you want to
apply a unique number (or counter value) for the
records in your database tables, you should use
the AutoNumber field and it characteristic can
(and assumed to be a default) function as a
Primary Key. It works as a surrogate key and a
table can have just one AutoNumber field. Even
though it begins with the value 1 by default,
users can edit the value as well before it is
used in anger.  
6
7. The use of analysis tools To improve
the design of your tables, you can use theTable
Analyzer wizard tool and the Performance
Analyzer tool. The Table Analyzer assists in
examining the table(s) and recommends a number of
potential changes which you can decide to accept
and apply. While Performance Analyzer evaluates
the whole database and gives you suggestions for
enhancingthe overall design.   8. Table
properties Much the same as fields,
tables also have properties to describe their
purpose. You can get to the table properties by
opening the table in Design view. There you have
the choice of properties under the View menu.
There are properties such as Order By and
Subdatasheet Name which indicates the order of
sorting in MS Access. For the most part, sorting
is executed according to the Primary Key
although you can change according to your
preferences. You can also disable the
sub-datasheet name property by setting it to
None. The additional benefit is the ability to
validate between two or more fields (using
logical expressions) to help control and store
logical data values.
7
On the off chance that you ever experience
a data error while working on an Access database,
you may want to take regular back-up and have
several restore points to work with. Also, learn
to regularly run a Compact and Repair action
which is a built-in tool for keep your data
organised and error-free!   Bonus tip
For a field name where it appears that more than
one word has been applied and spaces can be
frowned upon as poor practice, consider using the
underscore character ( _ ) as the separator or
Initial capitalise each word with no spaces to
make it easier to read.
8
Contact us
Ben Beitler
ben_at_accessdatabasetutorial.com
(44) 7881
502400
United Kingdom
London
https//www.accessdatabasetutorial.com/