Step by Step Guide to Ms Access

Document Sample
Step by Step Guide to Ms Access Powered By Docstoc
					Bikram Keshari Pahi                                                             bkp.pintu@gmail.com




                                           MS-ACCESS
         Ms Access is a component of ms office soft-ware package. It is also known as
         RDBMS (Relational database management system).Which can be used for
         maintaining different databases.

         DATA BASE A database is an organize collection of related information’s or data
         in tabular form about a specific subject. Generally, database is store house of
         information. Database is more same as table in general form.

         DBMS Database management system. This soft-ware or system used for managing
         a single database i.e. create, edit and maintenance of a single database is called
         DBMS. A DBMS involves on following basic management database.

              1)   Create database.




                                               m
                                           y. i
              2)   Insert data into the database.




                                         og h
              3)   Arrange data in the database.




                                             co
                                       ol Pa
              4)   Retrieve information from the database.
                                     hn ari
    RDBMS Relational data base management system. An RDBMS is defined as method
    of viewing information from several separate databases that relate to one another through
                                   ec h

    keyword. By using RDBMS we can maintain more than one database which can not
                                 at es


    possible in DBMS. Generally, an RDBMS is the combination of multiple DBMS with of
                               tn K



    some additional property of making relations through keywords. The RDBMS involves
    on following management of database.
                            .ra m
                           w ra




         1)   Create databases.
         2)   Insert data into the database.
                          w kBi




         3)   Arrange data.
         4)   Relate database.
         5)   Retrieve data from multiple databases through queries.

    Example:          MS Access, FoxPro, Oracle, Database SQL Server etc….
                        w




    MS- ACCESS         Ms Access is an RDBMS which used an electronic method for
    managing different databases.

    OBJECT            Ms Access consists of four basic objects:

         1.   TABLE
         2.   QUARIES
         3.   FORMS
         4.   REPORT

    TABLE Table is a collection of related information about a specific topic. In a table
    the information or data are stored in the cells formed by the intersection of row and



www.ratnatechnology.com                                                               www.ejajpur.in
Bikram Keshari Pahi                                                              bkp.pintu@gmail.com




    columns. In other word it is a closed rectangular area formed by a series of cells arrange
    in rows and columns.

    ELEMENTS OF A TABLE

        1. CELL The closed rectangular area formed by the intersections of rows and
            columns is called cell.
        2. FIELD The column of a database is called fields. A field can store similar kind
            of data.
        3. RECORD The rows in a database are called records, and each record consist
            of data values stored under a field.
        4. LABEL The text which are used for identifying a particular column in a table
            are called label.
        5. DOMAIN The maximum value of the field is called domain.
        6. RANGE The minimum value in the field is called range.
        7. PRIMARY KEY              Primary key refers to a field in a table that uniquely




                                               m
                                           y. i
                                         og h
            identifying each record in a table.




                                             co
                                       ol Pa
        8. RELATIONSHIP The tables in a relational database management system are
            tied together by the relationship which can be established through primary key.
    QUERIES Queries means a question designed by the data base designer or developers
                                     hn ari
    for finding record in a database according to some conditions. Queries are a systematic
                                   ec h

    way of retrieving information from a database.
                                 at es


    FORM Forms are the windows representation of table and queries. Generally, a form
    window shows one record at a time. We can use forms to show as well as insert records
                               tn K



    in any database.
    REPORT Reports are the written document of various information of our database.
                            .ra m




    Reports are used to represent the database.
                           w ra
                          w k




    STEPS TO START MS ACCESS
                             Bi




         1)   Click an start button, then click on program, then click on ms access.
         2)   Then select blank data base option. Then it will open the same database window.
         3)   Here type the name of your database.
                      w




         4)   Then click on save button.
         5)   After saving the data base a database window will open, which contain different
              objects like table, queries, forms and reports.

         N.B- Ms access first create a database then other objects we can store the data in a
         table, because we can store the data of a data base with in a table. It is the actual
         house of data.

         CREATING TABLE We can create table in three ways:
           1) Design view.
           2) Using wizard.
           3) By entering data.




