The next step is the Data Design Field for Your Microsoft Access Database.
From your reports, break down into natural units that will be the foundation for your Access database design. In simplified terms, an orders database system might consist of many tables but a core process like an order transaction could be made up from five tables (as described further in this article).
The Data Design (your fields) should start to get a little easier to complete this task providing you have spent enough time on the previous two steps of this series. This step handles each table that has been sketched out, a list of fields that will be defined and their best matching and qualifying data types
For example, Lets say you have determined that there are five tables for an example ‘Order‘ document which are:
- Order Details
Take each table and expand on this to list all the fields making sure the first field will act as the unique value and ID for a record. So the ‘Customers‘ table first listed field might be called ‘Customer ID‘.
You need to capture all the fields that you believe will make up the profile of a customer across all reports bearing in mind not all fields will be required for all reports.
As part of the database normalisation theory, consider fields to handle the smallest element of data that you would normally expect to use. For example, the address of a company would not be stored in one field called ‘Address’ but instead across five fields (‘Address Line 1′, ‘Address Line 2′, ‘City’, ‘Postcode’ and ‘Country’. This will help in grouping, sorting, filtering and calculating records at a later point.
For each field listed, decide on the best suited data type as it will drive the use of that field when reporting on it later on. So for example, will the ‘Quantity’ field need to be a number? Do you intend to calculate with it at some point?
A field may appear as numeric like the ‘Tel No.’ field but would probably be stored as the default ‘Text’ data type unless of course you intend to calculate with it!
Choosing the best matched data type will also start to validate the value you can enter. So you know a ‘Number’ data type cannot accept a non-numeric character. The ‘Date/Time’ data type validates only date and time (including leap years too!).
So, an example of the Orders table may have a list of the following fields and data types with sizes where applicable:
Field: Order ID, Data Type: Number (Long Integer)
Field: Customer ID, Data Type: Text (5)
Field: Shipper ID, Data Type: Number (Long Integer)
Field: Order Date, Data Type: Date/Time
Field: Required Date, Data Type: Date/Time
Field: Shipped Date, Data Type: Date/Time
Field: Order Amount, Data Type: Currency
Field: Freight Amount, Data Type: Currency
Field: Despatched, Data Type: Yes/No
Field: Invoiced, Data Type: Date/Time
The above table gives you the idea and other fields would probably come to light during this step analysis process.
Repeat this for each table and double-check that all fields have been covered against each report.
Another tip for you! When you name and label your fields for each table, try and keep the names of each field as short as possible and ideally with no spaces. This will help later when writing queries and reports and in particular when getting to the levels of using macros or even VBA (Visual Basic for Applications) at the automation level.
I invite you to keep up to date with my articles and eBooks which covers a lot of details and can be found at https://AccessDatabaseTutorial.com/eBooks.
From Ben Beitler – “Your Access Database Expert”
Article Source: http://EzineArticles.com/?expert=Ben_S_Beitler
This is one of seven key steps to planning and desigining an Access database as the author (me!) steps you through the design methodologies which he uses.