Simple Invoicing Desktop Database with MS Access 2013/2016

1 Simple Invoicing Desktop Database with MS Access 2013/2016 David W. GerbingSchool of Business AdministrationPortland State UniversityJuly 7, 2018 CONTENTS1 Contents1 Create a New Database12 Customer and product Customer Table .. the Customer Table .. the Changes .. Key .. Type .. Fields .. Remaining Customer Fields .. Customer Data .. Customer Data .. product Table .. Table .. Fields and Primary Key .. Description and Price Fields .. Products .. product Data ..63 Database Order and OrderLine Tables .. the Order table .. the Order Table Foreign key .. and Close the Order Table .. the OrderLine Table and Fields .. Table Relations .. the Database Structure.

2 94 The Create the Invoice Form .. the Form Wizard .. the Fields .. the Fields from the Order Table .. the Fields from the Customer Table .. the Fields from OrderLine and product Tables .. Edit OrderLines Subform .. Width of OrderLine Fields .. Calculation .. Subtotal Calculation .. Subtotal Calculation with the Currency Format ..135 Enhance the Shorten Blank Space on the Subform .. Change Customer ID Label .. Add Company Name to Top ..14c 2016 David W. GerbingJuly 7, 2018 CONTENTS26 Process an Display the Invoice for Data Entry .. Print the Invoice ..157 Query the Database168 Database Grouping Variables .. Calculate Totals.

3 20c 2016 David W. GerbingJuly 7, 2018 Simple Invoicing Desktop Database with MS Access 2013A Database Management System (DBMS) is a set of procedures and tools to store andretrieve information. The Database itself is the stored information. The types of informationstored in the Database are defined by the corresponding data structures. Thedatabasestructureoverall consists of thetables, their constituent fields, and the relations in the Database is stored in these DBMS consists of more than just the data. The DBMS also includes forms, queriesand reports. Theformsare the displays for screen and print that allow entering newinformation into the Database tables and displaying the existing information.

4 Thequeriesare searches of the Database that extract specified information. Thereportsare formatteddisplays of the extracted information, for screen and for four Database constructs are available from theCreatetab in are the directions for creating a Simple Database to generate an invoice forselling products to Create a New DatabaseThere are two ways to interact with a Database : design and use. The Database user canbe, and most often is, oblivious to the underlying design principles. However, someone hasto design the Database , to create the tables and their relations, to build the forms, andimplement queries and reports. The designer interacts with the Database in various designmodes that the most users never experience.

5 These interactions are typically iterative, asinitial designs are continually modified as a reflection of user starting the Access application, to begin a new data baseclick theBlank Desktop databaseicon. At the bottom-right of theopening window, specify the name of this new, blank Database , herenamedMyFirst. Note where the Database is stored, by default, insidetheDocumentsfolder. If, for example, you are working on a publiccomputer, you will likely wish to store the file on a file system that youcan Access from anywhere, such as on yourH:drive. Click the browseicon to change the location. Click theCreatebutton to Customer and product TablesTo construct an Invoicing Database , there needs to be customers who wish to purchase theproducts to sell.

6 The first steps for the design of this Database are the creation of therespective tables to hold the customer and product can happen in a Database without at least one table to storeinformation. A new Database opens with a table called Table 1, createdand ready for additional DBMS requires different views of the Database from which to a different task, with its own functions, rules andmenu system. The most fully featured editing of the Database structuretakes place in theDesign View. To move to this View, click theViewdrop down menu at the top-left corner, selectDesign Customer Name the Customer TableThe initial Database structure consists of only a blank tablenamed Table 1, with no fields.

7 When moving to theDesignViewa prompt appears to save the table, with an option toprovide a new name. Call the first table in the Invoicing Database the Customer Save the ChangesAt any point, the changes made to a table or other Database en-tities can be accomplished by right-clicking the correspondingtab. For the Customer table, right-click the tab named Cus-tomer, and selectSaveto save changes up to that Primary KeyAfter naming the table, a window appears with the fields for the table listed by rows. Thefirst field is the table s primary key field, which can be reset by clicking thePrimary Keyicon at the top left in the toolbar. The defaultField Nameof the primary key field Data TypeEach field in a Database stores information in a specific way, definedby itsData Type.

8 Most data fields tend to beShort Textfieldsfor generic text, but there are several other possibilities shown inthe accompanying table. Change the data type by clicking theData Typeentry for a row and then select an option from the resulting drop down 2016 David W. GerbingJuly 7, Customer Table3 Data TypeDescriptionTextAlphabetical/numerica l data, up to a maximum of 255 characters, suchas names, addresses, room numbers, zip codes, to 32,000 characters of for arithmetic and ($).AutoNumberAn automatic counter that assigns a number each time data is enteredinto a new data, such as True/False or primary key field has a defaultData TypeofAutoNumber. For the Customer table,the primary key field serves as the Customer ID.

9 For a Database to function successfully,each primary key field must possess specific properties, such as uniqueness. In terms of theCustomer table, no two customers should have the same ID. This uniqueness property isassigned by default to primary key fields, as indicated by theNo Duplicatesterm in theIndexedrow ofField Index FieldsA databaseindexfunctions similarly to the index in the back of a textbook. The databaseindex allows the DBMS to more quickly locate data in an indexed field and to sort andotherwise process that data. However, the index comes at a cost. The index must beconstructed and continually maintained as data is entered and modified.

10 So only fields suchas primary and foreign key fields, and other fields that are queried relatively often, such asLastName fields, are Add Remaining Customer FieldsTo add new fields to the table, just place the cursor in the first blank row in the firstcolumn, theField Namecolumn, and enter the name of the field. The Customer tableshould contain the following fields in addition to the Customer ID: FirstName, LastName,Addr, City, State, and Zip. To add the FirstName field, just enter in the name of the fieldand accept the defaultData TypeofShort 2016 David W. GerbingJuly 7, Customer Table4 The defaultField Sizeof aShort Textfield is 255 charac-ters, a bit large for a first name.