Working with databases/Create a database/Data types
|Working with databases|
|Create a database||Introduction | Table design | Data types | Field properties | Primary keys | Adding information | Records and tables | Key points | Assessment|
Field data types explained
As we have previously said, a data-type is used by Access to control the way data is stored, what can be done to the data, even the values you’re allowed to enter in. The following data-types are available in Access:
- This is the default type for fields. We can type anything here, including numbers provided that you’re not going to need them in sums or formulas. (ie: you could use this to store a phone number or a catalogue number, but not a price if you needed to add GST at a later stage.)
- Similar to a text field but can store a maximum of 65,535 characters. Good for storing long notes.
- Used to store numbers for calculations only. For now, use the default type of Long Integer.
- To store date and time.
- You do not need to add the dollar sign to this type. Access will assign it automatically.
- A number type that is automatically created by the database, and is unique to each record. The autonumber cannot be updated by the user. (Used for things like membership numbers for a club membership).
- Can be used to store yes/no true/false, etc
- OLE object
- OLE stands for Object Linking and Embedding. This can be used to connect Excel or Word documents, picture and sound files to our database. (This is an advanced concept that is not used in our study)
- Allows you to link to other objects like files, web pages, etc.
- Can be used to link to a record in another table.
Be careful when choosing your data-types. Once you have created your table and started inputting data, it may be impossible to change the data-types without losing the information stored in your table.
Which data-type should I use?
Sometimes it’s obvious, for example, names can use the text data-type. The date that someone joined, would be the Date/Time data-type.
Phone numbers should use the text data-type. The reason for this is if you have a cellphone number like 012 548 9856, the number data-type will strip off the leading zero.
Here is a partially completed list of the fields in the Books database:
|Number of pages|
|Paperback or hardback||Yes/No|
|Item to be ordered|
|Price purchased for||Currency|
|Price sold for (retail price)|