1. What is Ms Access?
· Microsoft Access is a database management system (DBMS) from Microsoft that combines the relational Microsoft Jet Database Engine with a graphical user interface and software-development tools.
2. What is Database?
· A database is a collection of information that is organized so that it can be easily accessed, managed and updated.
3. What is Table?
· A relational database like Access usually has several related tables. In a well-designed database, each table stores data about a particular subject, such as employees or products. A table has records (rows) and fields (columns). Fields have different types of data, such as text, numbers, dates, and hyperlinks.
4. What is Field?
· A field in Microsoft Access is a piece of information related to a single person or thing.
5. What is Records?
· A record is made up of fields, such as name, address, and telephone number. Arecord is commonly called a row, and a field is commonly called a column. Microsoft Office Access 2007 allows you to add, edit, or delete records in a table directly in Datasheet view.
6. What is Query?
· A query is a request for data results, for action on data, or for both. You can use aquery to answer a simple question, to perform calculations, to combine data from different tables, or even to add, change, or delete table data.
7. What is Relationship?
· In a relational database (Access), the data in one table is related to the data in other tables. In general, tables can be related in one of three different ways: one-to-one, one-to-many or many-to-many. The relationship is used to cross reference information between tables.
8. What is Form?
· A form is a database object that you can use to create a user interface for a database application. A "bound" form is one that is directly connected to a data source such as a table or query, and can be used to enter, edit, or display data from that data source.
9. What is Report?
· A report is a database object that you use to display and summarize data. Reports provide a way to distribute or archive snapshots of your data, either by being printed out, converted to PDF or XPS files, or exported to other file formats.
10. What is Primary Key?
· A primary key is a field or set of fields with values that are unique throughout a table. Values of the key can be used to refer to entire records, because each record has a different value for the key.
How to Open?
Start > All Programs >ms Office >Ms Access 2010
How to Create a Database?
Click on Blank Database> Type a file name > Create.
How to create a Table?
Right click on Table 1 > Design View > Type a table name > Ok
|
Field Name |
Data Type |
Properties |
|
Roll (Primary Key) |
Auto Number |
|
|
Name |
Text |
General: Field Size-30 Format - > |
|
Address |
Memo |
|
|
Gender |
Text |
Lookup: Display Control-Combo Box Row Source Type – Value List Row Source-Male;Female |
|
Mobile No |
Number |
General: Field Size : Double Input mask-“+91”0000000000 |
|
Dob |
Date/Time |
General: Format-Medium date Input Mask-Medium date |
|
Monthly Fees |
Currency |
|
|
Physically Handicap |
Yes/No |
|
|
|
Hyperlink |
|
|
Documents |
Attachments |
Caption-Documents |
|
Photo |
OLE Object (Object Linking and Embedding) |
|
One to One Relationship
|
Details (Table 1) |
Marks (Table 2) |
|
Roll (PK) |
Roll (PK) |
|
Name |
Math |
|
Gender |
Eng |
|
DOB |
Hist |
|
Mobile |
Geo |
|
|
Total |
|
|
Per |
|
|
Grade |
To create one to one relationship :
Go to database tools > Relationship > Show table dialog box will be open > Add two different tables > Add > Close the dialog box >
Drag Roll of Details table to the Roll of Marks table > Enforce Referential Integrity > Create.
Add records in Details table. > Click on plus sign beside the each records > add records in details table.
Or open the details table > Add records one by one.
One To Many Relationship
Product and Customer Table
|
Product |
Customer |
|
Product id(Pk) |
Product id(do not use PK) |
|
Product name |
Customer name |
|
Price |
Mobile no |
|
|
address |
|
|
qty |
|
|
amount |
Many to Many
|
Student table |
Course table |
Student_course table |
|
Student_id(pk) |
Course_id(pk) |
Sl_no(pk) |
|
Student_name |
Course_name |
Student_id |
|
Address |
duration |
Course_id |
|
Mobile |
|
|
|
gender |
|
|
Select Query:
At first create a table> Add Records > Save the table.
To search any record with criteria we should create a select query.
Create > Query Design > Show table dialog box will be open > Select your table > Add > Close the Dialog Box.
Double click on fields > type criteria > run or save the query and run.
Update query
Create a table > create > query design > show table dialog box will be open> select your table > add > close the dialog box> double click on fields name > choose update query > type formula in update to row. Save the query and run the query.
Make Table Query
Create a table for table1
Go to create tab> query design > show table dialog box will be open > select your table1table> add fields name > click on make table option > type a table name > ok > save > run the query
If you don’t want to move the records from table1 to another table then you can use a criteria “is null” in the criteria row of make table query.
If you want to move records with a criteria (course : software) then you can type any one course name in the criteria row.
A new table will be created.
if you want to make a new table in another database, then you have to select a database name from make table option and type a table name.
Append Query
To copy records from one table to another table you can use append query.
First of all you have to sure that you have minimum two tables.
Create > query design > show table dialog box will be open > select your first table > add fields name > click on append query > choose table name (for another database you have to browse another database name first and then choose table name) > save the query or run.
If you copy the records with a criteria then you can type any criteria like (course : software ) in append query criteria row, then run your query. Check your second table where you copy records.
Delete query
To delete any record from the table we need to use delete query.
Create > Query Design > Show Table dialog box will be open > Select your table > Close
Add filed > Choose Delete Query > Type any Criteria > Save the query by Ctrl+W> Run the query.
Form
For is used to insert record into the table.
To create a form at first we need a table to insert record into the table by form.
After creating a table, go to Create tab > Form Wizard > Select Table or Query > Add Fields > Next > Select Columnar> Next > Type a form name > Modify the form’s Design > Next > Finished.
Ctrl+W for save the form. Open the form by double click > add records by tab key.
You can add buttons on form.
Report Wizard
Create any table and fill with records. Create > report wizard > select your table or query name > add fields name > choose a group field > next > select a sort field > next > select a layout > next > select a style >type a report name > modify the report’s design > finish.
You can modify your report in layout or design view.
Save the report and open your report.