Creating tables and forms The basic units

Creating tables and forms

The basic units in database are tables and the relationships between them. • you can create a database by building your own tables, forms, reports, and other database objects. In most cases, this involves one or both of the following: • Entering, pasting, or importing data into the table that is created when you create a new database, and then repeating the process with new tables that you create by using the Table command on the Create tab. • Importing data from other sources and creating new tables in the process.

You can use two modes: “Design view” and “Datasheet view” • «Design view mode» uses for create and change structures of the table • » Datasheet view mode. » uses to view, add, delete, and edit data in a table

In Microsoft Access, there are four ways to create an empty table. • Using Wizard database to create a an entire database containing all the required reports, tables and forms, in a single operation. Wizard database creates a new database, it can not be used to add new tables, forms, reports in an existing database. • You can used the Table Wizard to quickly create a table from sample tables and fields. • Enter data directly into an empty table in Datasheet view. • Identification of all parameters of a table layout in «design view» mode

Field types in Microsoft Access • Fields in a Microsoft Access database can be of many different data types. Access supports different kinds of text, numbers, dates and some more special data types.

• Text • Use for alphanumeric characters, including text, or text and numbers, that are not used in calculations (for example, a product ID). • Up to 255 characters • Memo • Use for text greater than 255 characters in length, or for text that uses rich text formatting. Examples include notes, lengthy descriptions, and paragraphs that use text formatting, such as bold or italics. • Up to 1 gigabyte of characters, or 2 gigabytes of storage (2 bytes per character), of which you can display 65, 535 characters in any single control.

• Number • Use for storing numeric values (integers or fractional) that will be used in calculations, except for monetary values. • 1, 2, 4, 8, or 12 bytes (16 bytes when used for a replication ID) • Currency • Use for storing monetary values (currency). • 8 bytes • Auto. Number • Use for generating unique values that can be used as a primary key, which Access inserts when a record is added. Note that Auto. Number fields can be incremented sequentially or by a specified increment, or assigned randomly. • 4 bytes (16 bytes when used for replication ID)

Date/Time • Specifies a specific point in time (Date and time of day). Dates are internally stored as an 8 byte double precision floating point numbers, so the range is virtually unlimited. • 8 bytes Yes/No • A boolean value that can be either on or off, displayed as a check box in Microsoft Access. Displayed as 0 or 1 in MDB Viewer. • 1 bit (0. 125 bytes) OLE Object • An OLE object field can contain documents, such as images, text files, Microsoft Word files or Microsoft Excel files. • The size of this field is limited to 1 GB.

• Attachment • Use for storing binary files (that is, files that you cannot read by using a text editor), such as digital images (photos and graphics) or files created by using other Microsoft Office products. • For compressed attachments, 2 gigabytes. For uncompressed attachments, approximately 700 kb • Hyperlink • Use for storing hyperlinks, which provide single-click access to Web pages through a URL (Uniform Resource Locator) or to files through a name in UNC (universal naming convention) format. You can also link to Access objects that are stored in a database • Up to 1 gigabyte of characters, or 2 gigabytes of storage

• Lookup Wizard • Use to start the Lookup Wizard so that you can create a field that uses a combo box to look up a value in another table, query, or list of values. Note that Lookup Wizard is not an actual data type. • If the lookup field is bound to a table or a query, the size of the bound column. • If the lookup field is not bound to another column (and stores a list of values), the size of the Text field used to store the list.

Create a form in Access • Forms in Access are like display cases in stores that make it easier to view or get the items that you want. • There are several ways of creating a form in an Access: • Create a form by using the Form tool

• Create a form by using the Form tool • You can use the Form tool to create a form with a single mouse-click. When you use this tool, all the fields from the underlying data source are placed on the form. You can start using the new form immediately, or you can modify it in Layout view or Design view to better suit your needs • Create a split form by using the Split Form tool • A split form gives you two views of the data at the same time— a Form view and a Datasheet view

• Create a form by using the Form Wizard • To be more selective about what fields appear on your form, you can use the Form Wizard instead of the various form-building tools previously mentioned. You can also define how the data is grouped and sorted, and you can use fields from more than one table or query, as long as you specified the relationships between the tables and queries beforehand. • Create a form by using the Blank Form tool • If the wizard or the form-building tools don’t meet your needs, you can use the Blank Form tool to build a form. This can be a very quick way to build a form, especially if you plan to put only a few fields on your form.

Layout view and Design view • Layout view is the most intuitive view to use form modification, and it can be used for almost all the changes that you would want to make to a form in Access. • In Layout view, the form is actually running. You can see your data much as it will appear when you are using the form.

• Design view gives you a more detailed view of the structure of your form. You can see the Header, Detail, and Footer sections for the form. • The form is not actually running when it is shown in Design view. Therefore, you cannot see the underlying data while you are making design changes. However, there are certain tasks that you can perform more easily in Design view than in Layout view. You can: