Working with databases/Create a database/Data types

From WikiEducator
Jump to: navigation, search
OtagoPoly Logo S.png


Working with databases
Create a databaseIntroduction  |  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:

Text
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.)
Memo
Similar to a text field but can store a maximum of 65,535 characters. Good for storing long notes.
Number
Used to store numbers for calculations only. For now, use the default type of Long Integer.
Date/Time
To store date and time.
Currency
You do not need to add the dollar sign to this type. Access will assign it automatically.
Autonumber
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).
Yes/No
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)
Hyperlink
Allows you to link to other objects like files, web pages, etc.
Lookup
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.

Choosing data-types

Here is a partially completed list of the fields in the Books database:

Field Data-type
Year published
Number of pages
Paperback or hardback Yes/No
Author Text
ISBN number
Item to be ordered
Price purchased for Currency
Price sold for (retail price)
Book Title



OP icon activity.gif

Activity

  1. We need to decide on the data-types of all the fields used in our table. Some of these have been done for you.

    Jot down on a sheet of paper what you think are the appropriate data-types that are missing from the table.
  2. When you have completed this, check your ideas against our model answers.
  3. Now open your bookshop database.
  4. Open the table in Design View.
  5. Click in the data-type column of the field name that you want to change. From the drop-down menu, choose the data-type that you have decided on. You should end up with something looking like the following:

    Access-datatype2.png

  6. Save and close your database.






OP icon exclamation.gif

Please note

If you have any questions about what you have done, or do not understand this exercise, please review the earlier sections of this unit.




Previous.png | Next.png

Retrieved from "https://wikieducator.org/index.php?title=Working_with_databases/Create_a_database/Data_types&oldid=852338"