www.ratnatechnology.com                                                                www.ejajpur.in
Bikram Keshari Pahi                                                                 bkp.pintu@gmail.com




         DESIGN VIEW           In this process we can create the table by using data view
         window by specifying the field name and their respective data type. As well as
         specifying various property of different data type.

         USING WIZARD             In this process the table can be created using pre-defined
         database field and their respective data types.

         BY ENTERING DATA In this process the table will be created in data sheet view
         window by specifying field names and then we have to enter data under that field.
         Ms-Access will automatically assign data type after entering two or three record
         values.
         N.B- A field name can be up to by character long including spaces.

         DATA TYPE It means the type of data a cell can hold under a field. In other
         words data type determines the kind of data a field can accept. Different data types in
         ms access are as follows:




                                               m
                                           y. i
                                         og h
                1) TEXT→ It follows character data only the maximum size is 255




                                             co
                                       ol Pa
                    characters.
                2) NUMBER→ It allows only numeric values consisting of numbers (0-9).
                    A number of data type may be of following type.
                                     hn ari
                3) BYTE→ It occupies one byte of memory and its range is (0-255).
                                   ec h

                4) INTEGER→ It occupies the two byte of memory and can store values
                                 at es


                    store values up to 35667.
                5) LONG→ It occupies ‘4 byte’ of memory and its range is ‘0’ to 232-1.
                               tn K



                6) FLOAT→ It stores real no and provides precision up to ‘10’ decimal
                            .ra m




                    places. Ex- 5,3, 6,9, 3,55009.
                           w ra




                7) DATE AND TIME→ It allows date and time data in various format.
                8) AUTO NUMBER→ It is the pre- defined numbering format which
                          w k




                    automatically inserted in the cells, starting from one to un word. We
                             Bi




                    needn’t have to enter values in this field.
                9) CURRENCY→ It allows currency type data with different currency
                    symbols like pound etc.
                10) BOOLEAN: (Yes/No) → It can allow only two type of value that is true
                      w




                    or false or yes or no. It displays a check box on the cell under exact field.
                    Which can accept Yes/ No values by mouse clicking?
                11) MEMO→ It allows lengthy text. This type of field can hold unlimited
                    amount of text. To insert text in a memo filed cell press shift+f2 for
                    opening a zoom window. Press ctrl + enter to starting a new line.
                12) HYPERLINK→ These data type can hold address of website, data base
                    object, or other office document under this field we can also insert E-mail
                    ID’s website addresses etc.
                13) OLE OBJECT→ OLE stands for object linking and embedding. This
                    data type can hold OLE type data like pictures, documents, sounds or
                    other kinds of data that are created by programs out side access.

    PROPERTIES OF DATA TYPE


www.ratnatechnology.com                                                                   www.ejajpur.in
Bikram Keshari Pahi                                                                bkp.pintu@gmail.com




    The properties of various data type are used for setting up various characteristics or
    attributes of that data type. We can set different data type property using table design
    view only.

    FIELD SIZE→ It set the size or amount of data to be entered in to the field.

    FORMAT→ It set the format of data to be entered into the field.

    CAPTION→ It set the name of the field which can be used in a form during its design as
    well as coding in visual basic.

    DEFAULT VALUE→ It is used to set the default value of a filed. This value will
    automatically enter under the field if we skip them.

    VALIDATION RULE→ It is used to set the rule or condition to be satisfied by the data
    under that field. If not it will give error message.




                                               m
                                           y. i
                                         og h
                                             co
                                       ol Pa
    VALIDATION TEXT→ It is used to set the error message to show whenever an error
    occur.
                                     hn ari
    INDEX→ It is used to set whether there is any duplicate value will be allow under the
                                   ec h

    field. It has two values i.e. Yes/No.
                                 at es


    REQUIRED→ It set whether the value in this filed is required or not i.e. compulsory or
                               tn K



    optional.
                            .ra m
                           w ra




    INPUT MASK
    It is an important property of a data type which uses various input mask characters for
                          w k




    specifying the data formats as well as limits the data to be entered in to the field.
                             Bi




    INPUT MASK CHARACTERS AND ITS FUNCTIONS

    CHARACTER                                           FUNCTION
                      w




    0                            →       It requires number 0-9 (compulsory). No +/-
                                         Sign allowed.

    9                            →       Optional no 0-9 or space but no + /- sign allowed.

    #                            →       Optional no 0-9 or space as well as +/- sign allowed

    L                            →       Required letter (A –Z) (compulsory) or space.

    ?                            →       Optional letter A-Z or space.

    A                            →       Required letter or number (compulsory).


