Lila Ram Paudel: Access notes

MS ACCESS

Microsoft Access is database application software that is used to manage a data property.

Data: Data may be defined as a row-fact that does not a complete sense. That may be in the from of text, sound, image and so on.

Example: (Ram- salary -------------10,000)

Information: Information may be defined as a systematic arrangement of a data that gives complete sense.

Example: (Ram has got salary of Rs. 10,000)

« All information is data but all data may not be Information.

Database: A database is a collection of information that's related to a particular subject or purpose, such as tracking customer orders or maintaining a music collection and so on.

Microsoft Access database: A collection of data and objects[1], such as tables, queries, or forms, related to a particular topic or purpose.

Type of Database

Flat file Database Management System e.g. MS Excel
DBMS (Database management System)
RDBMS (Relational DBMS[2]) e.g. Ms Access, Ms SQL Server, Oracle Server
ORDBMS (Optional RDBMS) e.g. Oracle

Features of RDBMS

Validation

Data Integrity (Data type)

II.

Data Consistency

III. Relationship[3]

*** Microsoft Access is RDBMS (Relational Database Management System). ***

To Run Microsoft Access:-

ª Click on Start

ª Choose Program

ª Click on Microsoft Access
OR,

ª Click on Start

ª Choose Run

ª Type MSACCESS

ª Click OK button

To create Blank database: it is used to create blank database.

? Open MS Access

? Click Blank database

? Specify the location where we want to create a database

? Give the name of database (like School Management and so on)

? Click on Create

Create Blank database

Object in Ms Access

a) Tables

b) Queries

Forms

d) Reports

e) Pages

f) Macros

g) Modules

Tables to store your data.
Queries to find and retrieve just the data you want.
Forms to view, add, and update data in tables.
Reports to analyze or print data in a specific layout.
Data access pages to view, update, or analyze the database's data from the Internet or an intranet[4].

Data type in Ms Access:-

Data type[5]: - It defines the type of data that can store on a table.

1. Text[6]: It is used to Store alpha-numeric values and also store the difference symbol; its storage capacity is 255 byte (character). In this data type, we use single line data to alpha-numeric format (It is default data type) to store Name, Address, Phone no., Post and so on. By default in this data type field size 50 character, we can set up to 1 to 255 character.

2. Memo: It is also text used to store alpha-numeric values and difference types of symbols. It can store up to 64 K.B. In this data type, we use multiple line data to alpha-numeric format to store Note, Comment, and so on.

3. Number : It is used to store numeric values .(0................9). In this data type, we can store different types of format numeric data what we need (by default field size is Long integer store 4 byte of number).

4. Date/Time: It is used to store date & time. It is also number but storage format is date and time (MM/DD/YY).

5. Currency[7]: It is used to store a currency. It is also number but storage format is currency ($0000.00). [8]

6. Auto Number: It is used to store a number automatically.

7. Yes/No: It is used to store yes/no or true/false or on/off values. It store only one bit of data.

8. OLE object: It is used to store a photo, Image, Sound, Text and so on. In this data type, we can store object to how much space in our memory.

9. Hyperlink: It is used to link other related file or object what we need. In this data type we input alpha- numeric format data to link related file. It is highly used to store Email address, URLs[9] and so on .

10. Lookup Wizard: It is used to pickup the data from given list. In this wizard, we can use any types of data what we need.

