Microsoft Access Database: Should We Be Using Calculated Table Fields
With the introduction of Microsoft Access 2010, a new data type field appeared in your table design view which provided a basic way to take fields in the same table and create new expressions as a new dedicated field sitting at the ‘top of hierarchy’ database!
However, should we really be using this new data type and practice at all?
Purists will frown upon this technique and say you should stick with Excel to store real values and expressions together. By doing so, you are breaking the rules of database normalisation and also cause a maintenance headache once your database has been deployed.
There are some considerations to think about that may Continue reading “Microsoft Access Database: Should We Be Using Calculated Table Fields”
Microsoft Access Table Relationships
An ideal database is free of redundant or duplicate data. To achieve that, you must split your data into many subject-based tables so each subject is presented only once. To do that in Microsoft Access, you place common fields in tables that are related. But before you do that, you must need to understand the relationships between your tables, then you can specify these relationships in your Access 2013 database.
In this article, I will elaborate to you the ten things you need to know about table relationships in Microsoft Access.
1. A one-to-many relationship is when one record in the parent table connects to many records in the child table.
To exemplify this, let’s take for example two tables: one for doctors and the other is for patients. A doctor can have several patients. In this case, you will Continue reading “Microsoft Access Table Relationships”
Microsoft Access Validation Rules In Tables
A validation rule restricts or controls what a user can input in a table field. You can think of it as a set of layers which ensures that your users input data properly.
Here’s a quick video tutorial to get you going and very easy to apply.
Microsoft Access provides several ways to validate data: data types, field sizes, table properties, and input masks. Among these, table properties provide specific types of data validation.
One example is setting the required property to YES which results to forcing the users to enter a value in that specific field.
Another technique is by using the Validation Rule property to require specific values, and the Validation Text property to alert your users to any errors. For example, putting a rule such as Continue reading “Microsoft Access Validation Rules In Tables”
Microsoft Access Tutorial: Creating A Many-To-Many Relationship
Let’s revisit a popular aspect to your Microsoft Access database design; Creating a many-to-many relationship.
Firstly, watch this quick video which explain the key components and the starting steps to getting to the many-to-many state for your Access database.
In order to create this type of relationship, it’s now clear we need an interceding or junction table to handle the two-way joins of the outer tables with Continue reading “Microsoft Access Tutorial: Creating A Many-To-Many Relationship”
Microsoft Access Database: Creating Database Field Type Templates
Microsoft Access database applications just gets richer and richer as time goes by and from version 2010, you can now create field types or in effect user defined fields stored via the ‘Save Selection as New Data Type‘ command which is located from the context sensitive fluent ribbon bar Fields tab (Table Tools).
Take a look at a demonstration of this is set up and used in the video tutorial below (less than 4 minutes).
Microsoft Access Database (version 2010 onwards) has a new feature which allows you to preserve existing columns that you can use Continue reading “Microsoft Access Database: Creating Database Field Type Templates”
Microsoft Access Tutorial: How to use Input Masks
Microsoft Access databases provide a wealth of tools and utilities to help control design layouts and data integrity. Working with the latter, you can reduce and even dismiss any programming at all to control data entry to a field in a table or via a form using the Access Input Mask feature.
Take a quick look at this useful Microsoft Access tutorial (using version 2010) on how to set up an input mask.
Very easy to create indeed and the video covers the pre-set options which to be honest is based on the US style conventions for Continue reading “Microsoft Access Tutorial – How to use Input Masks”
Microsoft Access Tutorial – Working With Two Digit Hour Time Formats
Here’s a quick Microsoft Access tutorial video I found about applying formatting to the date/time data type control concentrating on the two digit hour time format that doesn’t always show up in two digit form.
Even if you create a custom date/time format within Microsoft Access, the two-digit hour which is supported using the convention of “hh” via the format property doesn’t always work. It will show a single digit hour even though you’ve specified the right code. Take a look at the video below.
Microsoft Access Tutorial – Working With Two Digit Hour Time Formats
In the video tutorial, it mentioned one of three ways to Continue reading “Microsoft Access Database – Working With Two Digit Hour Time Formats”
How to Use Smart Tags In Microsoft Access 2010
How to use smart tags in Microsoft Access will only be applicable to 3 versions (2003, 2007 and 2010). Forget about the earlier versions and now even with the latest Access 2013, this too has been dropped!
Since most users are still engaged with the main three releases mentioned, I thought it may be worth a re-visit to understand how to apply a smart tags for your fields in a table.
The key decision to consider when a smart tag should be added will be to identify a benefit for a user-friendly list of actions for certain type of information (or value) being stored allowing users to simple choose from a pre-set list of actions.
For example, a contact name field in a table (or form) could be best served to assist with Continue reading “How to Use Smart Tags In Microsoft Access 2010”
How To Create A Database In MS Access And Link To MySQL Database
Check out and follow these easy steps with this video tutorial (less than 2 minutes) to learn how to create a database in MS Access and link it an MySQL database using the ever popular ODBC connection.
The acronym ‘OBDC’ stands for ‘Object Database Connectivity’ and has been around for a very long time now which is now considered the standard for creating a gateway to you MS Access link.
As the video shows, you first establish the database source driver, create an established connection (including a quick test) and then Continue reading “How To Create A Database In MS Access And Link To MySQL Database”
Can A Foreign Key Be Null: Cascade to Null Relationships In Microsoft Access
The first thing to note here when asking the question can a foreign key be Null? is to clarify what a foreign key is, what is meant by the value Null and something known as setting the ‘Enforce Referential Integrity‘ option for your joined tables.
A Foreign Key is way to index a field which can accept duplicate values that are found from the ‘many‘ side of a ‘one-to-many‘ relationship and cannot be left blank.
A Null value is an unknown value or a data entry which has no value to it which can happen with your text and date/time data types. When Continue reading “Can A Foreign Key Be Null: Cascade to Null Relationships In Microsoft Access”