www.ratnatechnology.com                                                                  www.ejajpur.in
Bikram Keshari Pahi                                                               bkp.pintu@gmail.com




    a                             →       Optional letter or no or space.

    Am percent ($)                →       Required character or space (compulsory).

    C                             →       Optional character or space.

    Less than         (<)                →        Characters followed must be in lower case.

    Greater than (>)              →       Characters followed must be in upper case.

    \ (back space)                →       The next character will be displayed as a constant.


    If you want to close→ CTRL+W
    Go to next step in table→ ALT+D




                                                 m
                                             y. i
    Row delete→ CTRL + (-) Add→ CTRL + (+)




                                           og h
                                               co
                                         ol Pa
    FORM
    Forms are the windows which provide the most flexible way of viewing record and
                                       hn ari
    entering data in a table. In other word a form is the pictorial representation of its
    respective table or query. Forms don’t store information but they provide a convenient
                                     ec h

    way to access the information’s stored in table.
                                   at es
                                 tn K



    ELEMENTS OF FORM
                              .ra m




         1) LABEL→ The name/ text used for identifying and object in a form is called
                             w ra




            label.
         2) COMMAND BUTTON→ these are used to accept user instructions for a
                            w kBi




            specific purpose, like, ok, cancel, next, back, delete etc.
         3) TEXT BOX→ It is used to accept user data as well as show a single data at a
            time. That data may be any number / character.
         4) LIST BOX→ It is used to display more than one data values at a time.
                        w




         5) COMBO BOX→ It is the short form of combination box. It is the both
            combination of text box and list box. So, at a time it will behave like a text box
            and in other time it will behave like a list box.
         6) OPTION BUTTON→ It is generally used for selection purpose. We can select
            one option from a group of option.
         7) CHECK BOX→ It is a special case of option button. But, difference is that we
            can choose more than one option.
         8) OPTION GROUP/ FRAME→ It is used to create a group of similar option
            buttons or check boxes with in a certain boundary or frame. So, a frame must
            have a label. Which is called a frame name or group name? The elements must be
            placed inside the frame.

    DRAWING ELEMENT



www.ratnatechnology.com                                                                 www.ejajpur.in
Bikram Keshari Pahi                                                                    bkp.pintu@gmail.com




    These are used for creating different figures like, lines, rectangular, ovals, circles etc.
    IMAGES
    It is used for displaying images or graphics or photos.

    CREATING A FORM
    AUTOFORM→ This is a feature of ms access. Which provides the quickest and easiest
    way to create a form for a table or a query? These are three types of auto form is
    available in ms access.

         1) Columnar auto form.
         2) Tabular auto form.
         3) Data sheet auto form.

         1) COLUMNAR AUTO FORM→ This type of auto form shows one record at a
            time. Where fields are arrange in columns. We can also used this form for
            inserting new records as well as deleting records from a table.




                                               m
                                           y. i
                                         og h
         2) TABULAR AUTO FORM→ In this type of auto form all the records of that




                                             co
                                       ol Pa
            table will be displayed in the form. Where, fields are arranging in rows.

    Ex:-
                                     hn ari
               Name                   Roll                 Mark
                                   ec h
                                 at es
                               tn K
                            .ra m
                           w ra
                          w kBi




    But in columnar

    Ex:-
                      Name
                       w




                      Roll


                      Mark




         3) DATA SHEET AUTO FORM→ In this type of auto form the records are shown
            as the same way as they appear in data sheet view of the table.