Setting	Type of data	Size
Text	(Default) Text or combinations of text and numbers, as well as numbers that don't require calculations, such as phone numbers.	Up to 255 characters or the length set by the Field Size property, whichever is less. Microsoft Access does not reserve space for unused portions of a text field.
Memo	Lengthy text or combinations of text & numbers.	Up to 65,535 characters (64 MB).
Number	Numeric data used in mathematical calculations. For more information on how to set the specific Number type, see the Field Size property topic.	1, 2, 4, or 8 bytes (16 bytes if the Field Size property is set to Replication ID).
Date/Time	Date and time values for the years 100 through 9999.	8 bytes.
Currency	Currency values and numeric data used in mathematical calculations involving data with one to four decimal places. Accurate to 15 digits on the left side of the decimal separator and to 4 digits on the right side.	8 bytes.
AutoNumber	A unique sequential (incremented by 1) number or random number assigned by Microsoft Access whenever a new record is added to a table. AutoNumber fields can't be updated..	4 bytes (16 bytes if the FieldSize property is set to Replication ID).
Yes/No	Yes and No values and fields that contain only one of two values (Yes/No, True/False, or On/Off).	1 bit.
OLE Object	An object (such as a Microsoft Excel spreadsheet, a Microsoft Word document, graphics, sounds, or other binary data) linked to or embedded in a Microsoft Access table.	Up to 1 gigabyte (limited by available disk space)
Hyperlink	Text or combinations of text and numbers stored as text and used as a hyperlink address. The easiest way to insert a hyperlink address in a field or control is to click Hyperlink on the Insert** menu.	Each part of the three parts of a Hyperlink data type can contain up to 2048 characters.
Lookup Wizard	Creates a field that allows you to choose a value from another table or from a list of values by using a list box or combo box. Clicking this option starts the Lookup Wizard, which creates a Lookup field. After you complete the wizard, Microsoft Access sets the data type based on the values selected in the wizard.	The same size as the primary key field used to perform the lookup, typically 4 bytes.

Table[10]: - It is a database object that is used to store data in the from of the Rows and columns. It is highly used to collection of data; therefore it is also called as a container. Table is main object of database.

* Table is Back-end part of a database*[11]

Two views of table:

1) Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.

2) Datasheet view: A window that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.

To used data type Lookup wizard

Be in design view of table
Choose data type Lookup Wizard
Choose (8 I will type the value that I want )
Click Next
Type the list
Click Next then Click Finish

To Store object into OLE Object [12]data type:

Go to data sheet view of table & go to OLE data type field.

Click on Insert Menu & Choose Object command.

Choose Create new or Create from file what we need (e.g. Create from file)

Define the file or, define the file location & file path.

If we want to link the file to check the link field.

Then click on ok.
Or

Go to Ole data type field then right click on the field.

Choose Insert Object option then define the object.

To Hyperlink the file into Hyperlink Data type :

Go to data sheet view of table & go to Hyperlink Data type field.

Define the hyperlink text (e.g. Click here to display map of Nepal)

Click on Insert Menu & Choose Hyperlink command.
or, Right click on the field & choose Hyperlink option
or, Press Ctrl + K short cut key from keyboard.

To link the particular object what we need, then click OK.

Field Properties in table

? Number

B Field Size:

Setting	Description	Storage size
Byte	Stores numbers from 0 to 255.	1 byte
Decimal[13]	Stores numbers decimals format.	12bytes
Integer	Stores numbers from –32,768 to 32,767.	2 bytes
Long Integer	(Default) Stores numbers from –2,147,483,648 to 2,147,483,647 .	4 bytes
Single	Stores numbers from –3.402823E38 to –1.401298E–45 for negative values and from 1.401298E–45 to 3.402823E38 for positive values.	4 bytes
Double	Stores numbers from –1.79769313486231E308 to –4.94065645841247E–324 for negative values and from 4.94065645841247E–324 to 1.79769313486231E308 for positive values.	8 bytes
Replication ID		16 bytes

B Input musk: 0:- Required 9:- Option

**If we need to set the different symbol (/, - ) in the field, then we define the symbol in input musk .(E.g. 12-02-2005:- 00-00-0099) ***

