Scribd
Upload
6 views6 pages

MS Access Training Lessons Guide

The document outlines a series of lessons for creating and managing databases using MS Access. It includes instructions for creating tables, adding records, setting primary keys, sorting data, and performing calculations. Additionally, it covers creating forms, establishing relationships between tables, and generating reports.

Uploaded by

shreyamanu03
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views6 pages

MS Access Training Lessons Guide

The document outlines a series of lessons for creating and managing databases using MS Access. It includes instructions for creating tables, adding records, setting primary keys, sorting data, and performing calculations. Additionally, it covers creating forms, establishing relationships between tables, and generating reports.

Uploaded by

shreyamanu03
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

JADAVPUR YOUTH COMPUTER TRAINING CENTRE

KOLKATA-700032

LESSON-1

1. CREATE A DATABASE WITH YOUR FULL NAME.

2. CREATE A TABLE (ORDER) WITH THE FOLLOWING STRUCTURE: -

FIELD NAME DATA-TYPE FIELD SIZE

ORDERNO AUTONUMBER
ITEM TEXT 15
PRICE NUMBER SINGLE

3. SET ORDERNO AS A PRIMARY KEY

4. ADD THE FOLLOWING RECORDS INTO THE ABOVE CREATED TABLE

ORDERNO ITEM PRICE


1 PC 20000
2 MODEM 1200
3 TV 29000
4 AIR CONDITIONER 35000

5. INSERT A NEW FILED AREA WITH DATA TYPE :- TEXT AND FIELD SIZE:- 30
AND INSERT THE FOLLOWING DATA:- SALT LAKE CITY, BEHALA, SOVABAZAR, JADAVPUR

6. SORT THE ABOVE TABLE IN ASCENDING ORDER OF ITEM

7. CREATE A FORM USING FORM WIZARD WITH THE FOLLOWING ACTION BUTTONS:-

a. NEW RECORD
b. SAVE RECORD
c. NEXT RECORD
d. PREVIOUS RECORD
e. CLOSE FORM

MS-ACCESS Page 1
JADAVPUR YOUTH COMPUTER TRAINING CENTRE
KOLKATA-700032

LESSON-2

1. CREATE A TABLE WITH THE FOLLOWING STRUCTURE (TAKING EMPNO AS PRIMARY KEY). AND INPUT THE
FOLLOWING RECORDS:-

FIELD NAME DATA-TYPE FIELD SIZE

EMPNO AUTONUMBER
ENAME TEXT 20
DEPT TEXT 10
BASIC NUMBER SINGLE

ENO NAME DEPT BASIC

1 RAKESH ACCTS 6500


2 SOURAV MKTG 6000
3 NIKHIL PERS 4500
4 SUNIL ACCTS 3000
5 AMITABHA MKTG 2000
6 SUBRATA PERS 7000

2. DISPLAY ALL THE RECORDS OF THE EMPLOYEE WHOSE BASIC IS MORE THAN 5000
3. DISPLAY NAME, DEPT AND BASIC OF THE EMPLOYEE WHOSE BASIC IS BETWEEN 6000 AND 7000
4. DISPLAY ALL THE RECORDS OF THE EMPLOYEE WHOSE EMPNO IS 1 AND 2
5. DISPLAY ONLY THE NAME WHOSE DEPT IS ACCTS
6. DISPLAY ONLY SUNIL’S RECORD
7. DISPLAY ALL THE RECORDS OF THE EMPLOYEE WHOSE DEPT ARE NOT ACCTS AND BASIC MORE THAN 6000
8. DISPLAY ONLY THE EMPNO AND NAME WHOSE DEPT IS ACCTS AND BASIC IS MORE THAN 5000
9. DISPLAY ONLY THOSE RECORDS WHOSE NAME STARTS WITH ‘S’
10. DISPLAY ONLY THOSE RECORDS WHOSE NAME STARTS WITH ‘A’ AND ENDS WITH ‘A’
11. DISPLAY ONLY THOSE NAMES WHOSE 2ND CHARACTER IS ‘I’ OR 2ND LAST CHARACTER IS ‘I’
12. DISPLAY ONLY THOSE NAMES WHOSE 2ND CHARACTER IS ‘I’ AND 2ND LAST CHARACTER IS ALSO ‘I’

MS-ACCESS Page 2
JADAVPUR YOUTH COMPUTER TRAINING CENTRE
KOLKATA-700032

LESSON-3

1. CREATE A TABLE (TOY) WITH THE FOLLOWING STRUCTURE (TAKING CODE AS PRIMARY KEY). AND INPUT THE
FOLLOWING RECORDS:-

