1

786

In the name of Allah

Ms Access notes

Date started: 22/07/2010

Ms Access 2006 notes

2 22/7/2010

786

Ms Access

Microsoft Access
Microsoft access is called Database program / DBMS as well. What is DBMS? (Data Base Management System) means to plan Note: before making database in msaccess planning is must. Date base: a collection of data a storing of data that is called database. 2nd definition: a collection of inter related data. What is table? A combination of rows and columns is called table. 1: columns: the vertical line of a table is called column. 2: rows: the horizontal lines of table are called rows. 3: Cell: the intersection between rows and columns that is called cell. 4: Record: the data of horizontal lines of a table that is called records. 5: Fields: the data of vertical lines that is called fields. First give the database name then a main window will be appeared. Table: create table in design view Field name Name F/name Math Class date type text text number text description it is used for name it is used for father name it is used for math number

Note; text can used for both text and number When: we click on data type a list appears this list is called combo list Description s information will appeared in status bar

786

23/7/2010 Data: unclear information that is called data. Information: the data which is clear is called information. Auto number: it is used for serial no or automatically no. Field name S/no Name Class Male Date/ time Phone no Picture Int = number Char = text String 0,1 0,2 Note: memo: it is used for long note Country: lookup wizard = 2nd option To close the design view table press Ctrl + W To tick mark the yes/no data type press space **********26************ OLE object: (Object link embedded) It is used to make link with file, folder, etc, links. Field name Name F/name Class Date Name= properties Field size = 10 By default =50 data type text text text date/time minimum= 1 maximum = 255 date type auto number text text yes/ no date time number ole object

Ms Access

description it is used for serial no it is used for true and false

786

26/7/2010 Format ! [Red] enter

Ms Access

Ole object: we can make link with the installed program inside our computer but Hyperlink: with all ex song etc or we can apply our email address as well in this cell. (3) Main things come in hyperlink 1; underline 2; color blue 3; hand *******27***** Field name Name F/name Country Date/time data type text text text date/time

Name properties Click on name cell then you will get the name properties below. Caption; student Caption: father name Default value: Pakistan/ Pakistan Input mask: 00/00/0000 instead of slash, comma cant be used. Caption: it is used to replace the name which has been already given in design view Click on the name cell then you will get the properties below. 3: default value; it is used to give default value ex. I know that the database which I am making depends to only Pakistani people therefore I write Pakistan in the default value cell so that it automatically comes.

******29******* Math = number Math properties Validation rule: <=50 Validation text: less then 50 number is allowed It is used to give a message to the validation rule

786

39/7/2010 Indexed: has 3 options. 1. No 2. yes (duplicate ok) 3. yes (no duplicate) No means neither I depend to duplicate nor no duplicate.

Properties

Yes (duplicate ok) it allows us to enter two or more then two same names in datasheet Yes (no duplicate) it doesnt accept two or more the same names. Field name Phone no data type number

We cant insert our number completely for the purpose of inserting number we must go to its properties then select the Double option field size. Relation ship: 1; s/no 2; s/no name name *********30********* make up 2 tables f/name math class bio address chem. date/time English total

Go to first table and give primary key then click on Create only without tick marking the 3 options below.

786

2/8/2010 Primary key It is a field or group of field which uniquely identify each record. Foreign key A key which refers to primary key if you put a primary key then
no blank data = in properties = required (null)

Primary key

_______3_______5 Keep sorting Ex; 11, 22, 33, 44, etc Usage of primary key. Select first id no + click give primary key Indexed: primary key = yes (no duplicate) = yes (duplicate ok) = No Required: must or should Name: yes / no Validation: we use for age Validation: <>17 Between 16 and 17 Validation text: ex; only 17 years is allowed

Primary key Or the last one is called foreign key

foreign key

786

3/8/2010 Relationship of 3 tables Name: properties Format: > < / &name

it is used for to make the text in capital letter. It is used for to make text small letter. It is used for to change alignment it us used to add any word or letter at the end of every name or father name ex;

Saleem name Nouroz name Yaseen name Default value: =now () then enter Time () Day () Validation rule: between 16 and 30 ******4******

Query
It is used for to ask question. 2nd one: to ask anything in a correct form Or: it is used for calculation + formula as well. Make a table Roll no Name f\name Math Physic Bio Chemistry History Total: [math] + [bio] + [chemistry] + [history] 2nd one: total: math+bio+chemistry+history then press enter button

786

4/8/2010 Formula FormulaRoll no Name F/name Fee Fee paid Balance: [fee]-[fee paid] then enter and colon is must after balance Note; these all formula should be written in query design view ********5********* Percentage Per: obtain/500*100

Formula

Here we have to type the total number of all subjects ex; we have 5 subjects then Their total number is 500.

Pass / fail: iif (obtain>300,pass,fail)

Enter one of them

786

12/8/2010 Field type Fee data type currency

formula

Fee properties Validation rule :>=50 <=50 <>50 50 or 100 or 150 <>100 <>100 and <>200 *******16****** Grade: iif (obtainmarks>350,A, iif (obtainmarks>300,B, iif (obtainmarks>250,C, iff (obtainmarks>200,D,))))

Table
Field type Name F/name Class Fee Phone data type text text text currency number

First fill the table And apply this table in query then open design view of query and apply these all functions below. Criteria :> 5 <4 <=4 >=4

10

786

17/8/2010 Criteria: between 2 and 4 Not between 2 and 4 [Enter any number] 2nd cell name Criteria: [Enter any name]

Criteria

Working together Criteria: [Enter any roll no] 2nd cell [Enter name] 3rd cell [Enter father name]
Between[enter any rollno from]and[to] then press enter Keyboard shortcuts Design view Name text

Ctrl+> shows datasheet Ctrl+< design view F6: shows ex name properties, first we should click on name or other cell. F4: shows all data types or opens the combo list Alt + D: shows / opens design view the selected table but first table should be selected. *******18*******

11

786

18/8/2010

Changing currency
Field date S/no Afghani Change/watta Pakistani data type auto number number currency number

Note: click on change then go to in its properties then select 2 Create query wizard Design view Pakistani: [change]*[afghani] then press enter once you enter the formula then dont change it whether rates become high or down ex: nowadays the rate is 1.8 so the next day It will be 1.258 so here we shouldnt change the formula just type the rate inside the datasheet.

Doing in excel
1800 Pakistani =1800/1000 =1.8 =1000*1.8 =1800 = 1000 Pakistani

Now changing Pakistani to afghani

12

786

24/8/2010
Form

Form

To give sequence To give a complete shape. To work with a different styles To give different kinds of button. It is used for to bring different kinds of buttons, images, and option button, check button, and a lot of other works, that is called form. Make up a table! Field type Name f/name fee fee paid balance data type text text currency currency number

in query: total:[fee]-[fee paid] then enter then save it inside query and open the form then from form open it (query table should be selected) then you can add students properties inside that form, once you fill all the blanks then for new page or for new students press tab button then you can sum all the balances of all students whose fees are registered in this list. By the below formula. Then select text (A) from toolbox and click on the end of database. Then this formula can be written inside this. =sum (balance) then enter will sum all students blance Note: for giving password to the database go to tools then set password. ********7******** Note: database cant be saved with the (/ and ?). With the above database work with button from form and properties( double click on any button then its property will be displayed )

13

786

Switch board

First make up two tables then fill in blanks then If you need to apply any formula then take them into query options Then Go to form after that by Create form by using wizard you can bring them here Then open the Create form in design view so here you can write the name of you form ex: Form then below you can create 2 buttons for created forms then select form operation Then open form from here you can select the 2 created tables. Then Go to report bring the 2 table here and go to back in form then create form in design view then give the title ex: Report so here below make up 2 reports buttons by command button = report operation = preview report then select the table one by one. Then select the create table in design view give the name Switch board then bring report and form here by command button = form operation = open form.

Note: Go to tools =database utilities = switch board manager = edit= New = open form in add mode then form the third option you can select the Switch board which has been just created by you now. Then ok = close= close

Report It is used as print preview or when we want to see our last result then we use this option.

14

786