www.ratnatechnology.com                                                                      www.ejajpur.in
Bikram Keshari Pahi                                                               bkp.pintu@gmail.com




    Ex:-
                  Name                         Roll                           Mark
                   Ram                          1                             350
                  Shyam                         2                             360
                  Suvam                         3                             445
                   Sarat                        4                             515

    STEPS
      1) Select the form object
      2) Click on new button. Here select the type of auto form.
      3) Then select the table in a table combo box.
      4) Then click on ok.

    BY USING WIZARD
    Form wizard is a tool of ms access which can be used for creating forms of a table or
    query. Generally a form wizard is a serious of dialogue boxes. That walks you through all




                                               m
                                           y. i
                                         og h
    the steps needed to create a form.




                                             co
                                       ol Pa
    STEPS                            hn ari
      1) Click on form object.
      2) Then double click on create form by using wizard option.
                                   ec h

      3) Then select the table at the fields from the field list box as for your requirement.
                                 at es


      4) Choose the column layout and click on next.
      5) Then select any back ground. Enter a name for your form.
                               tn K



      6) Then click on finish button.
                            .ra m




    FORM DESING USING DESIGN VIEW
                           w ra




    We / You can create a form using design view in which we have to place different fields
                          w k




    shown in the field list and placing other form elements like text box, list box, combo box,
                             Bi




    command button etc. on a blank form.

    STEPS
      1) Select form object click on new button. Here, select form design view option.
                      w




      2) Then select the table or query.
      3) Then click on ok. After that a blank form will display with tool boxes and field
         list of the selected table or query.
      4) Then select the field name from the field list drag and drop it on the form.
      5) Continue this process until all the required fields you add on the form. Then place
         the controls or form element from the too box in the form.
      6) Then select the operation to be done by the control you have placed in the form.
         When the command wizard. Then save and close.

    N.B.-.
                                                                          Command wizard
                                                                          should be on.




www.ratnatechnology.com                                                                 www.ejajpur.in
Bikram Keshari Pahi                                                                bkp.pintu@gmail.com




    FINDINGS RECORDS IN A DATABASE
    Ms Access provides two ways to search records in a database.
       1) By using filter.
       2) By using query.

    BY USING FILTER→ A filter can be used for finding records in a table by using
    simple criteria. A filter only shows the records of a single table. We can’t use the filters
    for displaying the records in multiple tables. Generally, filter is a tool by ms access and
    we can’t wave then for future use.

    BY USING QUERY→ Query is an object of ms access RDBMS. Just like other objects
    we can create the queries and save then form later use. In this case we can search for
    records by using complex criteria. Queries are differing for filter by following ways.

         1) A query can be saving as a database object but a filter can not save for lettering
            use future use.




                                               m
                                           y. i
                                         og h
         2) We can apply filter only to a single table of a database. but, we can applies the




                                             co
                                       ol Pa
            queries to a single table as well as multiple tables combined together through
            relationship.
         3) We can create a form or report using query. But, it is not possible by the filter.
                                     hn ari
         4) Filter is an application of ms access RDBMS. Where as query is an object of ms
                                   ec h

            access RDBMS.
                                 at es


    DIFFERENT FILTER AVAILABLE IN MS ACCESS
                               tn K
                            .ra m




         1)   Filter by selection.
         2)   Filter excluding selection.
                           w ra




         3)   Filter by form.
                          w k




         4)   Filter form.
                             Bi




         5)   And filter.
         6)   Or filter.
         7)   Advanced filter.
                      w




         1) FILTER BY SELECTION→ In this type of filter process the records in a table
            will be displayed which matches the selected data values of a particular field.

         STEPS
         1) Open the table.
         2) Select the data value of the table.
         3) Right click over it.
         4) Then select filter by selection option.

         2) FILTER EXCULDING SELECTION→ It is the reveres process of filter by
            selection. In this case the records will be display. Which doesn’t contain the
            selected data values of particular field?




www.ratnatechnology.com                                                                  www.ejajpur.in
Bikram Keshari Pahi                                                               bkp.pintu@gmail.com




    STEPS
      1) Open the table.
      2) Select the data values.
      3) Right click over it.
      4) Then select filter excluding selection option.

         3) FILTER FORM→ It is same as filter by selection. Process but can be applicable
            in case of a table contain large number of records. Which can’t be displayed in a
            single screen?

    STEPS
      1) Open the table.
      2) Right click on the field.
      3) Then click on filter for text box. Then specify data value for search.
      4) Then press enter.




                                               m
                                           y. i
                                         og h
         4) FILTER BY FORM→ This type of filter is used for filtering a table using one or




                                             co
                                       ol Pa
            more field values. With this option we can choose our filtering value from a
            dropdown list of all possible values for that field.
                                     hn ari
    STEPS
                                   ec h

      1) Open the table.
                                 at es


      2) Go to record menu.
      3) Select filter option. Then select filter by form sub option.
                               tn K



      4) Select filter field from the dropdown list.
                            .ra m




      5) Click on apply filter on the tool bar.
                           w ra




         6) AND FILTER→ In this type of filter we can search for records which satisfies
                          w k




            more than one condition. And filter can be applied by using filter by selection,
                             Bi




            filter by form and filter for process.

         7) OR FILTER→ In this type of filter we can display the records which satisfies
            either of multiples criteria’s. or filter can be applied through filter for.
                      w




         8) ADVANCED FILTER→ By using advanced filter we can combine conditions
            and fields freely for a filter.

    STEPS
      1) Open the table.
      2) Go to records menu.
      3) Select filter option.
      4) Then select advanced filter / sort sub option.
      5) Then write the type of advanced filter. i.e. and filter / or filter.
      6) Click on apply filter option.

    CREATING AND FILTER IN ADVANCED FILTER