FIELD NAME DATA-TYPE FIELD SIZE

CODE AUTONUMBER
ITEM TEXT 20
COLOUR TEXT 15
PRICE NUMBER SINGLE

CODE ITEM COLOUR PRICE


1 CAR RED 300
2 BUILDING DARK BLUE 120
3 DOLL BLONDE 250
4 MARBLE RED STRIP 80

2. CREATE A TABLE (ASSET) WITH THE FOLLOWING STRUCTURE (TAKING CODE AS PRIMARY KEY)

FIELD NAME DATA-TYPE FIELD SIZE

CODE AUTO NUMBER


ITEM TEXT 50
PRICE NUMBER SINGLE
DISCOUNT NUMBER SINGLE
NET NUMBER SINGLE

CODE ITEM PRICE DISCOUNT NET


1 WATCH 2400
2 CALCULATOR 150
3 BLUETOOTH DEVICE 1999
4 NOTE 11000

3. ADD THE DATA OF ITEM AND PRICE FROM TABLE “TOY” TO TABLE “ASSET” USING APPEND QUERY
4. CALCULATE THE DISCOUNT OF TABLE ASSET AS DISCOUNT= 12% OF PRICE
5. NET = PRICE – DISCOUNT
6. MAKE THE DISCOUNT AND NET COLUMN UP TO 2 DECIMAL PLACES

MS-ACCESS Page 3
JADAVPUR YOUTH COMPUTER TRAINING CENTRE
KOLKATA-700032

LESSON-4

7. CREATE A TABLE (STUDENT) WITH THE FOLLOWING STRUCTURE (TAKING ROLL AS PRIMARY KEY). AND
INPUT THE FOLLOWING RECORDS:-

FIELD NAME DATA-TYPE FIELD SIZE

ROLL AUTONUMBER
NAME TEXT 20
EXAM1 NUMBER INTEGER
EXAM2 NUMBER INTEGER
LAB1 NUMBER INTEGER
LAB2 NUMBER INTEGER

ROLL NAME EXAM1 EXAM2 LAB1 LAB2


1 RAKESH 76 80 45 46
2 SOURAV 65 54 40 42
3 SACHIN 89 91 50 48
4 MOUMITA 70 88 44 45

8. CREATE A TABLE (RESULT) WITH THE FOLLOWING STRUCTURE (TAKING ROLL AS FOREIGN KEY)

FIELD NAME DATA-TYPE FIELD SIZE

ROLL NUMBER LONG INTEGER


TOTAL NUMBER INTEGER
AVERAGE NUMBER DOUBLE

9. INSERT THE FOLLOWING DATA INTO THE ROLL FIELD OF “RESULT” TABLE :- 1, 2, 3, 4

10. ESTABLISH THE RELATIONSHIPS BETWEEN TABLES STUDENT AND RESULT ON THE BASIS OF
PRIMARY KEY (ROLL) OF STUDENT TABLE AND FOREIGN KEY (ROLL) OF RESULT TABLE.

11. CALCULATE TOTAL = EXAM1 + EXAM2 + LAB1 + LAB2

12. CALCULATE AVERAGE = TOTAL / 4 OR (EXAM1+EXAM2+LAB1+LAB2) / 4

13. FORMAT THE AVERAGE VALUE UP TO 6 DECIMAL PLACES

MS-ACCESS Page 4
JADAVPUR YOUTH COMPUTER TRAINING CENTRE
KOLKATA-700032

LESSON-5

1. CREATE A TABLE WITH THE FOLLOWING STRUCTURE (TAKING EMPNO AS PRIMARY KEY). AND INPUT THE
FOLLOWING RECORDS:-

FIELD NAME DATA-TYPE FIELD SIZE

EMPNO AUTONUMBER
ENAME TEXT 20
DEPT TEXT 10
BASIC NUMBER SINGLE

ENO NAME DEPT BASIC

1 RAKESH ACCTS 4550


2 SOURAV MKTG 6000
3 NIKITA PERS 7000
4 SUNIL ACCTS 5500
5 AMITABHA MKTG 3200
6 SUBRATA PERS 7000

2. SET 3 NEW FIELDS AS PER FOLLOWING:-


FIELD NAME DATA-TYPE FIELD SIZE
HRA NUMBER DOUBLE
DA NUMBER DOUBLE
NET NUMBER DOUBLE

3. CALCULATE THE HRA. IF BASIC > 5000 THEN HRA = BASIC * 15% ELSE HRA = BASIC * 12%.

4. CALCULATE THE DA. IF BASIC > 5000 THEN DA = BASIC * 25% ELSE DA = BASIC * 20%.

