How do you create a student table in Access?
Tables are the foundation of an Access database. Access stores data in tables. This lesson teaches you how to create a table, add fields to a table, assign data types to fields, and set field properties. A table is a set of columns and rows. Each column is called a field. Within a table, each field must be given a name and no two fields can have the same name. Each
value in a field represents a single category of data. For example, a table might have three fields: Last Name, First Name, and Phone Number. The table consists of three columns: one for last name, one for first name, and one for phone number. In every row of the table, the Last Name field contains the last name, the First Name field contains the first name, and the Phone Number field contains the phone number. Each row in a table is called a record. All of the data in a table should refer to the same subject. For example, all of the data in the Employees table should refer to employees, all of the data in the Students table should refer to students, and all of the data in the Courses table should refer to courses. You can view an Access database as a collection of related tables. For example, in a database that contains tables for Employees, Students, and Courses, the Employees table lists the employees, the Students table lists students, and the Courses table lists the courses students can take. After Access creates a blank database, it opens in Datasheet view and makes available the tools you need to create a table. Datasheet view displays a table as a set of columns and rows. When you view a blank database for the first time in Datasheet view, you see a column named ID. This column is by default the primary key field. A primary key is a field or combination of fields that uniquely identify each record in a table. No two records in a table should have the same values in every field. For example, the following should not occur in a table.
In the real world, it is possible to have two people from the same city with the same first and last name. In cases like this, you can use the ID field as the primary key field and use it to make each record unique. The ID field has a data type of AutoNumber; as a result, Access automatically creates a unique number for each record in the database. The resulting table will look like the one shown here.
Access provides several methods for creating a table. One method is to use the Rename option with the Add New Field column labelto give each column the field name you want it to have and then to type or paste your data into the table. Field names can include letters, numbers, and spaces and can be up to 64 characters long. When choosing a field name, try to keep it short. When you save your table for the first time, Access gives you the opportunity to name your table. Each table name must be unique; hence, two tables in the same database cannot have the same name. The table name should describe the data in the table; can consist of letters, numbers, and spaces; and can be up to 64 characters long. When choosing a table name, try to keep it short. You can save a table by clicking the Save button on the Quick Access toolbar or by right-clicking the Tables tab and then choosing Save from the menu that appears. To add fields to a table:
Or
Name and Save a TableAfter you create a table, you must name and save it. To name and save a table:
Understanding Data TypesIn Access, you use data types to specify the type of data each field can capture. A field with a data type of text can store alphabetic characters and numbers. Generally speaking, you cannot perform mathematical calculations by using a text field. For example, you can use a text field to store a street address. Unless you do some manipulation, you cannot use the numbers in the street address in mathematical calculations. You will not be able to sum or average the numbers in an address field, which is fine, because you probably do not want to. Alternatively, you can assign a Test Score field a data type of Number. You can enter numbers into the field and then average, sum, or perform other calculations with the numbers. However, you cannot enter an alphabetic character in a number field.
After you create the fields for a table, you can enter data by typing in each field. As you type, Access assigns a data type to each field based on your entry.
Explicitly Assign Data Types and FormatsYou may want to change the data type Access assigned to a field, or you may want to explicitly assign a data type to each field. You can do so by choosing the Datasheet tab and then selecting the proper option in the Data Type field in the Data Type & Formatting group. Some data types allow you to select the formatting you want. By formatting, you determine how data in a field displays. For example, if you choose a data type of number and a format of Euro, any number you enter will appear with a Euro sign in front. Windows regional settings enable you to display information such as dates, times, and currency that match the standards or language used in the country in which you live. For example, if you live in the United States, the currency setting uses a dollar sign.
Use the Windows Control panel’s Regional and Language options to view or change regional settings.
To explicitly assign a data type or format to a field:
Tip: If you want every record in a field to be unique, check the Unique box on the Datasheet tab in the Data Type & Formatting group. If you do not want the user to leave a field blank, check the Is Required box. Tip: In the Data Type & Formatting group, there are several formatting options you can apply to numbers. If you want to use the Currency format, click the Currency button ; if you want to use the Percent format, click the Percent button ; if you want to use a Comma number format, click the Comma button ; or if you want to increase or decrease the number of decimal place, click the Increase Decimal or Decrease Decimal button .Tip: You can create a new table at any time by activating the Create tab and then clicking Table. Understanding Design ViewAccess provides several ways to view the objects in your database. You can use Design view to create or modify an Access table. You can use the View button on the Home tab or the Table Design button on the Create tab to change to Design view. Using Design view is the preferred method for creating a table because it provides you with the most options and enables you to precisely define your table. In addition to selecting a data type, you can set all of the following options in Design view.
To use Design view to create a new table:
To set field properties:
You can use Design view to create or modify a table. After you finish the task, you must save the table by clicking the Save button on the Quick Access toolbar.
Views are different ways of looking at the same object. Tables have four views: Datasheet view, Pivot Table view, Pivot Chart view, and Design view. You use Datasheet view to create a table, edit data, or view data; Pivot Table view to create a pivot table; Pivot Chart view to create a pivot chart; and Design view to create a table or modify an existing table. To change the view:
Create a Lookup ColumnIf a field can contain a finite list of values, you can create a Lookup Column and users can select the value they want from a list. For example, if the employees at a school can only work in one of the following departments: Administration, Computer Science, English, History, or Math. You can create a table Departments table that lists the departments and then use the list in the Employee table to assign each employee to a department.
Access has a wizard to help you create lookup columns. Creating a Lookup column creates a relationship between two tables. See the section Create Relationships in Lesson 3 to learn more about relationships. To use the Lookup Wizard to create a lookup column:Open the Lookup Wizard
Select your table or queryA lookup column can be based on a table, a query, or a list of values you type. If you base your lookup column on a table or query, you must create the table or query before creating the lookup column. A query is a list of rows and columns based on one or more tables. A query only displays the rows and columns you specify.
Select fieldsYou choose the fields you want to appear in your lookup column. Be sure to include the primary key.
Sort fieldsThe Lookup Wizard allows you to sort the records in a lookup column. You can display records in order, either ascending (alphabetical from A to Z, lowest number to highest number, earliest date to latest date) or descending (alphabetical from Z to A, highest number to lowest number, latest date to earliest date). You can also sort within a sort. For example, you can sort by state and then within each state by city, and then within each city by street address. If you are creating a sort within a sort, create the highest level sort on line one, the next level sort on line two, and so on. In the state, city, and street address example, you create the state on line one, the city on line two, and the street address on line three.
Adjust column widthsA key column is the column that connects one table or query to another table or query. For example, you can use the Department ID field in the Employees table and the Department ID field in the Departments table to connect the two tables. You may, however, want to display the name of the department when you view the table but not the department ID; if so, leave the Hide Key Column box checked.
Specify the Key Field (if you deselected Hide Key Column)A key field is a field that uniquely identifies a record. If you deselected Hide Key column, you must tell Access which field is the key field.
Name the columnField names appear at the top of each column. On this page of the Wizard you tell Access what you want to name your lookup column. In Access 2007, multiple values can appear in a field; click the Allow Multiple Values checkbox if you want to allow multiple values.
How do you create a student table?Solution. Creating student table. ... . Describe the structure of the table. ... . Insert few records into student table. ... . Add column to the student table (that is phone_no). ... . Modify the column name of phone_no to student_no. ... . Rename the table name to student_info. ... . Delete any records from the table.. How do you create a student database in Access?Create a database in Access. Open Access. If Access is already open, select File > New.. Select Blank database, or select a template.. Enter a name for the database, select a location, and then select Create. If needed, select Enable content in the yellow message bar when the database opens.. How do you create a table in Access database?In the Open dialog box, select the database that you want to open, and then click Open. On the Create tab, in the Tables group, click Table. A new table is inserted in the database and the table opens in Datasheet view.
How do you create a student database through table Wizard in MS Access?Create a table using the table wizard. From the tables list, click the New button and double-click Table Wizard.. Select the table category (Business or Personal).. Select the type of table from the Sample Tables list.. Choose the Fields in my table by using the add, add all, remove, and remove all buttons. ... . Click Next.. |