Ms-Access
MS-ACCESS
Microsoft Access is a relational database management system (RDBMS) software developed by Microsoft Corporation of America. It is also a part of office packages, which helps to develop the database as your requirement.
Ms-Access has its own "Back_End" and "Front_End". Its file format is [*.mdb].
Object of Ms-Access:
1. Table
2. Query
3. Form
4. Report
5. Macro
6. Pages
7. Modules.
Table:- It is a combination of columns and rows. In Ms-Access you can create up to 255 tables in a single database where each and every table content 256 columns and unlimited rows. It's a man of database. It holds all are records as content.
Query:- It is a conditional selection of database without a table, Query can not be created. It always fetches a record from table as the specific criteria.
Form:- It is also know as a front end of database. It is used to Manipulate the table records by using this you can update ever more then one table at a time, which the help of macro.
Report:- It is used for distribution propose or printing propose.
Macro:- It automate a particular field. It is a set of action, which can be assigned at particular event to the different objects/control.
Pages:- It is used for internet propose.
Modules:- It is a core part Ms-Access programming.
Table: A table has two different view:
1. Datasheet View:
It's the default view of table. In this view you see the actual records stored in table.
2. Design View: In this view, we design the structure of table
Parts of design view
I. Field name: In this column you can give the name of field that you want on table.
II. Data Type: It defined the type of data what you would enter in particular field. It invalid data entered you will be stopped to go forward.
➢ Text : It allows any combination of a-z, 0-9 and some symbols. Its minimum length is 255 characters. Its default length is only 50 characters.
➢ Memo : It is similar to text but lengthy up to 65535 characters.
➢ Number: It accepts only combination of number (0-9) calculation can be done.
➢ Currency: It is similar to number but it also accepts prefix or suffix.
➢ Auto Number: It provides a unique number automatically to the particular field.
➢ Date/Time: It accepts any format of date or time.
➢ Yes/No: It accepts Boolean expressing like yes/no, True/false, male/female, married/unmarried etc.
➢ OLE Object: It stands for object linking embedded. It can store picture, movie, sound etc.
➢ Hyperlink: It is used for URL and E-mail address.
➢ Look up wizard: It is used for look up control in table.
III. Description: In this column you can give additional information regarding the particular column. This information is displayed in status bar in datasheet view.
IV. Field Properties: This part you can change the properties of field as well as bound them by defining different validation rules.
SN Auto Number
Name Text
Address Text
Phone Number
Dob Date/time
Item code Text
Net Amount Currency
Toward Memo
Validation Rajbiraj
Name Field Size
Caption SN , =
➢ Field Size: You can used the field size property to set the maximum size for data stored in a field set to text, number or auto number data type.
➢ Format: You can use these properties to format the text of field value.
Character Discretion
> It used is character to capital letter.
< It used is character to small latter.
➢ Long Format: Tuesday 2 october,2007
Medium date 2 Oct 2007
Short date 2/10/2007
Input mask:- It is use to control the field while entering a date
Character Discretion
0. any single digit (0-9)
9. any single digit (0-9) or space.
# any single digit (0-9) or space.
➢ Caption:- By using this properties you can change the field heading
➢ Default value: It daffiness the default value to the particular field. It automatically inserts the value when new record is generated.
➢ Validation Rule:- You can use this property to make a validation rule for the particular field.
For Example:- "Manager" or "Accountant",
10000 or < 15000
➢ Validation Text:- You can use this properties to display own text or massage when text violets the validation rule.
For Example :- Sorry valid only 10000 to 15000.
➢ Required:-You can set this property to YES if the particular field never contain null value.
➢ Indexed: -
❖ No - No indexing.
❖ Yes (Duplicate Ok) - Indexing but duplicate value also accept.
❖ Yes (No duplicate) – Indexing but duplicate value not allowed.
C/W. Item code
Item Name
Company
Stock BL
Rate
➢ Additional Info: -
✓ Item code: should be followed by 4 digits and character eg: I0001, I0002, I0010.
✓ Item name: must set be large than 10 character and must be in upper case.
✓ Company: name may be "Samsung", "LG", "Nokia" or "Sony".
✓ Stock BL: must not in negative and not larger than 100.
✓ Rate: The minimum rate of Item is 5000.
❖ Empcode: E followed by 3 digits. For Ex. E001, E002, ........etc.
❖ EMP Name: Should be in lower case having 15 characters in length.
❖ DOB: Must greater than 1965 – 1-1 .
❖ DOJ : Must greater than 2000-1-1.
❖ Post : May be manager, Accountant, Assistant, Helper, Labor, driver, Guard.
❖ Phone No: 031- Followed by 6 digits required for eg. 031-521200
❖ Salary: Minimum salary is 2000 and maximum salary is 20000.
Look Up Tab:
Control:
A graphical object that can be placed in table form or report and assigned a bundle (Set) of action such as text box, combo box, list box, command button etc..
Text Box:
A visual basic control that allow to type something or display a string.
Combo Box:
It combine the feature of both text box and list box use a combo box when you want the option of either type a value or selecting a value from a predefined list.
List Box:
A control (VB control) that provides a list of choices. A list box consists of a list and on optional table.
FIELD PROPERTIES:
Luck Up:
I) Display Control:
Select to type of control from given list.
✓ Text box.
✓ Combo box
✓ List box.
II) Row Source Type:
Select the type of source from the given list.
✓ Table/Query
✓ Field list
✓ Value list
III) Row Source:
Define the source address or value for the combo box or list box.
Example: "Manager", "Accountant".
IV) Column count:
It a certain the number of column to be display on combo box.
For Eg.
|Post |
| | | | |
V) Column Heads:
Yes :- To display the heading of column is combo box.
No :- To hide the heading of column is combo box.
For Eg.
|Post | | | |
VI) Column Width:
You can use this property to specify the width of each column in a multiple column combo box or list box. [value in inch]
VII) List Width:
You can use this property to set the maximum number of rows to display in combo box or list box.
VIII) List Rows Properties:
You can used this property to set maximum number of rows to display so that it ascertain the total width of the combo box or list box.
For Eg.
| | |
| | |
| | |
| | |
| | |
| | |
IX) Limit to List :
You can set it to YES so that its (combo) value become limit No other entry are accepted.
Sales Date Number
Customer Name Text
Item code Text Item Id, name, qty, rate
Quantity Number
Rate Number
Amount Currency
Primary Key :
The power of relational database system as Ms-Access comes from its ability to quickly find and bring together information stored is separate table using query, form and report. In order to do this each table should include a field or set of field that uniquely identified each record stored in the table. This information is called the primary key of the table.
Once you design a primary key for a table Access will prevent any duplicate or null value from being interred in the primary key fields. And it also helps to make relationship between tables.
To Set Primary Key:
i) Open the table in design view.
ii) Select the field or fields you want to define as the primary key.
iii) Click on primary key on the toolbar.
iv) Save the table.
Relationship:
An association between two or more than two table are called relationship. There are three kinds of relationship, they are:
i) One to one Relationship.
ii) One to many Relationships.
iii) Many to many Relationships.
Importance of Relationship:
✓ It establishes a relation between two or more table.
✓ It prevent from redundancy.
✓ It defines a field unique.
✓ Record can be fetch (bring) from other table.
I) One to one Relationship:
In this relationship each record in table A can have only one matching record in table B and each record in table B can have only one matching record in table A.
Example: Admission Table Result sheet
Stud Code Stud code
Name English
Address Nepali
Phone Math
II) One to Many Relationship:
In this relationship a record is table A can have many matching records in table B but a record in table B has only one matching record in table A.
Example: Admission Receipt Table
[PM] Student code ( Student code
Name Rec. Date
Address Rec. Amount
Phone
III) Many to many Relationship:
In this relationship a record in table A can have many matching record in table B and table B has many matching records in table C.
Example: Item Table Order Table Customer Table
[PM] Item Code Ord. No. Customer ID
Name Customer ID Name
Quantity Order date Address
Rate Quantity Phone
Item Code E-male
Rate
# To make a relationship between table:
1. First create the both table A and B.
2. Click on relationship tool available on toolbar.
Or, Go to Tools Menu and click on Relationships.
3. Add Both table [A and B]
4. Select the related field of table A and drag and drop to table B on related field.
5. Check the following options:
❖ Enforce Referential Integrity.
❖ Cascades update Related fields.
❖ Cascade Delete Related Records.
6. Click on Ok.
Query: It is condition of database when you make a question to database then the answer is query. Query needs at least a table without a table query is not possible.
# To make a Query:
1. Double click on Create query using wizard.
2. Select the Name of Table and its field which you want on query.
3. Click on Next ( Next ( Finish
# Create a query using Design View:
1. Double click on create query giving Design view.
2. Add a table by which you want to make a query.
3. Click the available field and drag and drop to down at grid.
4. Save the query and run it.
Eg.
Query:
Formula:
Aount = Amt: [Qty]*[Rate]
Dis = Dis: [Amt]*5/100
Net Amount = Net Amt: [amt]-[dis]
Formula for Result sheet
Total mark = Total mark: [English]+[Nepali]+[math]+[Science]…..
Percentage: = Per: [Total mark]/400*100
Result = Result: IIF([English]>=35 and [Nepali]>=35 and [math]>=35 and
[Science] >=35,"Pass","Fail")
Division: = Division: IIF([result]="pass" and [per]>=60, "1st", IIF([result]="pass" and
[per]>=45, "2nd", IIF([result]="pass" and [per]>=35, "3rd","Try again")))
# Find Duplicate query: It is used to find the duplicate records from a table or query.
1. Click on New an query object.
2. Select Find Duplicate Wizard and click on Ok.
3. Select the name of table/query from which you want to keep duplicate or repeated records.
4. Click on Next.
5. Select its field to find duplicate.
6. Click on Next.
7. Again click on Next and then Finish.
# Find unmatched Query: It is used to list those records of table A which is not matched with table B. This query needs two table to compare with.
Table A [ Admission] Table B [Receipt]
Stud Id Stud Id
Name Date
Address Amount
Phone
Steps:
1. Click on New button.
2. Select Find unmatched Query Wizard and click on Ok.
3. Select Table A that has no related records in Table B.
4. Click on Next.
5. Select another Table (Table) which contains the related records.
6. Click on Next.
7. Select the Field of both table which piece of information is in both table and then click on Match too. [< = > ]
8. Click on Next.
9. Give the Name of query and click on finish.
# Update Query: It is used to update or modify the related table.
Steps:
1. Double click on Create Simple Query.
2. Click on Query menu and then click on Update query.
3. Drag and drop those fields which you want to update.
For Ex. Amts
Discount
Net Amts
4. Pass the expression or formula in update to field.
Field:
5. Save the query and run as many times as the number of fields are to be update.
# Delete Query: It is used to delete the records from table based the specified criteria.
1. Design the Simple Query and open it in design view.
2. Pass the criteria or conditional in criteria field.
3. Click on query menu and then click on delete query.
4. Save the query and run it to delete the record which has quantity greater than 100.
# Append Query: It is used to append or insert the records of Table A to Table B.
1. Design a Query Just as select query.
2. Click on Query Menu. and then click on Append Query.
3. Select the name of query in which you want to append.
4. Click on Ok.
5. Save the query and run it.
# Make Table Query: It makes a new table of selected field from existing table. You can create it by Query in the same database or existing database.
1. Double click on Create Query in Design View.
2. Add a table from which you want to pick a field or fields.
3. Drag and drop the required field.
4. Click on Query Menu and then make table query.
5. Enter the name of Table, which will create, on the execution of this Query.
6. Click on Ok.
7. Save the Query and Run It.
FORM: It is a form of database by this you can manipulate records or table entry. You can use form to make an entry that is automatically update to A and form B.
Table
Form
Back
# Creating A Form:
1. Double click on create form by using wizard.
2. Select the name of Table or Query for which you want to make form.
3. Also select the required field of Table or Query which you want on Form.
4. Click on Next.
5. Select a layout of form column, tabulation and click on Next.
6. Select a style of form you like and click on Next.
7. Give the name of form and click on Finish.
Eg:- * Item code
*Result Sheet
Item Name
Quantity
Rate
Amount
Discount
Inserting command buttons in form
1. Turn on the control wizard available on tool box.
2. Click command button tool and insert into form.
As soon as you place a command button you get control wizard box.
3. Select appropriate categories and action you want to happen when the button is pressed.
Categories Actions
Record Navigation Add New Record
Record Operation Delete Record
Form Operation Save Record
Report Operation etc.
Application
Miscellaneous
4. Click on Next.
5. Select Text or picture, which you want on the button. * Add
6. Click on Next.
7. Enter Name of button and the click on Finish.
In the same way insert more buttons in form for eg.
First Previous Next Last Close Exit
# Macro: Macro is a set of action that can be executive at an event or assign it to a control.
Action of Macro [Assume , that Macro is for Amount]
1. Click on New on Macro object.
2. Select on action and enter its expression.
Action Comment
Set value
Item
Item [Forms]![Item]![Amount]
Expression [forms]![item]![rate]* [forms]![item]![qty]
3. Save the Macro.
Eg. McrAmt
4. Now, tag the macro with a control on particular even you want to happen.
5. On got focus
Conditional Macro
1. Click on view menu in Macro Design view.
2. Click on Condition.
|Condition |Action |Comment |
| | | |
Discount calculation:
If Amount > 10000 then 5% otherwise (10000
2. Write the given in Action Set Value
3. In Argument Item [Form ! [Item] ![amt]![dis]
Expression [forms ! [Item]![Amt]*5/100]
4. Save the Macro and set on Got Focus of discount field.
# For Result Calculation:
Condition : [Nepali]>=35 and [English]>=35 and [science]>=35
Action : Set Value
Item : Forms ! [Result sheet]![Result]
Expression : "Pass"
Condition : [Nepali]=60
Action : Set Value
Item: Forms ! [Result sheet]![Division]
Expression : “First”
Condition : [Result]= “pass” and [Per]>=45
Action : Set Value
Item: Forms ! [Result sheet]![Division]
Expression : “Second”
Condition : [Result]= “pass” and [Per]>=35
Action : Set Value
Item: Forms ! [Result sheet]![Division]
Expression : “Third”
Condition : [Result]= “pass” and [Per] ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- chemical compounds project don voorhees
- ms excel mcq quiz set 1 e library wcl
- perform microsoft access mail merge
- preliminary reports on database access capabilities of tools
- microsoft access semester project
- operations maintenance manual o m manual template
- microsoft access 2013 charles county public schools
- gis database integration access arcview
- microsoft access table design
Related searches
- ms access many to many relationship
- ms access relationships
- ms access vba listbox
- ms access string functions vba
- ms access string function
- ms access connection string 2016
- ms access vba functions
- ms access functions list
- ms access query functions
- ms access text function
- ms access formula list
- ms access sql isnull function