www.ratnatechnology.com                                                                 www.ejajpur.in
Bikram Keshari Pahi                                                              bkp.pintu@gmail.com




    STEPS
      1) Open the filter window.
      2) Drag a field from the field list and drop it on the 1st column.
      3) Write the data value in the criteria row of the same column.
      4) Drag another field from the field list and drop it on the second column.
      5) Write the data value in the criteria row of that column.
      6) Then click on apply filter button for applying advanced and filter.

    CREATING OR FILTER USING ADVANCED FILTER

    STEPS
      1) Open the filter window.
      2) Drag the fields from the field list and drop it on the 1st column.
      3) Write the data value in the criteria row of the same column.
      4) Drag another field from the field list and drop it in the 2nd column.
      5) Write the data value in or row of the same column.




                                               m
                                           y. i
                                         og h
      6) Click on apply filter option to apply advanced or filter.




                                             co
                                       ol Pa
    QUERY
    Query is the systematic way of retrieving data from a database. Generally, a query means
                                     hn ari
    a simple question or criteria. Which have to be satisfied by the records to display?
                                   ec h

           Ms Access provides following type of queries.
                                 at es


        1) Select query
        2) Update query.( calculation / modification).
                               tn K



        3) Append query.
        4) Delete query.
                            .ra m




        5) Make table query.
                           w ra




        6) Totals query.
                          w k




        7) Cross tap query ( analysis of data)
                             Bi




    1) SELECT QUERY→ It can be used for displaying the records of a table or multiple
    tables satisfying the given criteria.
                      w




    DIFFERENT CRITERIA

    IS NULL→ Looks for blank field values?
    TRUE/FALSE→ Looks for record in a Boolean or Yes /No field.
     BETWEEN→ It search for the records which satisfies the specified range of value.
    ASTERISIC (*)→ All → It is the wild card character which can be used in place of
    multiple characters.
    (R* → It shows in which the name in (Raj) R character).
           Select query can used multiple criteria to find records when you want to find
    records that satisfy more than one criteria’s. It is done through and/or query.

    AND QUERY→ In case of and query we have to write all criteria in criteria row. If we
    want to create the and query to a single field we have to use the and logical function.



www.ratnatechnology.com                                                                www.ejajpur.in
Bikram Keshari Pahi                                                               bkp.pintu@gmail.com




    OR QUERY→ To find records that matches either criteria we can create or query. Or
    query select recodes that satisfying one criteria or another. We can create the or query by
    writing the criteria’s in separate line from criteria on word.

         2) UPDATE QUERY→ We can change the value of fields in a data base to
            specified value by up date query. We can also calculate the value of different
            fields by using other field values.

    STEPS
      1) Click on query object.
      2) Then click on new button.
      3) Then select designed view and click on ok.
      4) Go to query menu, select update query. In the update query window add the tables
         you want to change.
      5) In the update query grid specify the fields you want to update the values. In the
         update to row write the formula for their calculation.




                                               m
                                           y. i
                                         og h
      6) Then run the query.




                                             co
                                       ol Pa
    N.B. → The field names are enclosed within square bracket. ([]).
                                     hn ari
         3) APPEND QUERY→ Using this query we can insert the data from one table to
                                   ec h

            another table. The tables do not need to have the same member of fields or the
                                 at es


            same field name. If the tables have the matching field names then the append
            query automatically specify the field names in its appropriate row. Other wise we
                               tn K



            have to maintain the field name to append.
                            .ra m




    STEPS
                           w ra




      1) Click on query object.
                          w k




      2) Then click on new button. Here select design view and click on ok.
                             Bi




      3) Then go to query menu, select append query.
      4) Then select the table to append.
      5) Then maintain the field name in the grid as well as append to field name.
      6) Run the query.
                      w




         4) DELETE QUERY→ It is an action query which is used to clear the record from
            a table which are no more useful. In this query we have to specify the condition
            to delete. The records from the table.

    STEPS
      1) Select query object.
      2) Click on new, then select design view.
      3) Then click on ok
      4) Then add the tables.
      5) Go to query menu.
      6) Then select delete query. Then drag and drop different fields from the table.
         Specify the criteria in criteria row or “or row”.