5. CALCULATE NET = BASIC + HRA + DA

6. DELETE ALL THE RECORDS WHOSE BASIC IS BELOW 5000 USING DELETE QUERY

MS-ACCESS Page 5
JADAVPUR YOUTH COMPUTER TRAINING CENTRE
KOLKATA-700032

LESSON-6

7. CREATE A TABLE WITH THE FOLLOWING STRUCTURE (TAKING ROLLNO AS PRIMARY KEY). AND INPUT THE
FOLLOWING RECORDS:-

FIELD NAME DATA-TYPE FIELD SIZE

ROLLNO AUTONUMBER
NAME TEXT 20
LAB1 NUMBER INTEGER
LAB2 NUMBER INTEGER
EXAM1 NUMBER INTEGER
EXAM2 NUMBER INTEGER

ROLLNO NAME LAB1 LAB2 EXAM1 EXAM2

1 NIKHIL 20 20 80 80
2 PARTHO 18 19 74 76
3 ROMIT 10 8 48 52
4 JOYDEEP 20 18 65 75
5 SUMIT 14 20 45 72
2. SET 2 NEW FIELDS AS:-

FIELD NAME DATA-TYPE FIELD SIZE


TOTAL NUMBER INTEGER
GRADE TEXT 15

3. TOTAL = LAB1 + LAB2 + EXAM1 + EXAM2

4. GRADE WILL BE AS PER FOLLOWING RULE:-

a. GRADE = A FOR TOTAL >=180


b. GRADE = B FOR TOTAL >=160
c. GRADE = C FOR TOTAL >=140
d. GRADE = D FOR TOTAL >=120
e. GRADE = F FOR TOTAL <120

5. CREATE A REPORT

6. GIVE A SUITABLE REPORT TITLE

MS-ACCESS Page 6

Common questions

Powered by AI

A primary key is a unique identifier for each record in a table, ensuring no duplicate entries. In the ‘EMPLOYEE’ table, ‘EMPNO’ is the primary key, uniquely identifying each employee. A foreign key, on the other hand, is used to link two tables together, and references the primary key of another table. In the 'RESULT' table, 'ROLL' acts as a foreign key linking it to the 'Student' table .

To establish a relationship between the 'Student' and 'Result' tables, you need to use the 'ROLL' field as the primary key in the 'Student' table and as the foreign key in the 'Result' table. This relationship is important as it allows for data integrity and enforces referential integrity, ensuring that each record in the 'Result' table corresponds to a valid record in the 'Student' table .

First, calculate the total using the formula TOTAL = EXAM1 + EXAM2 + LAB1 + LAB2 for each student. Then, compute the average using AVERAGE = TOTAL / 4. The average value is formatted up to 6 decimal places to ensure precision in the data representation .

Calculated fields such as HRA, DA, and NET provide comprehensive insights into employee compensation beyond the basic salary. They allow for automated compensation calculations based on specific conditions (such as basic salary thresholds), giving a clearer picture of total earnings and benefits. This helps in transparent and efficient payroll management .

DELETE queries are used to remove unwanted or redundant data, such as eliminating records with a BASIC salary below 5000. This helps maintain focus on relevant data points, optimizes database performance, and cleanses the dataset by aligning it with the analysis requirements. Such operations remove clutter, making the database more efficient .

An append query adds records from one table to the end of another table. In the case of the 'TOY' and 'ASSET' tables, the append query transfers data fields like ITEM and PRICE from 'TOY' to 'ASSET', effectively expanding the 'ASSET' table with new records without altering existing data. This operation is efficient for consolidating similar datasets .

Formatting numerical outputs to specific decimal places involves setting a predetermined number of decimal digits for precision and consistency in calculations, like for DISCOUNT and NET values which are formatted to two decimal places. This ensures accuracy in financial calculations and a professional presentation of values, facilitating clear and precise financial reporting .

Sorting data in ascending order by 'ITEM' in the 'ORDER' table helps enhance data readability and facilitates easier data retrieval and analysis. It allows users to view records in a logical and organized manner, improving data handling efficiency and enabling quick access to specific entries .

Adding a new field to a table, such as 'Area' in the 'ORDER' table, involves specifying the field name, data type, and field size. This process enhances the table by allowing additional data capture and categorization. The specific purpose in this scenario is to store location data associated with each order, which helps in data categorization and retrieval based on geographical fields .

Setting a field size and data type is crucial as it determines the kind of data that can be stored in each field, ensuring data is stored efficiently and accurately. Field size restricts the input length, helping to maintain consistent data integrity across records. This setup also optimizes storage use and prevents data entry errors by restricting data to defined types and lengths .

You might also like