Microsoft Office/Erstellen einer grundlegenden Datenbank mit zwei Tabellen mit Hilfe von Access

Vokabular

• Datenbank - Eine organisierte Zusammenfassung von Daten, die erstellen, bearbeiten, löschen und nachschlagen erlaubt.
• Tabelle - Speichert Informationen zu einem gewissen Objekt.
• Datensatz - Die Zeilen in einer Tabelle, die Information zu einer gegebenen Person, einem Projekt oder Event beinhalten.
• Feld - Die Spalten in einer Tabelle, die eine gewisse Information beinhalten, z.B. die Stadt, das Bundesland oder die Telefonnumemr.
• Anfrage/Query - Die Möglichkeit die Daten abzufragen. Beispiel: Welche Firmen gibt es in einer Stadt, oder alle Kunden mit dem Nachnamen Schmidt.
• Formulare - Attractive layouts used for entering or looking up records in a table.
• Berichte - Attractive layouts used for printing out the data with sophisticated presentation.
• Web Page - A SharePoint for information that is stored in Access so it can be seen on the web.
• Primärschlüssel - a unique identifier for a record. There can be only one. Every record must have a different value in a field that is set up to be a primary key. If it is a table of products and the PRODUCT ID is the primary key, no two records can have the same ID.
• Datentyp - Specifying the type of data a certain field will hold. Access needs to know if you are going to work with a number, text, a time/date or a hyperlink.
• Feld Größe - Specifying how large the field will be. Access needs to know how large a text box needs to be created to hold text type data.
• Entwurfsansicht - A view that allows you to manipulate how data will be created, stored, and displayed on the screen and in reports.
• Datasheet view - A view of a table that is much like a spreadsheet. It allows you to enter in your information quickly.
• Report View - Once you create a report, you need to turn it to report view to preview it on the screen.
• Form View - Once you create a form, you need to turn it to form view to enter data, or use if for lookup.
• Form Layout - Once you create a form, you need to turn it to layout view if you wish to move items around on your form.
• Report Wizard - A tool that allows you to create a report by simply choosing what table and fields in the table you want to display. The report is built for you very quickly.

Unterrichtsinhalt

What is a database?

A database is a collection of organized tables. Each table is a collection of related records. Each record gives information about one item and is made up of many fields. Each field is one specific piece of information.

Datei:Https://en.wikibooks.org/wiki/File:AC1-Database layout.jpg

In this figure you can see the Clients Table is open in the Access work area. It contains 10 records, one for each client. There are nine fields: Client number, Client name, Street Address, City, State, Zip Code, Amount Paid, Type of Advertisement, and Employee.

Guidelines

Before you start to create a database, there needs to be a lot of planning. The first question should be: What is the purpose of the database? I usually want to know, what do I want to be able to pull back out of the database. So I guess I work backwards from the reports to figure out what will need to be put in.

1. What is the purpose of the database
2. Who all will use the database
3. What will each type of user need to be able to do
   • What reports does each user need
   • What forms does each user need
   • What transactions will each user need to document
4. Determine the tables that you will need
5. Determine the primary key fields
6. Determine all other fields that you will need in each table
7. Determine data types for each field
8. Determine field size for each field
9. Determine the relationship between the tables
10. Determine what fields will need to link the tables together
11. Remove any redundancy in the tables

Requirements

The yearbook staff need to keep track of their yearbook ad sales to the businesses in the community. In order to do this they will need to track the businesses of the community and what businesses have purchased ads, what types of ads they have purchased and if they have paid. They will also need to keep track of each student, in this case employee, to determine who has sold the ads. The staff will need to keep track of two basic groups of data, the businesses and the yearbook staff members. For this example we will call the businesses - Clients, and the staff members - Employees. For the clients we will need to know:

• Client Number
• Client Name
• Street Address
• City
• State
• Zip Code
• Amount Paid
• Type of Advertisement
• Employee Number

For the Employees we will need to know:

