Step 1 : Import Text Files (done by Wibunsak and bilal -Year 11 B2)
Step 2 :datatype(done by Wibunsak and bilal -Year 11 B2)
Step 3: Formatting Fields (done by Wibunsak and bilal -Year 11 B2)
Databases
Databases are one of the most powerful tools that people use to help them handle large amounts of information.
A database is made up of one or more related files (or tables in Microsoft Access).
A file is made up of one or more related records.
Records are made up of one or more related fields.
A field is made up of a group of related characters. It is a piece of information about something.
Tables:
Design tables
Set Format for a table field
Apply & use an input mask for a table field
Understand the purpose of indexes
Use data validation for a table field and for a record
Document a table - Relationships, Documenter report, Object Dependencies
Queries:
Create a Select query in Query Design View
Use AND and OR criteria in a query
Use the Like operator
Use totals in a query
Concatenate text fields
Calculate values
Create a parameter query
Create a Make-Table query
Create an Append query
Create a Delete query
Create an Update query
Create a Crosstab query
Document a query - Documenter report, Object Dependencies
Compact and repair a database
Table Design
You want to design your tables so that:
It is easy to enter and update information.
Data is consistent throughout.
There is no wasted disk space.
The database responds quickly to searches.
How does one do that??
General Principles for Table Design:
One item of data goes into only one field in one table.
A table's fields must be logically related to each other.
The order of the fields is not important.
Choose entries from a list or table whenever possible.
Use field validation to make sure that the data is in the right form.
Field size fits the expected size of the data.
Create indexes for fields that you will be searching on often.
Good table design requires some logic and thinking ahead. Are you wasting any disk space? Will the same value, like a company's name, be spelled the same in all records? If the company's name changes, can you update it in just one place? Will your design allow for fast searches? What if there are thousands of records?
Goals for Table Design
Save disk space
Save time and effort
Avoid data errors and inconsistencies
Keep database functions fast
How to accomplish these goals? You will need to follow a few rules and then think ahead about the future needs of your database.
How do you want to see the data in your fields? Do you want dates to show like 1/4/2006 or January 4, 2006? Do you want a phone number to look like 123-555-6789 or 1235556789? Does a text field need to look like abcd or ABCD?
You can make choices like these in the Table Design View. There are two properties that you can use to control how a field's values show, Format and Input Mask.
Inherited: The Format and Input Mask properties also apply to the field when it is part of a query, form, or report. You can make a different choice in the Design View for the query, form, or report.
Using both: If you define both the Format and Input Mask properties for a field, be sure that they do not conflict with each other! You will see the Input Mask only when you are entering or editing data. The Format is applied only after the record is saved
Format Property
Format: Controls how the value is displayed, but does not affect what is actually stored in the database.
The formatting is applied only after the value has been saved.
What are your choices? It depends on the field's type. Many types have a drop list in the Format property box.
--------------------------------------------------------------------------------
Input Mask Property
Input Mask: Controls how you enter or edit the data.
You can use certain symbols to restrict what kinds of characters are allowed and to show how many are allowed. You can save or not save literal characters like hyphens, spaces, parentheses, and periods as part of the value.
Input Masks are used mostly with Text and Date/Time fields, but can also be used in Number or Currency fields.
After the data is saved, the Format property controls what you see. Think carefully before setting both Format and Input Mask properties for the same field.
Parts of an Input Mask: An input mask has 3 sections, separated by a semi-colon (;)
Example: !999(999)000-0000;0;_ for a phone number with country code and area code.
Part 1: Mask itself, like !999(999)000-0000 in the example above for a phone number.
Once you click in the field itself, you would see: (___)___-____
! = Accept typing starting from the right instead of from the left
0 = You must enter a digit for each 0 in the mask.
9 = You can enter a digit in where you see a 9 in the mask, but you do not have to.
Part 2: Save literal characters in the mask? (like the parentheses and hyphen in the example)
0 = Yes, store characters with the data.
1 = No, do NOT store characters with the data.
Part 3: Placeholder: The character to use to show how many characters to enter.
The example above uses an underscore. You can use any character as a placeholder. To not show a placeholder at all, use "" in the third position.
Input Mask Wizard: The wizard offers masks for common fields like US phone numbers, postal codes, social security numbers. You open this wizard from the button with three dots , which appears when you select the Input Mask box for the field.
In the wizard's Try It box you can enter an example value to see if this mask will work well for the values you expect. To use a mask, you must know what to expect!!
Customize the list: The Edit List button opens another dialog that shows the definition of the selected mask. You can enter a new Description and change the mask. Clicking the Close button adds it to the list.
Symbols for custom Input Masks
Step-by-Step: Formatting Fields
--------------------------------------------------------------------------------
What you will learn: to create a table with Table Wizard
to set the Format property for a field
to enter data using an input mask
to edit data in a field with an Input Mask
to create a custom Input Mask for a field
--------------------------------------------------------------------------------
Start with: , Projects database open.
The story so far:
World Travel Inc. wants to include in their database some information about various projects and which staff members are involved with which projects. You designed two tables in the last lesson but now see that you need a Staff table also.
You will create a Staff table using the Table Wizard and then look at Format and Input Mask properties.
Create Staff Table with Table Wizard
If necessary, switch to the Database Window in your Projects database.
Select Tables and click the New button. The New Table dialog appears.
Click on Table Wizard and then on OK.
The Table Wizard dialog appears with a number of pre-designed tables for common situations.
Select Business, if necessary.
Select Employees, from the Sample tables list. A list of fields from this sample table appears in the center.
You do not need all of these fields. You can rename them or add others after creating the initial table.
Using the list below, click a field in the center list of the Table Wizard. Then click the > button to add it to the list on the right, the fields for your new table. The list in the center does not change.
EmployeeID
SocialSecurityNumber
NationalEmplNumber
FirstName
MiddleName
LastName
Title
EmailName
Extension
Address
City
StateOrProvince
Region
PostalCode
Country/Region
HomePhone
BirthDate
SpouseName
EmrcyContactName
EmrcyContactPhone
Notes
Click on Next.
The next step in the wizard asks you about the table's name and primary key .
Change the table's name to Staff.
Choose to set the primary key yourself.
Click on Next.
The next step in the wizard sets the primary key.
Select the field EmployeeID as your primary.
Choose to use consecutive numbers that MS Access automatically assigns as each record is created.
Click on Next.
The next step tries to identify what tables the new one might be related to.
Access cannot see any relationships. You will be making changes, however that will make the new Staff table related to the ProjectStaff table.
Click on the button Relationships in the dialog.
A new window appears with some choices.
Choose the middle item, One record in the Staff table will match many records in the ProjectStaff table.
Click on OK to close the Relationships dialog.
The Table Wizard window now shows that Staff is related to ProjectStaff .
Click on Next.
The next step asks if you are ready to enter data or do you want to modify the design.
Select to Modify the table design.
Click on Finish.
Your new table opens in Design View.
--------------------------------------------------------------------------------
Enter Data
To see how field properties work, you first need a record!
Switch to Datasheet View for the table Staff.
Enter the following as the first record, exactly as shown:
(Leave the other fields blank for now.)
First Name Luis
Middle Name P
Last Name Perez
Title President
Email Name LuisPerez
Extension ViajesMundiales.net
Address Montevideo 6553
City Buenos Aires
Postal Code 1123
Country/Region Argentina
Home Phone +54 (1) 555-1234
Spouse Name Rosa Perez
Emrgcy Contact Name Rosa Perez
Emrgcy Contact Phone +54 (1) 555-1234
Beware of Undo! The Undo command will wipe out the whole record if it has not been saved! After saving, Undo removes all of the changes that you made since saving the record.
To save a record:
Just change to a different record.
Menu: select Records
Save Record.
Keyboard use the key combo SHIFT + ENTER.
Toolbar: click the Save button .
Before leaving a field, pressing the ESC key will return a field to its previous value.
Note: The person's name is in three fields. The email address is in 2 fields.
Note: The phone numbers in Argentina have a different form than in the USA. It's a good thing that the wizard did not apply a Format or Input Mask property for the phone numbers. Depending on the countries that you need to work with, you might want to split each phone number up into parts, like with the email address and name fields.
Widen columns: Drag the right edge of the heading to the right for any fields that are too narrow to show all of the characters.
Click the Save button to save the record.
--------------------------------------------------------------------------------
Format Property: < (forcing lower case)
Since this table will have records involving several countries, you cannot easily apply formatting to postal codes or phone numbers or even addresses. You can use formatting to make sure that the email address parts are all lower case.
Switch to Design View for the table Staff.
Select the field EmailName.
In the Format box, type < .
Repeat for the field Extension.
Save the table.
Switch back to Datasheet View.
Both of the fields are now in all lower case letters.
(Did you widen the Extension field to see all of the characters?)
Experiment by typing various combinations of lower case and upper case letters in these fields and then changing to a different field. The value is shown in lower case only after you leave the field or save the record.
The Format Property does not change what is stored in the field, only the way it is shown.
--------------------------------------------------------------------------------
Using an Input Mask: Enter Value (Date)
The Table Wizard set both the Format and Input Mask Properties for the field, Birthdate. Sometimes these can conflict with each other, or at least be confusing.
Now you will enter a date into the Birthdate field to see how an input mask works. After you save the date, the Format property displays the date in Short Date format.
Remember: Using the Undo command will wipe out the whole record if it has not been saved!
Click in the Birthdate field.
A mask appears - blanks and slash characters. The cursor is a thick black bar, showing where the next character will go.
Problem: Mask does not appear: Did you click in the field or did you use TAB or arrow keys to get into the field? Sometimes moving with the arrow keys or TAB key does not make the mask appear right away.
Solution: Start typing anyway. Once you start typing the mask appears.
In the Birthdate field type February 14, 1954.
Does your typing show at all?? The letters do not! Letters are not allowed by the input mask: 99/99/00;0
Explanation of the mask 99/99/00;0
Where you see 9 in the mask, you can type a digit or a space.
Where you see a 0, you must type a digit, and not a space.
BUT, if you want to type just one digit for a month or day, it must be in place of the second 9, not the first one.
So... you can type: space 5 space 4 6 4 and Access will show the date 5/4/64. But if you type 5 space 4 space 6 4, then Access will show an error message and refuse your date!
Details on custom Input Masks
Problem: February 14, 1954 does show in the field
If you delete a value from a field, you may be able to type values that do not match the input mask... but the value will not be accepted.
If you save the value by moving to a different field or saving the record, then a message appears that says that the value is not appropriate for the input mask.
Solution: Click on OK and then delete the date. Try again.
Click in the Birthdate field and type 2141954.
Before you leave the field to save this value, the mask displays it as 21/14/95 and ignores the final 4 altogether! What is the 21st month, anyway!?
Press the TAB key to move out of the field and save the value.
A message appears that says your value is not valid. Your date is not a real date and Access recognized that when it tried to save it.
Select the date and press the Delete key to erase the value.
In the Birthdate field type 2/14/1954, including the slashes, starting from the left. Watch the mask as you type!
Whoops. These numbers do not fit the mask either! Typing the slash moved the cursor to the next section of the mask, but only the first two digits of the year fit in the mask.
Press the TAB key to move to the next cell and save the value.
The date is rewritten! Access did not remember the 54 and thought you meant 2019 for the year. Not good!! Even though this one is a legitimate date, its the wrong date.
Let's try again.
The mask requires 2 digits for the month and 2 for the day and 2 for the year.
In the Birthdate field type 021454.
Finally! The date is the one we wanted.
Press the right arrow key to move to the next field.
The field is reformatted, without the leading zero, in Short Date format. This is the Format Property at work!
In this case the difference is subtle, but it causes a big confusion if you need to edit the date.
--------------------------------------------------------------------------------
Using an Input Mask: Edit Value (Date)
When the Format Property formats a value differently from the input mask, trying to make changes can be frustrating! You must enter your values as the mask requires. If the mask does not show, you may have a hard time figuring out what is wrong.
Drag across the 2 in the Birthdate to select it.
Type a 3 and then click in another field to save the change.
A message appears that says your value is not appropriate for the input mask.
Even though the date is being displayed as 2/14/54, to edit it, you must match the mask, not what you see!! Confusing!!!
Try again, replacing the 2 with 03 AND replace 1954 with 54.
TAB or arrow or click on another field to save the change.
The date is changed to 3/14/54.
This is all VERY annoying... and crazy-making if you do not know what is going on!
--------------------------------------------------------------------------------
Create a Custom Input Mask
Access provides a wizard for creating some common input masks plus you can create a custom mask using symbols. Your mask can demand a certain number of characters or allow for fewer. Your mask can require only numbers or only letters letters or it can allow any kind of character.
Now you will see the effect of an input mask when the data does not match.
Input Mask Vs. Existing Data: Creating an input mask does not cause an error if data that is already in the table does not match the mask. Only when you enter or edit data will an error occur.
Switch to the Design view of the Staff table.
Click on the field StateOrProvince and then in the box for the property Input Mask.
Type AA;;@ in the box as the mask definition.
The A means that you are required to enter a letter in this position, instead of a number or another character. There are no literal characters in this mask, so the second section of the definition is blank. The @ symbol will be the placeholder. This is easier to see than an underscore, but more confusing to a new user.
Caption: The value in the Caption property will be the column heading (State/Province) instead of the name of the field (StateOrProvince). With a caption you can use characters that are not allowed in the name of a field, like the slash /character.
Save the table,
Switch back to the Datasheet view.
In the first record, click in the left side of the field State/Province, but not on the divider line. The first placeholder @ is selected.
Is it obvious to you that you are to type two characters only? Probably.
Is it obvious that they must be letters and not numbers or other characters? Probably not!
Problem: Whole cell was selected instead.
What happened: You caught the border of the cell when you clicked.
Solution: Click out of the cell to deselect it and try again.
Problem: The second placeholder was selected instead.
Solution: Press the left arrow key to move left one character.
Problem: No @ symbols show.
Solution: Click out of the cell to deselect it and try again.
ype Tenn, which is a common abbreviation for the state of Tennessee, USA.
Only the first two characters were kept. No error message!
Delete the "Te"
Type tn , which is the actual postal abbreviation for Tennessee, and press TAB to exit the cell.
The two letters remain in lower case even though the mask used a capital A. That capital A does not tell the mask anything about the capitalization of what you type. It means that a letter or digit is required in this position. A lower case a would mean that a letter or digit is acceptable but is not required.
(Yes, I know that the address in this record is in Argentina. We are experimenting here!)
--------------------------------------------------------------------------------
Add Format
Switch back to Table Design view.
In the Format box for the field StateOrProvince, type > to force upper case for this field.
Save the table.
Switch back to Datasheet view.
The text is now in upper case, all capital letters, like the postal service wants.
Of course since there is no state TN in Argentina, we have a little problem to deal with! Unhappily, there is no neat way to use an input mask for this field if you will have addresses from different countries. The same issue keeps us from using an input mask for telephone numbers and postal codes also. The joys of international business!
Switch back to Design view and delete the Format and the Input Mask for the field StateOrProvince.
Database Fields
Fields can be one of a range of different data types, including: Text, Integer, Real number, Yes/No (Boolean), Date, Time, Sound, and Video.
Key fields allow computers to uniquely identify each record. They contain a unique value for each record. The records in a file are usually sorted by the value of the key field, for example, the ProductID in a stock file.
Each field can only hold one data type at any time. e.g. a field for a date will always, and only, hold a date. Fields can be either fixed or variable in length.
Fixed length fields will always use a certain number of characters for each record. Variable length fields only use the minimum number of characters necessary for each record.
Fixed length fields are faster to search through, but variable length fields use less storage space.
--------------------------------------------------------------------------------
Coding Databases
A well-designed database will code data that appears many times, rather than entering full terms.
e.g. in a database that records the gender of a person, it would be sensible to code as "M" or "F" rather than typing "Male" or "Female" each time.
This also allows you to enter data much faster, as well as allowing you to easily validate the input.
--------------------------------------------------------------------------------
Searching Databases
Databases can be searched by creating a query, of which there are two types: simple and complex.
Simple Query
A simple query uses one test on a single field to find records in a database. They are made up of three parts: a field name, an operator, and a value.
Examples of operators are equal to (=), more than (>), less than (<), and not equal to (<>).
An example of a simple query is:
Colour = Red
Complex Query
Complex queries use more than one test, or look at more than one field. They are made up of two or more simple queries joined together by logical operators, such as AND, OR and NOT.
An example of a complex query is:
Colour = Red OR Colour = Blue
--------------------------------------------------------------------------------
Database Summary
Databases consist of 1 or more files (or tables). Files are made up of records.
Records consist of 1 or more fields, basically information about something.
Key fields make a record unique, e.g. a customer number.
Fields contain different data types including Text, Integer, Real number, Yes/No (Boolean), Date, Time, Sound, and Video
Fields can be fixed or variable length. Fixed length fields search faster; variable fields take up less disk space.
Coding data speeds up data entry and helps avoid mistakes. e.g. instead of entering child or adult, code as c and a in the appropriate field.
Databases are searched with simple or complex queries. Simple queries search on a single field. Complex queries can search more than one field on a combination of terms.
--------------------------------------------------------------------------------
No comments:
Post a Comment