Home

Creating a Record

 

Introduction

Consider a table created with the following code:

CREATE TABLE Employees
(
    EmployeeNumber integer,
    FirstName Text(50),
    LastName Text(50),
    Title varchar(100),
    HourlySalary Currency
);

In the SQL, data entry is performed using the INSERT INTO expression, followed by the table name, followed by the VALUES keyword, and followed by the values in parentheses. The syntax to use is:

INSERT INTO TableName VALUES(Column1, 
			     Column2,
			     Column_n)

In the above syntax, the value of each field of the column must be entered in the exact order of the columns as they were created in the table. Here is an example:

INSERT INTO Employees
VALUES(495842, "Anne", "Ladder", "Regional Manager", 52.28);

Alternatively, you can specify the columns whose values you want to create. To do this, add an opening and a closing parentheses to the right side of the name of the table. Then, when performing data entry, in the parentheses of the VALUES attribute, type the values in the order specified in the parentheses of the table name. This random order of fields presents another advantage: it allows you to specify only the fields whose values you want to enter.

Here is an example:

INSERT INTO Employees(Title, LastName, FirstName)
VALUES("Sales Manager", "Simms", "Claudine");

Using an Append Query

You can use an Append Query to create a table filled with records from another existing table.

To start an Append Query, start a query in the Design View. In the Query Type section of the Ribbon, click the Append button Append. You will be presented with a dialog box that expects you to either select the table to which you will add the records, or to specify the name of a new table that would receive the records. If you want to add the records to an existing table, you can select it from the combo box:

Append

As you may imagine, appending a record consists of adding a record to a table. If you want to do this in SQL, simply create an INSERT statement as we saw with the Make Table Query.

When you finish, Microsoft Access would create a SQL statement behind the scenes and use it to create the records. Here is an example:

INSERT INTO Cars ([Tag Number], Make, Model, [Car Year], 
		   Category, Doors, Available, Condition )
SELECT  [Cars Reviewed and Approved].[Tag Number], 
	[Cars Reviewed and Approved].Make, 
	[Cars Reviewed and Approved].Model, 
	[Cars Reviewed and Approved].[Car Year], 
	[Cars Reviewed and Approved].Category, 
	[Cars Reviewed and Approved].Doors, 
	[Cars Reviewed and Approved].Available, 
	[Cars Reviewed and Approved].Condition
FROM 	[Cars Reviewed and Approved];

 

 

Home Copyright © 2008-2016, FunctionX, Inc.