More Projects

W.J.Pais Database Study - Lesson 1
Print

 

Create a New Project.

Start to think of an easy project, which you may likely use, according to your needs.   If you are a student, you may want to organize your studies, or if you are a teacher, you may want to organize your teaching plans, a housewife may want to keep a laundry table, or recipe table, or where she has kept her important stuff.   

Let us take a Student:

To keep his plans in front of him, he should think of what is upper most in his mind.  Let us say, that Mathematics is his problem and he wants to keep a tab on it.   He wants to monitor the work he has done on it.   What information would he need to have, of the work he has done, and after times goes by, wants to know all that he has done so far?

He would like to have his records have the following fields:

Autonumber.  Date,  Time,  Chapter,  Notes (Data Type Memo allows you to store unlimited  data, but other fields permit only up to 255 characters,.  You can limit this length in the Property Settings in the General Tab below.

Project: Design a table with the above fields and name it Mathematics Table.

s

Data Types,  Date, can have an input mask, a date format.  Note the character of the mask and format.  On the extreme right of the Property Window on the General Tab, if you place your mouse on Format line, three stars appear on the right and if you click on them, the wizard starts and you follow the instructions of the wizard.

f

                    Time too can have an input mask.  On the extreme right of the Property Window on the General Tab, if you place your mouse on Format line, three stars appear on the right and if you click on them, the wizard starts and you follow the instructions of the wizard.

f

Input Masks are a means by which, only the data that is expected will be input by the operator.,

You can  study from the samples in Microsoft Access.  

f

Choose the second option, Access database wizards, pages, and projects and select any and study how the tables have been organized.  

Home Accounts

Tables.

t

Family Members - linked table.

f

Note that the Beneficiary ID is unique as it is the Primary Key and is linked in the previous table  as Beneficiary ID, where duplicates are allowed, as the same person can hold more than one account.

Expense Types - Linked table.

e

Bank Transactions.

v

Note that I have made a spelling mistake in the Bank Name, and it is Namk instead of  Bank.  It does not matter what name you give, provided throughout the program you keep the same name.  Note that the data type is number (Autonumber of the Bank Table)   In the same way, Expense Type is a Number, (Autonumber pf Expense type table.)

Relationships

g

These are one to one. 

These tables will be used by using a technique called Form / Subform Input.  This will be explained in a later Lesson when dealing with Forms, which are a convenient and User friendly way to help operators to feed in data into the computer.

Daily Cash Purchases.

We will make a sub program module, to monitor our cash purchases from the market or retail shops and to tally the balance amount in hand, after deducting the cost of things purchased.    The program should keep a list of the things we purchased, should break it down into categories of expenses,  multiply the quantity with the cost of the item and extend the total and accumulate it, to show the total value of the bill, per day.    

We sill then transfer the day's expenses into the Cash and Bank register, under the cash account.

Tables.

g

Note in the Date field the properties of Format, Input Mask and Default Value.   Default value gives the current date of the day, which can be changed to another date.   The Input Mask:  00/ is numeric data representing Day, sometimes expressed as dd  the next two zeros are mm for month and the last are 4 zeros for year. followed by a semicolon and then the # or another symbol which appears in the mask,.   Format Short Date is  dd/mm/yyyy  or 01/012006

Cash in hand is the cash we hold at the beginning of each day.   The other information comes in the Form, which we shall  discuss later.

Now we have  to create two other trebles, one for the types of expenses we incur in cash purchases, and another to monitor daily transactions in cash.

Expense List.

Data View appearance of the List

d

Design View

d

 

Note the text name of the expense should not inadvertently be repeated, so we make it indexed with No duplicates so that we will be prevented from making the mistake.

Cash Transactions Table.

c

Measure Unit Table  Data View and Design View

c

c

Relationships

r

Combinations.

The Main table is Cash Purchases, and the subordinate tables are Daily Balance, Measure Units and Expense Types. They are all combined.   The totals are got by using the other Component of the Database called QUERIES.

Investments Table

e


Bookmark with:

Deli.cio.us Deli.cio.us    Digg Digg    reddit reddit    Facebook Facebook    StumbleUpon StumbleUpon    Newsvine Newsvine