www.ratnatechnology.com                                                                 www.ejajpur.in
Bikram Keshari Pahi                                                                    bkp.pintu@gmail.com




         5) MAKE TABLE QUERY→ It is an action query which is used to create a new
         table by combining the fields of existing tables. Generally make table query method
         helps in creating the tables which summaries the fields and respective data values
         from existing table. So that we can analyses the data very easily and efficiently.

    STEPS
      1) Select query object.
      2) Click on new button.
      3) Then select design view.
      4) Then click on ok. Then add the tables.
      5) Go to query menu, select make table query.
      6) It will open a dialog box. Here, specify the name of the new table.
      7) Then click on ok.
      8) Then drag and drop the fields from field list. Then specify the condition.
      9) Then click on run button.




                                               m
                                           y. i
                                         og h
                                             co
                                       ol Pa
    REPORT
    Repot is an effective way to represent your data in printed format. All the visual effects
    and features that your want to see on printed paper. From your database will be designed
                                     hn ari
    in report document. We can also do same modifications or formatting in the report by
                                   ec h

    using different tools. Which increases the appearance as well a s attractiveness of the
                                 at es


    report.
                               tn K



    DIFFERENT WAYS TO CREAT REPORT IN ACCESS
                            .ra m




       1) Auto report.
       2) Report wizard.
                           w ra




       3) Label wizard.
                          w k




       4) Chart wizard.
                             Bi




       5) Report design method.

         1) AUTO REPORT→ It is the quickest way to crate report for a table or query.
            Auto report also two types- 1) Columnar auto reports, ii) Tabular auto reports
                      w




         i)       Columnar auto report→ In this type of auto report the records from a table or
                  a query show with the fields arranged in column. i. e. all the records arranged
                  in column with the respective field name on their lift.
         ii)      Tabular auto reports→ This type of auto report shows records in a tabular
                  listing fashion. In this layout all the fields in a table or query are arranged in
                  column and each record is shown as a row.

         2) REPROT WIZARD→ It helps in creating the report of the single or multiple
            table or query, going through certain steps. In this method you have to specify the
            report heading and the information of various elements of your report during its
            design.




www.ratnatechnology.com                                                                      www.ejajpur.in
Bikram Keshari Pahi                                                               bkp.pintu@gmail.com




         3) LALBE WZARD→It creates report designs that can be used to print levels to fit.
            The standard forms like Avery, Expe, Herman etc. it also gives you the option to
            set up your own label specification. If you are not working with once that match a
            redimate format.
         4) CHART WIZARD→ A chart is a pictorial representation of data values of a
            table or query. We can use chart wizard for creation of different type of chart
            which suit our requirement. We can also do same modifications like setting of
            back ground. Displaying or hiding the grid lines, naming char x-axis and y-axis
            and many more to enhance the attractiveness and under stability of our char.
            Which can be more easy to analyze?
         5) REPROT DESIGN→ By using design view method we can create our report
            using various tools available under this method. We can place different fields as
            our need and make some graphic design using different drawing options as well
            as can do same other modification. In this method we have to make the heading of
            the report and also do different formatting to that heading. We can place different
            fields of the table arranging them as our requirement. After creating the report we




                                               m
                                           y. i
                                         og h
            have to save them as a separate object of our database.




                                             co
                                       ol Pa
                                     hn ari  The end
                                   ec h
                                 at es
                               tn K
                            .ra m
                           w ra
                          w kBi
                      w




www.ratnatechnology.com                                                                 www.ejajpur.in

				
DOCUMENT INFO
Shared By:
Stats:
views:28
posted:11/16/2012
language:
pages:13