Database Inventory System - Microsoft Access - Research Paper Example

 Table of Contents Introduction 1 Entity Relationship Diagram 2 Relationships (ERD) 2 Tables (Entities) 3 Database Overview & Screenshots 5 Forms 5 Reports 10 Queries 14 Macros 16 Introduction This paper will provide detail description of Microsoft Access Database “Inventory System”. The Inventory System developed in Microsoft Access will be used to store details of Items (Products) and Outgoing Items and publish different types of reports. This report is divided in two parts as Entity Relationship Diagram (ERD) and Database Overview and Screenshots. Entity Relationship Diagram (ERD) part will discuss the relationship (Entity Relationship) and tables in Inventory System and Database Overview and Screenshots part will provide details on Forms, Report, Queries and Macros in Inventory System. Entity Relationship Diagram Relationships (ERD) Figure 1: Entity Relationship Diagram for Inventory System Figure 1 shows the relationships between different tables in database Inventory System. All the relationships are described below: Outgoing table is related with Products table on attribute ProductNumber. Products table is related with Categories table on attribute CategoryID. Products table is related with Sector table on attribute ID. SubSector table is related with Sector table on attribute ID. Location table is related with Products table on attribute Location. Tables (Entities) There are seven tables in database Inventory System. The description of each table is given below: Name: This table contains the detail of the individuals who will use “Inventory System”. The attributes of the tables are Name, ID and Password. Products: Its each instance contains the details of item that exist in stock. This is the main table of the database Inventory System that keeps the details of all items in inventory. The main attributes of the tables are Product Number, Product Name, Product Descriptions, Category ID, Location, Z-Number, Sector, Quantity, and Issued date. Outgoing: This table keeps the record of all outgoing items from inventory. The main attributes of the table are Product Number, Order Number, Note, Pull Quantity, and Pull date. Categories: This table contains the list of the categories for the product in the database Inventory System. There are two attributes for this table as Category ID and Category Name. Location: This table provides the location of each item in the database Inventory System. Sector: This table contains the list of sectors (i.e. Bank, Internet, etc) for item in the database Inventory System. There are two attributes for this table as ID and Sector (sector name). SubSector: this is the sub table for Sector table and contains the sub sectors for sectors. The main attributes of this table are Sector Number (ID) and Sector Description). Database Overview & Screenshots Forms Screen Shot 1: Start-up (Name Form) Screenshot 1 shows the start-up screen when database is opened. This is similar to login form that will be used for entering in the database Inventory System. The users here select his/her name and click “Enter” button to enter in the database Inventory System. After clicking “Enter” Button the next form that comes is Main Menu form (Screen Shot 2). This is the navigation form for the database Inventory System. Users will navigate by clicking various buttons for Items, Outgoing Items and Reports. User can close database by clicking “Quit” button. Screen Shot 2: Inventory System Menu (Main Menu Form) Screen Shot 3: Item (Products Form) By clicking “Item” button in Main Menu (Inventory System) Products form opens (Screen Shot 3). This form will provide details of each product that is in inventory. There is a “Main Menu” button provided so that user can easily navigate to main menu of the database Inventory System. By clicking “Outgoing” button in Main Menu (Inventory System) Outgoing (Item) form opens (Screen Shot 4). This form will be used for viewing/entering items that are outgoing from the inventory. In this form too, “Main Menu” button is provided so that user can easily navigate to main menu of the database Inventory System. There is also an “Outgoing Report” button for generating report for that particular item outgoing from inventory. Screen Shot 4: Outgoing Item (Outgoing Form) Screen Shot 5: Report Menu (Report Menu Form) Screen Shot 5 shows the Report Menu form that comes by clicking “Report” button in the Main Menu of the database Inventory System. There are three button provided in this form as “Inventory” for generating Inventory Report based on Location of the product, “By Order Number” for generating report based on order number, and “Qty Less Than 10” for generating report for the products that quantity is less than 10 in the inventory. “Main Menu” button is also provided for going back to main menu of the database Inventory System. Reports Screen shot 6 shows the “Inventory Report” report based on locations for inventory items. This report is generated when user clicks the “Inventory” button in Report Menu form. Screen Shot 6: Inventory Report based on Location (Products Report) Screen shot 7 shows the “Report by Order Number” report based on order number for inventory items. This report is generated when user clicks the “By Order Number” button in Report Menu form. Screen Shot 7: Report by Order Number (Order Number Report) Screen shot 8 shows the “Qty. Less Than 10” report based on product number for inventory items. This report is generated when user clicks the “Qty Less Than 10” button in Report Menu form. Screen Shot 8: Quantity Less Than 10 (Q_QtyLess10 Report) Screen Shot 9 shows the “Outgoing” report for items that is generated when user clicks the “Outgoing Report” button in Outgoing form. Screen Shot 9: Outgoing Report (QR_PullonScreen Report) Queries Screen Shot 10: Queries Screen Shot 10 gives the list of queries that is used in the database Inventory System. A brief detail of some of the important queries is given below: LocationList: This query generates the list of all the locations. OrderNumberP1: Gives next order number (max order number +1). Outgoing_Q: Gives sum total of each outgoing items. Q_Outgoing_CalcQty: Gives sum total of each outgoing items, and remaining items in inventory. Q_QtyLess10: Gives details of items that are less than or equal to 10 in inventory. Q_SectorList: Gives sector list. Q_SubSector: Gives sub sector list for sector. QR_PullonScreen: Generates list of outgoing items based on order number. Qury_Outgoing: Generate list of outgoing items. Macros Macros are very useful for automating simple tasks, such as carrying out an action when the user clicks a command button. Screen Shot 11 gives the list of Macros that is used in the database Inventory System. Screen Shot 11: Macros AutoExec: Opens form Name at the time of start-up of database. MainMenu: For opening forms Products (Macro Name: Opt1), Outgoing (Macro Name: Opt2), Report Manu (Macro Name: Opt3), and Main Menu (Macro Name: Opt4). Also used for closing form Main Menu (Macro Name: Quit). Quit: Used for closing database (Macro Name: Quit) ReportMenu: For opening reports Products (Macro Name: Opt1), Q_QtyLess10 (Macro Name: Opt2), QR_PullonScreen (Macro Name: PullonScreen) and Order Number (Macro Name: Opt3). Return: For returning to form Main Menu (Macro Name: Return and Return2) Read More