Access provide many ways to create tables and queries.Through SQL commands, you can also create tables, queries, forms, and reports. SQL stands for Structured Query Language which is being used extensively for creating, handling and manipulating RDBMS structure. Its syntax is very easy to comprehend but becomes complex when you need to handle colossal database. In this post we will be creating a simple query with SQL commands, which eventually creates a simple table.
To start off with, Launch Access 2010 and give database an appropriate name, click Create.
It will open a blank table, close it down to remove it, navigate to Create tab and click Query Design.
Upon click you will see Show Table, close this dialog box.
Now head over to Design tab and click Data Definition from Query Type group.
You will see Query tab1 will open up, here you can write SQL commands. As we are intended to create simple table so we will write SQL commands to create table and important field labels with their respective data types.
CREATE TABLE Student
ST_ID int PRIMARY KEY,
In SQL commands, we started off with CREATE TABLE, this will create a table with the name specified Student. The first field label ST_ID with data type int (Integer), we have also specified it as PRIMARY KEY (unique identifier and non-repetitive). The next field label Name has data type CHAR(255) or Text. The last field label Present has data type YESNO, which confines your database user to enter either Yes or NO.
As you can see in SQL commands, we have enclosed field labeling & data type section in parenthesis and place semicolon at the end to specify that we have stop entering more field labels. Syntax error would erupt if you wont comply with these standards
Now save the query by right-clicking Query1 tab and hit Save. Specify an appropriate name for it.
You will see the newly created query through Data Definition in the left sidebar of the database window.
Now run this query, you will see Access info message pop-up asking confirmation of running data-definition query. Click Yes to continue.
Upon click it will do as specified in SQL command, it will create a table by the name of Student, as shown in the screenshot below.
On opening newly created table, you will see all the field labels as specified in SQL commands. As shown in the screenshot below that it has all the column labels; ST_ID, Name, Course, Marks, Grade, Phone, and Present.
Now switch to Design view and check the Data types. From here on you can apply new constraints for table data fields and add more field labels etc.