♣ '#': Digit or space (entry not required; spaces are displayed as blanks while in Edit mode, but blanks are removed when data is saved; plus and minus signs allowed). For ex.: In telephone no field: Input musk:- (##)-#######.

(It is highly used in phone no. field or Date time field.)

B Caption : It defines the caption text (ex: Caption: Name of Student).

B Default Value: It defines the value to stores by default in specify field. (Ex: in phone no. field : Default value: 01-4690098)

B Validation Rule[14]: Rules of data entry or, it defines the limited of record

(ex. >[15]0 and <100)

B Validation Text: To give Message of user when data is not valid

(ex. You can store greater than 0 and less than 100)

B Required (Yes/No)

No: If we choose No then we can leave a blank this column.

Yes: If we choose yes then we cannot leave a blank this column.

B Indexed: ► No: (Default) No index.

► Yes (Duplicates OK): The index allows duplicates.

► Yes (No Duplicates): The index allows duplicates.

? Text

B Field Size: It defines the size of the field. (By default size is 50 character) we can define the size of field is 1 to 255.

B Format: - '>':- All text change into capital letter

'<':- All text change in to small letter

* If we want to define the space into top of the text then define space into format. **

B Input Musk: - L: - Required.

? : - Option.

B Required (Yes/No)

No: If we choose No then we can leave a blank this column.

Yes: If we choose Yes then we cannot leave a blank this column.

(Note: If we want to give default values then we choose Default Value then give default value.)

** If we want to insert current date/time then we go to field properties then choose Default Value then give

Now () keyword. **

** "=date()" : This keyword is used to insert default value of date time field to insert current date of the particular field.**

++ If we want to insert only current time into the particular field, then define default value is "=time ()" keyword.++

- All data types' field properties set the particular option what we need. .

Query: It is a database object that is used to retrieve records from one or more tables. Besides it is used for sorting, filtering, manipulating, modifying date and so on.

Views of Query: - 3 kinds of views of Query.

Design view.
Datasheet view.
SQL[16] (Structure Query Language) view.

Two types of Query

L Select Query: - It is used to retrieve record from one or, more tables based upon a condition. Also it is used to manipulating data by given condition & it is used to sort data into particular order (Ascending & Descending order).

There are two types of Select Query:

1- Select Query

2- Cross tab Query[17]

L Action Query: - It is used for modifying data on a table. To show the effect of Action Query; we have to run it manually.

There are four types of Action Query:

Update Query.
Delete Query.
Make table Query.
Append Query.

@ Delete Queries A delete query deletes a group of records from one or more tables

@ Update Queries An update query makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

@ Append Queries An append query adds a group of records from one or more tables to the end of one or more tables.

@ Make-Table Queries A make-table query creates a new table from all or part of the data in one or more tables.

To Create Query:

§ Choose Queries object

§ Choose option ( Example: Create Query in design view)

§ Click on Design or, double click it

§ Choose table when we want to create Query

§ Click on Add button then click Close button

§ Choose Query type (by default Select Query) in Query Menu or

from Query design toolbar.

§ Insert the field where we want to manipulating, modifying, retrieving

Give the condition into criteria.

§ Save the Query

# Criteria: Conditions you specify to limit which records are included in the result set of a query or filter. ex. Select all the item detail whose QTY. is grater then 50, Define the criteria QTY>50.

Select Query:

· Retrieve data from one or more tables by using criteria you specify and then display the data in the order you want.

· Manipulating data: Select query is also used to calculation the record following different condition.

Example: We insert the sales table

SalesID	ItemID	SalesRate	SalesQty	Amount: [Salesrate*salesqty]	Discount: [10*Amount/100]	VAT: [8*Amount/100]	Net Amount: [Amount+VAT-Discount]

2 kinds of Query

1. Parameter less Query: These types of query the condition define the design time. The parameter value defines the design time; therefore this types of query use only one time.

2. Parameterize Query: It is Query that is used to get value from the user. It is used to multiple conditions & used multiple times. The values of parameter define by user in datasheet period.

SalesID

ItemID

SalesRate

SalesQty

Amount:

[Salesrate*salesqty]

Discount:

([Enter Dis%] *[Amount]/100)

VAT:

([Enter VAT %]**Amount/100)

Net Amount:

[Amount+VAT-Discount]

Parameter

Discount :( [Enter discount %]*[Amount]/100) VAT :( [Enter VAT %]*[Amount]/100)

@ If we want to give condition to search record …

We give condition in Criteria to related field

For e.g. we want display all the record from Item table where Item name is Monitor

? Make Query using by Item table then insert the entire field.

?Go to Item Name field then put name (Monitor) in Criteria

If we want to make this Query to parameterized then we put in Criteria [Enter Item Name to Search]

Note: If we need to insert the other table or query in design period then: ♣ go to Query Menu & choose Show table command. ♣ Or,

From Query design toolbar.

To Create Crosstab Query:

Choose Queries object

Choose option ( Example: Create Query in design view)

Click on Design or, double click it

Choose table when we want to create Cross tab Query (e.g. Item table)

Click on Add button then click Close button

Choose Query type (Crosstab Query) in Query Menu or

from Query design toolbar.

Insert the field where we want to define the row & column, calculating data. (E.x. Item name Row heading, Item name column heading, Item QTY: calculting value (min, Max, Avg, count etc). )

Give the condition into criteria.

Save the Query

? Ex. To define the Item detail query using Crosstab Query:

ü Define Query using Item table

ü Choose Query Crosstab Query

ü Insert field what we need (Itemid, Itemname, ItemQty, Rate)

ü Define the Crosstab ((row heading, column heading & value) Row Heading is Item id, Store date, Item Rate\ Column Heading is Item Name\ Value is Qty)

ü Define SUM into Qty field & all are Group By in total field.

ü Define one column Total of Qty: QTY & define Total field is SUM Crosstab is Row Heading.

ü Save the Query.

Action Query:

§ Update Query: It's an action Query that is used to update the records of a table since it's an action Query. We have to run it manually to reflect the data on a table. Update Query is very useful query to update the record.

To create Update Query:

Make Query using by table (Example: Item)
Choose Update Query from Query Menu.
Insert field name in column when we update the record (Example: Itemid)
Choose Update to field
Put the new value or, formula when we want to update (Example: [Itemid] +100)
Give the Condition on Criteria (Example: 102 or 103)
Run the Query

*** ….(If we make parameterized Update Query then prompt from user new record & where we want to update record then prompt Old record (Example: Change Name of Student when we prompt new name & old name then make update Query using by student table then insert StudentName on column then put Update to [Enter New Name] then put Criteria [Enter Old Name].) ***

1. To update rate 15 increase: Update to : [rate]+([rate]*15/100)

2. To update Qty of Monitor by 30 decrease: Update to: [qty]-30; then define the criteria of itemname field Monitor.

§ Delete Query: - It is an action Query that is used to delete the record of the table.

To create Delete Query

± Make Query using by table (Example: Item)

± Choose Delete Query from Query Menu

± Insert field name in column when we delete the record given by condition (Example: Itemid)

± Give the Condition on Criteria (Example: 101)

± Run the Query

“Note: - If we don't to define any criteria then all our record has been deleted.”

If we want to have value of Criteria from user we make parameterized Query ([Enter Id to Delete the Record])

§ Make table Query: - It is used to make a table either on a same database or, a different database based on a given table using same column, datatype & record.

To create Make table Query:

§ Make Query using by table (Example: sales)

§ Choose Make table Query

§ Give the Name of New table (Example: Sales Detail)

§ (If we need to make table on different database then Choose Another Database then click on Browse select the database then click ok button.)

§ Insert field name in column when we make the table (Example: Salesid, SalesQty, SalesRate, Salesdate)

§ If we need to define the condition of create table then define the condition on the criteria of specify field.

§ Run the Query

§ Append Query: - It is used to add records from one table to next either on a same database or different database table.

How to create Append Query:

Make Query using by table where we append the record into another table (Example: Item)

² Choose Append Query from Query Menu

² Choose the Name of table where we want to Append (Example: Item detail)

² (If we need to Append on different database then Choose Another Database then click on Browse select the database then click ok button.)

² Insert field name in column when we want to Append the record

² Choose Field name from Append to where we want to append the record

² If we need to define the condition then define the condition on particular field.

² Run the Query

? Ex. To define the item detail table where we store item information & Amount of total QTY. using item table.

? To create Make table Query using Item table,( Define table name item detail) .

? Define the Amount field using item Qty & Item rate (Amount:Itemqty*Itemrate).

? Run the Query.

? Ex. To store the record of item into item detail table where store date is today.

? To create Append Query using Item table,( Define Append table name item detail) .

? Define all of the field & define Amount field using item Qty & Item rate (Amount:Itemqty*Itemrate).

? In Store date criteria define the today's date.

? Run the Query.

Some SQL Structure:

R To define the select query:

►select table.fieldname1, table.fieldname2, table.fieldname3, from tablename;

E.g.: SELECT item.Itemid, item.name, item.qty, item.rate, item.stockdate FROM item;

B If we want to display all record of the table then :

Select* from tablename;

ex. Select* from item;

To define the condition on the select query:

SELECT item.Itemid, item.name, item.qty, item.rate, item.stockdate FROM item

WHERE (((item.name)="keyboard"));

R To define the Update query:

►update tablename SET tablename.field name='update formula';

UPDATE item SET item.qty = [qty]+([qty]*15/100);

To define the criteria of the updated item:

UPDATE item SET item.rate = [rate]+([rate]*25/100)

WHERE (((item.qty)<15));

Data Definition Query: This query language is used to create the table using particular data type.

E.g. To create Friends table

CREATE TABLE Friends

([FriendID] integer,

[LastName] text,

[FirstName] text,

[Birthdate] date,

[Phone] text,

[Notes] memo,

CONSTRAINT [Index1] PRIMARY KEY ([FriendID]))[18];

Note : When we run this Query then the friends table created.

To append the data using SQL language: It is used to store data into table.

INSERT INTO friends

VALUES ('101', 'Pandey', 'Narendra', '12-05-1986', 4566005, ' ');

D Join[19]: It is a tool that is used to retrieve data from one or more than one table upon a given column.

? INNER Join

? Outer Join

INNER Join:

It retrieves only these data which are common on both the table.

SQL SYNTAX: select <column list> from <Table-1>

inner join <table-2> ON condition

e.g.: SELECT item.Itemid, item.name, item.rate, item.qty, item.stockdate

FROM item INNER JOIN sales ON item.Itemid = sales.itemid;

Outer Join: It retrieves both match and unmatched from the table.

Types of outer join:

w Left Outer join: It retrieves all the records from left table and only match records from the right table.

SQL SYNTAX: select <column list> from <Table-1>

left outer join <table-2> ON <condition>

e.g.: SELECT item.Itemid, item.name, sales.rate, sales.qty, sales.date

FROM item LEFT outer JOIN sales ON item.Itemid = sales.itemid;

w Right Outer join: It retrieves all the records from right table and only match records from the left table.

SQL SYNTAX: select <column list> from <Table-1>

right outer join <table-2> ON <condition>

e.g.: SELECT item.Itemid, item.name, sales.rate, sales.qty, sales.date

FROM item Right outer JOIN sales ON item.Itemid = sales.itemid;

PRIMARY KEY

@ It is used to make record unique on a table (It doesn’t accept duplicate value).

@ Whenever a primary key [20]is defined on a column then that column can not be left blank (It doesn’t accept null value).

@ Only one primary key can be defined on a table.

@ It is used to stables relationship between tables.

Note: It is a key to establish the relationship into object. Therefore it is must useful key into RDBMS.

Relationship: -

It is used to define the relationship between tables. The Relationship will be defined where the related field is same column & datatype. Relationship will be define parents table to child table (primary table to related sub table).

***Column name & data type is exactly same. ***

Types of Relationship

There are three types of Relationship:

@ One to Many [21] 1------------¥

@ One to One [22] 1-------------1

@ Many to Many[23] ¥-----------¥

How to create the Relationship:

² Go to Tools Menu

Choose Relationship or, click

from standard toolbar

² Choose table or, Query when we need to create relationship

² Insert the object (table or, query) where we want to define the relationship.

² Choose the field name when we want to create relationship (Common Field name & data type.)

² Drag it on the another table (Parents table to child table Example: Item table = Itemid and Sales table = Itemid)

R Enforce Referential Integrity [24]

R Cascade Update Related Fields[25]

R Cascade Delete Related Records[26]

Enforce Referential Integrity: - We checked on this option then two

options are actives:-

R Cascade Update Related Fields: - The data of parents is modified/ updated then the related data of child table will be automatically updated.

R Cascade Delete Related Records: - The data of parents is deleted then the related data of child table will be automatically deleted.

Table Lookup: - It is used to put value from parents table to child table using to combo box.

v Go to design view of a table where we need to used table lookup (ex. Sales table)

v Choose the column for what we want to lookup (Example: Itemid or, customerid)

v Choose Lookup tab

v From Choose Display Control : Combo Box

v Choose Row Source Type : Table/Query

v Choose Row Source : table name where we want to look record (ex. Item)

v Give Bound column : (1)

v Give Column Count :( 2)

v If necessary defined column need (Yes/ No)

Form:-

It is front-end part of a database that is used to insert data into a table or to display data from table.

F Form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.

To use Form:

A form is a type of a database object that is primarily used to enter or display data in a database. we can also use a form as a switchboard that opens other forms and reports in the database, or as a custom dialog box that accepts user input and carries out an action based on the input.

Create Form in Design View:-

# Go to Form object

# Choose Create Form in Design view

# Click on Design or, double click it

Click on View Menu

# Choose Properties or, click here from standard toolbar

# Choose Record Source (ex. Sales)

# Put the Field name on form when we need

# If we want to change Background color then Choose Back color from Properties

If we need to insert button, label, textbox…etc then go to View menu & choose toolbox or, click here from standard toolbar then insert the tools when we need

Save the form

Create Form by using wizard:-

# Be in the Form object

# Select the Create Form By Using Wizard

# Double click or, Click on design

# Choose table or, Query for what we want to design a form (ex. Sales)

# Select the fields that are necessary

# Click Next

# Choose layout of the form (ex. Columnar)

# Click Next

# Choose the style (ex. International)

# Click Next

# Click Finish

Removing the Navigation Button of a Form:-

Ø Be in the Design Form of at Form

Ø Choose Navigation Buttons of a Form properties

Ø Make it No

Creating a Navigation Button

Ø Be in the Design Form of at Form

Ø Be sure that Wizard control is ON

Ø Choose Command button control from the toolbox

Ø Drop it on a form

Ø Choose Record Navigation from categories

Ø Choose any action you want (ex. Go to Previous Record, Go to next record...)

Ø Choose the Symbol of Button or, we want to give text then choose Text

Ø Type the Button name or, choose the picture

Ø Click Next button then Click Finish

Wizard Control

Text box

Select object

Label

Opening Form using Command Button:-

õ Be in the design view of a Form

õ Be sure that Wizard control is ON

õ Choose command button from toolbox

õ Drop it on a form

õ Choose Form Operation from categories

õ Choose Open Form from action

õ Click Next

õ Choose Form that you want to open (ex. Customer)

õ Click Next; then click Next

õ Choose Sample of button ( text or, picture)

õ Click Next; again click Finish

To insert other useful button into form:

Quit Application Button: This button is used to close the application.

q Be in the design view of a Form

q Be sure that Wizard control is ON

q Choose command button from toolbox

q Drop it on a form where we need to insert quit button

q Choose Application from categories

q Choose Quit Application from action

q Click Next

q Specify the Button picture or text

q Give the button name & Click finish.

Run Application Button: This button is used to open the particular application.

q Be in the design view of a Form

q Be sure that Wizard control is ON

q Choose command button from toolbox

q Drop it on a form where we need to insert quit button

q Choose Application from categories

q Choose Run Application from action

q Click Next

q Define the command line to application path (ex. to open Adobe Photoshop: C:\Program Files\Adobe\Photoshop 7.0\Photoshop.exe).

q Specify the Button picture or text

q Give the button name & Click finish.

Run MS Word/ Excel & Notepad from command button : It is used to run useful application from form using command button.

q Be in the design view of a Form

q Be sure that Wizard control is ON

q Choose command button from toolbox

q Drop it on a form where we need to insert quit button

q Choose Application from categories

q Choose Run MS Word/MS Excel/Notepad from action

q Click Next

q Specify the Button picture or text

q Give the button name & Click finish.

Note: If we want to print form, Record then we print using command button. Also delete record, Save Record, Duplicate record etc all process are commanded by command button.

Working with Radio Button or, Option Button (for sex)

Y Be in the design from of Form

Y Select the field name (custsex) then Delete the (Custsex)

Y Choose Option Group from toolbox

Y Drop it on the Form where we need to insert the option button

Choose the Option Button from toolbox

Drop it on a Option Group of form

Y Give the name (Level ) of control (ex. Option group = ‘Sex’, Option button= ‘Male’ & ‘Female’)

Y Select the Option Group

Y Choose Properties

Y Choose Data then choose Control Source = Source name (ex. Custsex)

Y Select the Option (ex. Male/ Female)

Y Define the Option value from property ( if Checked = true then -1 & checked = False then 0 therefore Male = -1 [27]/ Female = 0)

Y Save the Form

Customizing the Picture size

Be in the design from of Form

Select the picture control

Choose Properties

Choose Size Mode then Make it Stretch (Stretch/Clip/Zoom)

Deducting the ItemQty from item table (using update Query)

Create Update Query using table( Item)

Choose (Itemid) id & Updated Qty (Itemqty) an update Query

In Criteria of a Field (Itemid column)

I Right mouse click on Criteria

I Choose Build or,

click here from Standard toolbar

I Double click on Forms

I Double click All Forms

I Select Sales Forms

I Double click on Itemid

I Then click OK button

In Update To of a Field (ItemQty column)

I Give [ItemQty][28]- on Update To

I Put the Cursor on here then Right mouse click

I Choose Build ; or,

click here from Standard toolbar

I Double click on Forms

I Double click All Forms

I Select Sales Forms

I Double click on SalesQty

I Then click OK button

I Save the Query (ex. Update stock)

To insert the Query Run Button on a Form

O Be in the design view of a Form (Sales)

O Be sure that Wizard control is ON

O Choose command button from toolbox

O Drop it on a form

O Choose Miscellaneous from categories

O Choose Run Query from action

O Click Next

O Choose Query that you want to open (ex. Update stock)

O Click Next; then click Next

O Choose Sample of button ( text or, picture)

Click Next; again click Finish

(Note: when Sales record inserted on sales form or, we sale the item then we run this Query)

E All setting & formatting is formatted by Properties window

Report:-

It is a final part of a database that is used to give a final result of output of a database. It is also use for generating a Bill report.

3 parts of Report

Create Report in Design view:

A Go to Report object

A Choose Create Report in Design view

A Click on Design or, double click it

Click on View Menu

A Choose Properties or, click here from standard toolbar

A Choose Record Source (ex. Sales)

A Put the Field name on form when we need

A If we want to change Background color then Choose Back color from Properties

If we need to insert button, label, textbox…etc then go to View menu & choose toolbox or, click here from standard toolbar then insert the tools when we need

A Save the Report

Create Report in Design view

Create Report by using wizard:-

# Be in the Report object

# Select the Create Report By Using Wizard

# Double click or, Click on design

# Choose table or, Query for what we want to design a form (ex. Bill)

# Select the fields that are necessary

# Click Next again click Next

# Choose layout and orientation of the Report (ex Tabular/ Landscape)

# Click Next

# Choose the style of Report (ex. Corporate)

# Click Next

# Click Finish

Opening Report using Command Button:-

ï Be in the design view of a Form

ï Be sure that Wizard control is ON

ï Choose command button from toolbox

ï Drop it on a form

ï Choose Report Operations from categories

ï Choose Preview Report from action
( If we need to print the report then choose Print Report from action)

ï Click Next

ï Choose Report that you want to open (ex. Bill)

ï Click Next

ï Choose Sample of button ( text or, picture)

ï Click Next; again click Finish

To Generating a Bill:-

Ä Make a Select Query having following fields from table (Item, Customer, Sales)

(Field =Salesid, ItemName, SalesRate, SalesQty, Custmoreid, CustomerName, CustomerAddress, SalesDate etc...)

Ä Add one more column Amount Such that Amount:[salesrate]*[SalesQty]

Ä In Criteria of CustomerId column

" Right mouse click on Criteria

" Choose Build or,

click here from Standard toolbar

" Double click on Forms

" Double click All Forms

" Select Sales Forms

" Double click on Customerid

" Then click OK button

Ä In Criteria of SalesRate column

" Right mouse click on Criteria

" Choose Build or,

click here from Standard toolbar

" Double click on Forms

" Double click All Forms

" Select Sales Forms

" Double click on Salesrate

" Then click OK button

Ä Save the Query (ex. Bill)

Ä Go to Report Object

Ä Choose Create Report in Design view

Ä Click on Design or, double click it

Click on View Menu

Ä Choose Properties or, click here from standard toolbar

Ä Choose Record Source = Bill

Ä Drag the Fields and Drop it on a Report Details

Ä If we want to change Background color then Choose Back color from Properties

If we need to insert label, textbox…etc then go to View menu & choose toolbox or, click here from standard toolbar then insert the tools when we need

Ä Save the Report (ex. Bill Report)

Ä Go to Design View of a Sales Form

Ä Be sure that Wizard control is ON

Ä Choose command button from toolbox

Ä Drop it on a form

Ä Choose Report Operations from categories

Ä Choose Preview Report from action
( If we need to print the report then choose Print Report from action)

Ä Click Next

Ä Choose Report that you want to open (ex. Bill Report)

Ä Click Next

Ä Choose Sample of button ( text or, picture)

Ä Click Next; again click Finish

To Search the Record using Button of a Form:-

ï Go to Design View of a Form

ï Be sure that Wizard control is ON

ï Choose command button from toolbox

ï Drop it on a form

ï Choose Record Navigation from categories

ï Choose Fine Record from action

ï Click Next

ï Choose Sample of button ( text or, picture ex. Text = Search )

ï Click Next; again click Finish

ù If we want to give different information into our database front end part to define VB Syntax into form Code view. We can define Access project using SQL & VB language.

E If we need to define the Front end part into HTML (Web) page then we use Page object.

Sating the Start of Form:-

? Create a new Form with command button that open the corresponding Forms and Report (It is Final page or our Home page of Database. All related object are defined here to using command button.)

? Save the Form (ex. Main Form)

? Click on Tools Menu

? Choose Startup…

? Choose the Form that you want to open why opening a database application (ex. Main Form) from Display from/ Page:

? Disable all the properties of Startup…

? Click OK Button

rdd ::::::::::::::: End ::::::::::::::: ggr

[1] Database objects: An Access database contains objects such as tables, queries, forms, reports, pages, macros, and modules. An Access project contains objects such as forms, reports, pages, macros, and modules.

[2] Relational database: A type of database that stores information in tables. Uses matching values from two tables to relate data in one table to data in the other table. In a relational database, you typically store a specific type of data just once.

[3] Relationship: An association established between common fields (columns) in two tables. A relationship can be one-to-one, one-to-many, or many-to-many.

[4] Intranet: A computer network that is private to a company, University, etc, but is connected to and uses the same software as the internet.

[5] Data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).

[6] Text data type: In a Microsoft Access database, this is a field data type. Text fields can contain up to 255 characters or the number of characters specified by the Field Size property, whichever is less.

[7] Currency data type: In a Microsoft Access database, a data type that is useful for calculations involving money or for fixed-point calculations in which accuracy is extremely important.

[8] If we want to change the currency symbol into Rs.: Open control panel, then choose Regional Setting , Choose Currency field & change $ into Rs. , Click Ok.

[9] URL: Uniform Resource Locator

[10] Table: A database object that stores data in records (rows) and fields (columns). The data is usually about a particular category of things, such as employees or orders.

[11] Front-end/back-end application: An application consisting of a "back-end" database file that contains tables, and copies of a "front-end" database file that contain all other database objects with links to the “back-end” tables.

[12] OLE Object data type: A field data type you use for objects created in other applications that can be linked or embedded (inserted) in an Access database

[13] Decimal: A number that is shown as a dot or point followed by the number of tenths, hundredths etc.

[14] Validation rule: A property that defines valid input values for a field or record in a table, or a control on a form. Access displays the message specified in the Validation Text property when the rule is violated.

[15] >: this symbol is used to define the grater condition and , < : this symbol is define the less condition to given value.

[16] SQL view: A window that displays the SQL statement for the current query or is used to create an SQL-specific query. When you create a query in Design view, Access constructs the SQL equivalent in SQL view.

[17] crosstab query: A query that calculates a sum, average, count, or other type of total on records, and then groups the result by two types of information — one down the left side of the datasheet and the other across the top.

[18] It defines the primary key constant into friendid field.

[19]Join : An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.

[20] Primary key: One or more fields (columns) whose value or values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.

[21] One-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.

[22] One-to-one relationship: This types of relationship the primary table have only one record matching in related table.

[23] Many-to-many relationship: An association between two tables in which one record in either table can relate to many records in the other table. To establish one, create a third table and add the primary key fields from the other two tables to this table.

[24] Referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.

[25] Cascading update: For relationships that enforce referential integrity between tables, the updating of all related records in the related table or tables when a record in the primary table is changed.

[26] Cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.

[27] In Yes/ No datatype of field when the value is checked then database have -1 value & checked is false then the value is 0.

[28] Field name where we want to update

Lila Ram Paudel

Pages

Access notes

No comments :

Post a Comment

quote

my marquee

about blog

Translate

Kathmandu