• Employee Number
• Last Name
• First Name
• Street Address
• City
• State
• Zip Code
• Number of Ads Sold
• Dollar Value Sold

Name the tables and fields

• They must not be longer then 64 characters
• You can use letters, integers, spaces, and some punctuation marks
• They cannot contain: periods (.), exclamation points(!), asterisks(*), question marks(?), accent marks(`) or square brackets([])
• The same name cannot be used twice
• Pick a logical naming convention so you can find your information quickly
  • Use upper cases to start words - Client Name
  • Use no space between words - ClientName
  • Use an underscore between words - Client_Name

We will call the two tables: Clients and Employees

Primary Keys

Each table needs to have a primary key. This is the one value in each table that can not be repeated, it is considered the look-up value. This field is used to easily access all the data for one record and usually has a short field length. For this example we will use the:

• Employee Number
• Client Number

Relationships between tables

When you design a database you need to be able to tie the data together. In most databases you have one table that is used once and tied to many records in another table. In this example the Client is used once, and each client is tied to an employee. So, each record in the employee table is used many times. This is called a One to Many relationship. Employee A works with

• Client 1
• Client 2
• Client 3
• Client 4

Data types

When you are creating the fields you also need to think about what type of data you are collecting in each field. Here is a run down of each data type:

Structure of the Client Table

Data Type	Description
Text	255 Characters of any type of character including numbers
Memo	Used when you want more than 255 characters of space and can contain any type of character
Number	Only numbers that are used for some type of arithmetic manipulation. If you do not plan on adding, subtracting, multiplying or dividing the number it is safer to make it type text.
Currency	Used for money and will auto-format to contain the dollar sign, commas and a two digit decimal.
Date/Time	Used when you are going to store a date or a time
AutoNumber	This field will start at 1 for the first record and each additional record will be one higher. This is great for use with invoice numbers.
Yes/No	This field is a check box that stores either yes/no, true/false, or on/off.
OLE Object	An object linked or embedded in the table
Hyperlink	Text that can be used as a hyperlink address
Attachment	This field will contain an attached file. This is handy if you are doing an on-line application and want have the user attach a resume.

Redundancy

The point to using a database instead of a spreadsheet is the ability to link tables together. This is useful for reducing redundancy. Redundancy is the repetition of the same data over and over again. For example if you are tying clients to employees, you would not want to have to tie the same employee name, address, city, state, and zip to every record. Instead you can link them both together with an employee number and store the data in separate files. By storing the data in separate file it reduces the risk that data will not be updated properly. Think about if an employee moves and the employee data is in the same table as the clients. You would have to go through all the records that had that employee and change the same data for each record that employee works with.

Client Table

Datei:AC1-Create Ribbon.jpg From the Access Window click on the:

• Create Tab
• Go to the TABLE Group on the far left of the ribbon
• Click on the TABLE button

Datei:AC1-Table Design View.jpg Click on View and Design View Datei:AC1-Save Table.jpg Click on OK Then fill in the fields on the design view of the form to follow the structure of the table below. You will need to enter the field name, data type, and field size as the arrows above show.

Structure of the Client Table

Field Name	Data Type	Field Size	Primary Key
Client Number	Text	4	Yes
Client Name	Text	25	No
Street Address	Text	30	no
City	Text	20	no
State	Text	2	no
Zip Code	Text	5	no
Amount Paid	Currency		no
Type of Advertisement	text	20	no
Employee Number	text	2	no

Once you are done putting in the field definitions then you will need to put in the data.

• Click on VIEW
• Click on Datasheet View

Then you can enter the data just like a spreadsheet. HINT: If you do not like entering the data this way you can click on FORM and and it will create a form for you to enter the data into a form.

Client Table Data

Client Number	Client Name	Street Address	City	State	Zip Code	Amount Paid	Type of Advertisement	Employee Number
ROLA	Rosken LLC Accountants	592 Main St Suite 1	Meeker	CO	81641	$40.00	Page Sponsor	KA
COAV	Coulter Aviation	921 Market Street	Meeker	CO	81641	$50.00	Business Card	DW
MEAI	Meeker Airport	921 Market Street	Meeker	CO	81641	$40.00	Page Sponsor	WS
BRLA	Brooks Laurie J Appraiser	889 Main Street	Meeker	CO	81641	$250.00	Full Page Ad	EJ
ROMO	Rocky Mountain Bowstrings	696 Main Street	Meeker	CO	81641	$50.00	Business Card	WS
ANTO	Ann Toney PC Attorney	PO Box 1022	Meeker	CO	81641	$40.00	Page Sponsor	EJ
ZABR	Zagar-Brown Trina K Attourney	685 Main Street Suite 5	Meeker	CO	81641	$150.00	Half Page Ad	KA
BOKE	Borchard Kent A Attourney	335 6th St #1	Meeker	CO	81641	$50.00	Business Card	KA
MECO	Meeker Collision Center	43904 Hwy 13	Meeker	CO	81641	$40.00	Page Sponsor	DW
NOAU	Northwest Auto	485 Market Street	Meeker	CO	81641	$50.00	Business Card	EJ

After you are done putting in all the data for the Clients you are ready to create the table for the employees and put in that data. You will follow the same steps as above.

1. Go to Create - Table
2. Go to View - Design View
3. Name the table Employees
4. Set up the fields as list in the table below

Employee Table

Structure of the Employee Table

Field Name	Data Type	Field Size	Primary Key
Employee Number	Text	2	Yes
Last Name	Text	20	No
First Name	Text	20	No
Street Address	Text	30	no
City	Text	20	no
State	Text	2	no
Zip Code	Text	5	no
Number of Ads Sold	number	2	no
Dollar value of Ads Sold	Currency		no

Once you have created the fields for the table then go to VIEW - Datasheet View and type in the data as listed below.

Employee Table Data

Employee #	Last Name	First Name	Street Address	City	State	Zip Code	# of Ads sold	$ value of Ads sold
KA	Kippers	Amanda	123 Malle Street	Meeker	CO	81641	3	$240.00
DW	Downing	Will	312 Howe Drive	Meeker	CO	81641	2	$90.00
EJ	Eston	Jim	213 Pawnee Way	Meeker	CO	81641	3	$340.00
WS	Wilson	Sophie	332 Wilbert Way	Meeker	CO	81641	2	$90.00

Create a form

The easiest way to create a form is to have the table open that you want the form to populate.

• Click on the Create Tab
• Go to the Forms Group
• Click on FORM

The from is created and you are in layout view. You can move items around from this view, but when you are ready to enter data you will need to change to Form View.

• Go to the HOME Tab
• Click View
• Click Form View

Start entering data. If you want to look through the records then use the buttons at the bottom of the screen they are just like those on all the electric devices you all use today. If you want to enter new data then click on the button that is the blue arrow pointing right with a star. Enter in the new data for the first field

• Press TAB to go to the next field
• Press SHIFT+TAB to go to the previous field
• Press TAB at the end of the fields on the screen will save that record and bring you to the next empty record.

Print a table

To print a table have the table open:

• Go to the Office Button
• Click Print arrow
• Click on Print Preview
• Change it to landscape if needed
• Adjust the margins if needed

NOTE: You do not have the printing control like you did with Excel, if you need a better format that is when we create a report.

Create a report

• Close all the tables and forms.
• Click on the CREATE tab
• Go to the REPORTS group
• Click on REPORT WIZARD

Datei:AC1-Report Wizard Table and Fields.jpg

• Set the table to Clients
• Click on the greater than sign to move the selected field to the right (fields on the right are put into the report)
• Move the following fields to the report:
  • Client Number
  • Client Name
  • Amount Paid
  • Type of Advertisement
  • Employee Number
• Click on NEXT

Datei:AC1-Report Wizard Grouping.jpg

• For this report we will not do any groupings and will leave this for a more advanced topic
• Click NEXT

Datei:AC1-Report Wizard Sorting.jpg

• Click the Drop down and choose the Employee Number (This will sort the report by employee number giving a list of clients for each employee.)
• Click Next

Datei:AC1-Report Wizard Layout.jpg

• Leave this set to tabular, you are welcome to play with this and see what each one will do.
• click Next

Datei:AC1-Report Wizard Style.jpg

• Look at the different styles and choose the type that you like the best
• Click on NEXT

Datei:AC1-Report Wizard Title.jpg

• Type in the name of the report
• This is the name that will print across the top of the report so watch your typos
• Click FINISH

Print a report

Go to the office button and click on PRINT

Project

Create a database to keep track of a business of your choosing. You must have:

• multiple tables - most of you will need four tables
  • Client table (with at least 10 records)
    • Client number
    • First name
    • Last name
    • Street address
    • PO Box
    • City
    • State
    • Zip Code
    • Phone number
    • Employee number - depending on your business
    • other fields that fit your business
  • Employee table
    • Employee number
    • Last name
    • First name
    • Street address
    • PO Box
    • City
    • State
    • Zip Code
    • Phone number
    • Pay Rate (if this fits your business)
    • Other fields that fit your business
  • Inventory or Services table (depending on your business)
    • Item Code
    • Item Description
    • Item Cost
  • Sales or Appointment table (depending on your business)
    • Invoice number - auto number
    • Client Code
    • Employee Code
    • Time
    • Date
    • Service or item
    • Quantity of item or length of time on service (if this fits business)
• You can have more tables to keep track of your business if you need
• Create a form for entering data and looking up data
• Create a report that matches your business such as:
  • Invoice
  • Billing Summary
  • Appointment Schedule
• Turn in a print out of:
  • each table
  • each report

Rubric

Create a Business Database

Standards	1 point	2 points	3 points	4 points	5 points
Choose a business that fits your interests	Could not identify a business, teacher assigned one.	Identified a business that could not be investigated or one that did not merit investigation.	Identified, with adult help, a business which could be investigated.	Identified, with adult help, a business which was interesting to the student and which could be investigated.	Independently identified a business which was interesting to the student and which could be investigated.
Creates Tables for Business	Some tables in the database had a function and clearly served to illustrate some aspect of the business.	Each table in the database had a function and clearly served to illustrate some aspect of the business. Some tables had most fields that were correctly labeled with name, data type and size.	Each table in the database had a function and clearly served to illustrate some aspect of the business. Most tables had most fields that were correctly labeled with name, data type and size.	Each table in the database had a function and clearly served to illustrate some aspect of the business. All tables had most fields that were correctly labeled with name, data type and size.	Each table in the database had a function and clearly served to illustrate some aspect of the business. All tables had fields that were correctly labeled with name, data type and size.
Creates Forms for Business	Creates a form for a table	Creates a form with adult assistance that allows for easy data entry and retrieval from a table.	Creates a form with no adult assistance that allows for easy data entry and retrieval from a table.	Accurately creates a form with some assistance that allows for easy data entry and retrieval from a table.	Accurately creates a form with no adult assistance that allows for easy data entry and retrieval from a table.
Creates a Report for the Business	Creates row for total	A report is created for the sales or appointments table.	A report is created for the sales or appointments table that shows understanding of why the report is needed. The title is descriptive.	A report is created for the sales or appointments table that clearly shows understanding of why the report is needed. The title is descriptive, and the data is sorted.	A report is created for the sales or appointments table that clearly shows understanding of why the report is needed. The title is descriptive, and the data is sorted in an appropriate manner.
Prints Tables and Reports for the Business	Missing 4 or more items	Missing 3 items	Missing 2 items	Missing 1 item or the Report prints on more than one page	All tables and forms are printed correctly, stapled with the report on top, and name on the back.