Scribd
Upload
9 views12 pages

Lesson 2 Working With Ms Access

The document provides an overview of MS Access, detailing its capabilities as a relational database management system, including the creation of tables, primary and foreign keys, and the use of queries and forms. It outlines the steps to create tables, establish relationships, and generate queries and reports using wizards. Key concepts such as primary keys, foreign keys, and the functionality of queries and forms are emphasized for effective data management.

Uploaded by

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

Lesson 2 Working With Ms Access

The document provides an overview of MS Access, detailing its capabilities as a relational database management system, including the creation of tables, primary and foreign keys, and the use of queries and forms. It outlines the steps to create tables, establish relationships, and generate queries and reports using wizards. Key concepts such as primary keys, foreign keys, and the functionality of queries and forms are emphasized for effective data management.

Uploaded by

rehanparabatani494
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
RECA, PAPO Creating table using design view is used to create the table manually and it is the most common i , ofcreatinga table. ] i Aprimary key is the column that uniquely identifies each rowina table. Rules fora primary key: © _Ituniquely identifies each record. . i © Itshouldnotcontainanull value. © Eachtable can only have one primary key Example: Cust 1D (Customer Dis the primary key in Customerstable) © Scanned with OKEN Scanner ps to Create Primary key for Customers Table reate the Customers table using ‘Table design button’ and apply the Primary key. Click on Create tab on the ribbon, Click on" : of Tablos‘group. Access opensanew tablein Design View, allowing you toadd fields tothe table's design. Enter Field names with their Datatypes, _ Cust Name : ShortText E | Address Long Text. i Contact Number Short Text Clickon the Cust ID Field name, Click on Design tab and select Primary key button, Asmall key icon [P] appears on the left side of the Cust ID field, this shows that the Cust ID is the primary key fieldin the table, Data Type Number _ Short Text | Address tongtext |Contact Number ___ Short Text ieee! ~ Create the Orders table with field names Order ID, Cust ID, Product ID, Product Name, Price, Order Dateand Delivery Status. Apply the Primary key on ‘Order ID’ field name, Number Number - Product ID A ‘ShortText Product Name (short Text Price [Number || Order Date ___ Date/Time a | Delivery Status Short Text LED © scanned with OKEN Scanner customer I Or (WeuitiD™ =| custome +] aasress >| carte number «Cie To aa — ya Hyderabad | 99aessz24a 5 302 Aaradhya henna. -—-7344220011. |= 203 Aarah Bangalore 6088112200 \e 104 Srikar Amaravath! 9001117722 105 Rohan Tirupathi 110556677 ® 306 Virat Mumbat 7022334455 * 207 Eshwar Hyderabad 7730298998 ® 108 Rahul Delhi seagonz244 ij 4108 Shubham chennai 9948012345, (8 200 Peter Vizag 9393221122 * ° BE conones [Si ooo OrderIO. =| custid >] Producti >] Produ Name =] ree |OwerDate = [BAWERVSaRE > [cick to Aad > soot tornmiix Phone x 000 8/7/2018 ina 1002 02 crooiv Moto ZPlus 45000 6/33/2018 Delivered 1003 302 551968 Samsung nem TV 26900 6/2/2018 Pending 1008 1104 KTC8E6 Philips Power Bank 2199 6/21/2018 Delivered 1008 101 KTCES6—_—PhilipsPower Bank 2199 6/2/2038 Delivered 1008 105 cMws6——_Fasrack Watch 559 6/26/2018 Pending 1007 toe Fsxees Mion 15000 6/36/2038 n rant 1008, 107 Kecs23 Polo T shit 2500 6/20/2038 Delivered 1009 108 UvC635 ‘Spinner Fidget 599 6/26/2018 in Transit x00 403 GMise ——_Fasrck watch e539 6/21/28 in transit 3011 soa wrnizx 2000 #/7/2018 Delvered : eet — OREIGN KEY. A foreign key is a field in a relational table that matches the primary key column of another table. Creating a Relationship using Foreign Key 1. Click on Database Tools and select ‘Relationship’ button of Relationships group. 2. Show Table dialog box will appear. 3. Select the table names (Customers, Orders) thenclickon Add button. 4, Then click close button of Show Table dialog box. 5. Now both the tables will display in Relationship window. 6. Select the Cust ID column in Customers table and drag towards Cust ID column inthe Orders table. es Compactand | Visual Run Relationships Object Repair Database | Basic Macro Dependencies | Tools Macro Relationshios © scanned with OKEN Scanner Edit relationship dialog box will appear. a _ ee E ip dialog box will appear. Rone aE The foreign key can be used to cross-reference tables. Select Enforce Referential Integrity option. Click on Create button, Customers V custo Cust Name Adress Contact Number Cust 10 Predua 1D Product Name Pace Order Date Delivery Status Queries | Query is database object. Query is like a general question that we ask to get the specificinformation from the database. tis used to extract specific information from database based on certain condition. uery is used to apply afilter to the table's data, so that you only getthe information thatyou want. ‘ondition. : The result of query is . . ; - result of query isalso displayed in the form oftable. ienevitt E pysaeet © scanned with OKEN Scanner Creating queries can be done in two different ways 1, Query using Query Wizard. 2. Query using Design View. 1. Query using Query Wizard : Query wizard is a step by step process used to retrieve records from database table. For Example Runa query to retrieve the detailsi.e., Cust ID, Cust Name, Address from customers table and Product name and order date from Orders table. Follow the below. ‘steps. 1. Clickoncreate tab. 2. Select Query Wizard button of Queries group. New Query dialog boxwill appear. 3. Select Simple Query Wizard and click on OK button. Simple Query Wizard dialog box will appear. 4. Selectthe tablenamesand the columns from the list. (From Customers table select only Cust ID, Cust Name, Address and from orders table select only productnameand order date) 5. Clickon thenextbutton. 6. — Specify the title as Customer Query and click on Finish button, et Smetana || 7. Result willbe displayed [J cuteness) as shown below. ig 103 Aarah 6/21/2018 104 Srikar “Amaravathi oyaa/2ois 105 Rohan Tirupathi —_Fastrack Waten s/re/2018 106 virat Mumbal Mi Band srspross| 107 Eshwar Hyderabad Polo shirt 6/20/2038; 108 Rahul Detht Spinner Fidget 6has/20s| © scanned with OKEN Scanner Query using Design View: Design view allows more control ind the answers o over a query design. jign view makes it easy to fi f'some very complex questions. Example Query to retrieve the order details: of Customer whose Cust Dis 101 una tepstocreatea query using Design View Click on Create tab and select Query Design button of Queries group Show table dialog box will appear. Select the tables (Customers, Orders) and click Add button, . The tables will be added to Query window. Specify the table and fields to be extracted as shown in below. Specify the criteria i.e., CustID [Link] shown below. E EE Sy HT te, Oem | [=| 4 © Ci crostab & Pas-Trough |View Run Select Make Append Query Show! ff : Table Ieee BE DstaDefintion Seup> Hide m= ouvmee ‘s Pea ~ Gostomen Order iS Address Produet 0 = ¥ aes | Gane (|fE| cale a rreaaieone Navigation Pane © scanned with OKEN Scanner The result of the query will be as shown below To save a query. 1. Right click on Query and select save option. 2. ‘Save as’ dialog box will appear. 3. Type the Query name. 4. click on OK Query will be saved. orm: A form is a database object. Forms are like display cases in stores that make it easier to view or gett items that you want. It is used to edit, enter or display the data from a table or a query. It is easy interact with database. Forms offera friendly way of viewing the data, in that they show asingle recordata time. Creating a Form using Form Wizard : The Form Wizard provides step by step process to design: form. Steps to create form using Form Wizard : = 1 bh aed = Form wizard dialog box will appear. © scanned with OKEN Scanner Which elds do you want on yur form? ‘You can choose fram mare than one table or query. ‘Which ald do you wanton yur form? ‘You can choose fom more than one able or query TeblesQueies cr ‘Avalale Fede: ‘Selected Fildes eas Select the table / query from the drop down. Select the columns that are to be included and clickon Nextbutton. Select the Form Layout and click on Next button. 33 Te hs © scanned with OKEN Scanner | | I | Enter a title to the form and click on Finish button. Form will be displayed as shown Next Record button] [ Last Record button ‘To enter a new record into a table from Form. 1. Go to Last record and click on next record button. 2. _ Enter the new customer's information in the form and click next record button. 3. Newly added customer's information will be added in the Customers table. Areportisa database object. Itis used to display and print data in an organized manner. Creating Report using Report Wizard : Report Wizard provides step by step process to create a report. Stepstocreatea Report 1, Clickon Create tab 2, Select Report Wizard option of Reports group Report Wizard dialog box will appear. Select the fields to display in the report. 3. From Customers table select all fields, from Orders table select product name and delivery status. 4, — Select ‘By order’ to view the data, 34 © scanned with OKEN Scanner Mention the grouping levels, From the list select Cust ID and click on Next button. epee ee Select the sort order and mention the column to be sortedand click on Nextbutton, 7. Selectthe layout as tabular and Orientation as Portrait for the report and click on Next button. Enter the title name for the report as ‘ProductReport’ © scanned with OKEN Scanner Result of the reportas sh Product Report aad 10 adress emactnunber — rasuctime One 101 Rye vyderabad ssesssz204 tPhone x sea son Aarechye chennat snz001t oto 2 Pts pelivere 100 Aaah bangalore soes132200 Mersungoncay penne y tos star amaraath seou11772 pampspower sane eer 101 Riya Hyderabad 9989552244 phitips Power Bank sivered 10s Ronan srpatht 5110556577 Famer wen penne 106 vat ums 2338435 saioand i 107 eshwr siyderabas 70998998 polo shit oetere 106 fehl bet soasozee frinnerridget Tens 103 Aare sangaore 00112200 ceex watch wore 102 auras henna oa200%1 ‘Phone x petivered pagesof ured, December 07,2017 . @ Aprimary: key isthe: column that uniquely: identifies eachrowina table. = Aforeign key isa field ina relational table that matches the primary key column ofanother tabl: ‘The foreign key can be used to cross-reference tables. database. e specific information from the eral question thatweaskto getth query. Forms offera friendly we > AQueryislikeagen rordisplay the data from atable ora © Formsareused to edit, ente! ‘of viewing the data. Reportsare used to display and print data inan organized manner. Key Points to Reme: mber, 4, Primarykey isuniquely identifies each record. 2. Queryis database object. Query wizard isa step by step process used to retrieve records from database table. ‘The Design view allows more control overa query design. 5. Formsprovideadd buttonsand th i it a” ; other functionalities toa form to automate actions. . Form wit wizard speeds up the process of creating a form because it does all the basic work andit provides step by step process to designa form. 7. Re i: i port Wizard provides step by step processto createa report © scanned with OKEN Scanner Prima Ker uniquely identifies each record. é ! Query is ala base object. result of query known as n set i) The €[Link] 3. * w view allows more control over a query design. ae view is used to set the layout and design the form manually. Primary key should contain a null value. table cannot have more than one primary key. Query is not a database object. Data Sheet view provides to add controls in the layout. tee _Reposswait used to display the data in an organized manner. Multiple Choice Questions :3/ isa key that uniquely identifies the records, [al a) Primary key b) Foreign key ©) Composite key d) All of the above 2. The relationship button is used to relate the tables is present in__ tab. tcl a) Create b) Home ©) Database Tools ) Design 3. The result of queryjs also calledas___— l bh j a) Form b) Dynaset ©) Report 4) None of the above 4. {in button is present in__ tab. tel a) Home b) Create ¢) Design d) Database Tools used to edit, enter or display the data from a table or a query. Te) ‘able b) Query ©) Form d) Report PoC a 37 © scanned with OKEN Scanner

You might also like