MS Access-Creating Table

Contents
MS Access - Create Database ......................................................................................................2

Create Blank Database ................................................................................................................8
MS Access - Data Types ........................................................................................................... 10
MS Access - Create Tables ........................................................................................................ 12
MS Access - Adding Data ......................................................................................................... 24
MS Access - Query Data ........................................................................................................... 28
MS Access - Query Criteria....................................................................................................... 32
MS Access - Action Queries ..................................................................................................... 38
Create an Append Query ........................................................................................................... 38
Create an Update Query .................................................................................................... 44
Create a Delete Query ........................................................................................................... 46
MS Access - Parameter Queries ................................................................................................ 49
Example............................................................................................................................... 50
MS Access - Create Database
Create Blank Database
MS Access - Data Types
If you use previous versions of Access, you will notice a difference for two of those data
types.
 In Access 2013, we now have two data types — short text and long text. In
previous versions of Access these data types were called text and memo.
 The text field is referred to as short text and your memo field is now called long
text.
 Here are some of the other more specialized data types, you can choose from in
Access
MS Access - Create Tables
MS Access - Adding Data
MS Access - Query Data
A query is a request for data results, and for action on data. You can use a query to
answer a simple question, to perform calculations, to combine data from different tables,
or even to add, change, or delete table data.
 As tables grow in size they can have hundreds of thousands of records, which
makes it impossible for the user to pick out specific records from that table.
 With a query you can apply a filter to the table's data, so that you only get the
information that you want.
 Queries that you use to retrieve data from a table or to make calculations are
called select queries.
 Queries that add, change, or delete data are called action queries.
 You can also use a query to supply data for a form or report.
 In a well-designed database, the data that you want to present by using a form or
report is often located in several different tables.
 The tricky part of queries is that you must understand how to construct one before
you can actually use them.
MS Access - Query Criteria
MS Access - Action Queries
In MS Access and other DBMS systems, queries can do a lot more than just displaying
data, but they can actually perform various actions on the data in your database.
 Action queries are queries that can add, change, or delete multiple records at one
time.
 The added benefit is that you can preview the query results in Access before you
run it.
 Microsoft Access provides 4 different types of Action Queries −
o Append
o Update
o Delete
o Make-table
 An action query cannot be undone. You should consider making a backup of any
tables that you will update by using an update query.

Create an Append Query

You can use an Append Query to retrieve data from one or more tables and add that
data to another table. Let us create a new table in which we will add data from
the tblEmployees table. This will be temporary table for demo purpose.
Let us call it TempEmployees and this contains the fields as shown in the following
screenshot.
In the Tables tab, on the Show Table dialog box, double-click on
the tblEmployees table and then close the dialog box. Double-click on the field you want
to be displayed.
Create an Update Query
You can use an Update Query to change the data in your tables, and you can use an
update query to enter criteria to specify which rows should be updated. An update query
provides you an opportunity to review the updated data before you perform the update.
Let us go to the Create tab again and click Query Design.
Create a Delete Query
You can use a delete query to delete data from your tables, and you can use a delete
query to enter criteria to specify which rows should be deleted. A Delete Query provides
you an opportunity to review the rows that will be deleted before you perform the deletion.
Let us go to the Create tab again and click Query Design.
MS Access - Parameter Queries
The best part about queries is that you can save and run the same query again and
again, but when you run the same query again and again by only changing the criteria
then you might consider the query to accept parameters.
 If you frequently want to run variations of a particular query, consider using a
parameter query
 Parameter query retrieves information in an interactive manner prompting the end
user to supply criteria before the query is run.
 You can also specify what type of data a parameter should accept.
 You can set the data type for any parameter, but it is especially important to set
the data type for numeric, currency, or date/time data.
 When you specify the data type that a parameter should accept, users see a more
helpful error message if they enter the wrong type of data, such as entering text
when currency is expected.
 If a parameter is set to accept text data, any input is interpreted as text, and no
error message is displayed.
Example
Let us now take a look at a simple example by creating a parameter query. Let us open
your database and select Query Design in the Create table tab

MS Access-Creating Table

Uploaded by

Copyright:

Available Formats

MS Access-Creating Table

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

MS Access-Creating Table

Uploaded by

Copyright:

Available Formats

Contents

MS Access - Create Database ......................................................................................................2

Create an Append Query

You might also like