Scribd
Upload
66 views9 pages

Skills For Data Manipulation

This document provides information about various tasks in Microsoft Access related to data types, queries, reports, forms, and relationships. It discusses setting data types when importing a CSV file, creating queries to search and calculate fields, using queries to filter information displayed in reports, sorting and formatting reports, adding buttons and records through forms, and different query search criteria operators like equals, not equals, less than, greater than, between, like, not like, is null, and is not null.

Uploaded by

May Sabal Myo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
66 views9 pages

Skills For Data Manipulation

This document provides information about various tasks in Microsoft Access related to data types, queries, reports, forms, and relationships. It discusses setting data types when importing a CSV file, creating queries to search and calculate fields, using queries to filter information displayed in reports, sorting and formatting reports, adding buttons and records through forms, and different query search criteria operators like equals, not equals, less than, greater than, between, like, not like, is null, and is not null.

Uploaded by

May Sabal Myo
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Data Type( Field Type) for MS Access

Import/Table Tasks (import .csv file as text doc)


 Open Database
 Import .csv file
 Create new table
 Set the field names
 Set Data Types (at advanced level)
 Change field properties (decimal places or Yes/No etc)
 Add/Insert new records (provided by test paper)
 Update record
 Delete record
What is a query? (Finding, Searching data)
Always Create from table
A query is a way to search through the data stored in the database to produce
meaningful results.
In this question the query needs to search the database in 2 ways:
1. Search the information where Number is less than 2 AND Stock Item is ‘Yes’.
2. Includes a field called ‘Order’ which is calculated at run-time and multiplies the
Price field by 3.

What is a report?
Always Create from query
A report is a method we use to display our information in the clearest way
possible. Reports are essentially the output of the database.
The report in this question only wants us to display information that meets
certain criteria and, therefore, we also need to create a query.

Query tasks (Queries are always created from table)


 Creating Query
 Run the Query
 Creating some query search criteria (>, Not, Like)
 Set format of calculated field (New:[Existing]+3)
 Show/Hide field (by ticking checkbox)
 Sort the order of a field
 Check the result (Datasheet view)
 Show total row (Sum, Average, Maximum,…)
Order : [Price]*3
newFieldName: [existingFieldName]-20
DiscountPrice: [OldPrice]*0.3
Report tasks (Reports are always created from queries)
 Sort order of a field (Ascending, Descending)
 Select Portrait/Landscape
 Resize fields to fit the page (move fileds)
 Change headings
 Add calculations (=SUM, =AVG, =MAX, =MIN, =COUNT) to report footer
 Add name and numbers to page footer
 Add summery field into report footer
 Alter the format of the summary field
 Label (special kind of report, designed to provide a short, summary of
information) [You are required to fit two labels side by side].

=AVG([Order])

Order:[Price]*3
Form Tasks
 Create New form
 Adjust alignment in text box
 Add Buttons (Next, Previous, Save)
 Add New Record using form

Export data

Summarize data

Database Relationship
Query search criteria exercises
1) Exactly match a value, such as China
Like “China”
2) Do not match a value, such as Mexico
Not Like “Mexico”
3) Begin with the specified string, such as U
Like “U*”
4) Do not begin with the specified string, such as U
Not Like “U*”
5) Contain the specified string, such as Korea
Like “*Korea*”
6) Do not contain the specified string, such as Korea
Not Like “*Korea*”
7) End with the specified string, such as "ina"
Like “*ina”
8) Contain null (or missing) values
Like Null
9) Do not contain null values
Not Like Null
10)Contain zero-length strings/ blank
Like Null / Like “”
11)Match one of two values, such as USA or UK
Like USA or UK
12)Contain a value smaller than 100
<100
13)Contain a value larger than 100
>100
14)Contain a value that falls within 50 and 100
Between 50 and 100
15)Exactly match a value, such as 2/2/2006
#2/2/2006#
16)Do not match a value, such as 2/2/2006
Not #2/2/2006#
17)Contain values that fall before 2/2/2006
<#2/2/2006#

You might also like