CITY UNIVERSITY / Vysoká Škola Manažmentu

.:CS 416 - Database Management:.
 
Syllabus
 Assignments Schedule Theory Practice Grades
           
PRACTICE
   
MS Access 1  - 2007 version

Start Microsoft Access – New - Blank Database.

 

Change folder, go to student/bsba/cs416; name the file with your last name, click Create

 

A window with table data entry view appears /most fundamental part of DBMS; selected by default/

 

Go to design view and name the table Customers

 

1st field – name: customerID, data type: AutoNumber /selected by default/.

 

DBMS automatically assigns a unique identifier property to the customer ID (unique option is selected). The DBMS will automatically generate this number for each entered record.

In the 2nd field write LastName, data type -  text. In the Field properties part, change the Required option (3rd from the bottom) to Yes. Also, change the length – field size (1st option) to 30.

 

3rd field – FirstName, text, length 15 characters, required.

 

4th – City – text, 25, required.

 

Right click the “city” row, select insert row.  Field name - Address (will contain street and street # - combination of text and numbers), data type will be text, field size 50, required.

 

6th – YearOfBirth – number, field size = long integer, not required.

 

7th – photo – OLE object, not required.

In the Description cells describe each element e.g. (CustomerID – Unique number automatically assigned to a new record; YearOfBirth - customer’s year of birth).

 

Open the table in Datasheet View and insert 4 records.

 

Create a data entry form for this table

 

Click Create – Form. Access will automatically set up a default data-entry interface. DO NOT SAVE.

 

Create a form using Form Wizard – click Create – More Forms – Form Wizard

 

Move all the elements in the Selected fields window (click the double arrow), click next

Choose columnar look of the DB form, Next,  choose style.

 

Name the form customers.

 

Choose view – form view

 

You can browse individual entries with the arrows at the bottom of the form

 

Enter new data using the form interface (click New Record control)

Create other 2 tables – PRODUCTS AND ORDERS (click Create – Table, click Design View, name the table, …)
 

a.Products table will contain ProductID (AutoNumber, primary key), ProductName, category, description (memo as it might contain more than 255 characters), price (data type = currency, format - Euro)
 
b.Orders – OrderID (data type = automatic number), OrderDate, CustomerID, ProductID, quantity (number).

 

-In both tables set the primary key, define field size (number of characters), describe individual fields, choose if the entry is required or not. In the Products table insert  4 records so that we have 2 categories (food, clothes) and 2 products for each category (e.g. bread, rice; shirt, hat).
-

In the Navigation Pane, organize the order by Object Type
 

Use form wizard to create 2 forms – Products and Orders.

Format the forms - open each form in the Design View to edit it.

 

Change the actual size of the whole form, insert header and footer, then enlarge the header area.

 

Move all labels and text boxes to the middle of the detail section.

 

To relocate desired items, go to Arrange section, click remove icon and then move the fields to some other position.

 

In order to format the form and/or its individual elements, right-click, choose properties, eventually click Property Sheet icon in the Design section. A window with 5 tabs/options appear (all, format, data, event, other). To change the appearance of the form interface, click format.

Change the background color of the form detail, header and footer; change back and fore color as well as font type of text labels and text boxes.

 

Delete the default title from the header, replace it with a text label /click Aa button in the Design section, draw a rectangle, insert text: e.g. FlowerShop Customers./ and format its appearance.

 

In the footer, insert another text label with your name and current date and time.