Access 2013/Infomation Systems

Directions in uplodaded files.

Please remember that this is a professional business document and should appear as such.

Using Access 2013, demonstrate that components of your application project have the potential to work by creating at least two of the tables described in Part 3. Create a data entry screen (input form) for each of these tables. Use these data entry screens to input at least twenty‐five sample data records for each of these tables.

Using these tables, create queries to prepare the data for two of the reports you described in Part 2. This should be the result of a process that queries both of the above tables, not a simple listing of the table contents. Give each query the same name as the report it will be used to produce. Also create the two reports.

For each table,

· The definition of the table.

o For the definition of a table:

· Include for Tables ‐‐ Properties Relationships

· Include for Fields ‐‐ Names, Data Types, Sizes

· Include for Indexes ‐‐ Names, Fields, Rational

· A minimum of fifteen sample data records for each table.

· A custom input form for each table.

For each report

· The definition of the query used to select the data for the report.

· For the definition of a query:

o Include for the Query ‐‐ Properties ‐ SQL

o Include for the Fields ‐‐ Names, Data Types, Sizes

o The results of the query.

Include a written overview of what additional components such as tables, queries, forms (data / input) and reports (information / output) will be included in your final project.

Application Design

 

 

Tables 

 

Customers 

This table will contain data on each customer that purchases a product from us.  The data will include the customer’s name and complete contact information.  This table will be used in conjunction with the Invoices table to produce the Sales by Customer report.  The query from this table can also be used to filter and organize customers by geographic location. 

Required inputs for this table are:  unique customer ID number, customer name, street address, city, zip code, phone number and email address.  The customer ID will be created by data entry personnel.  The rest of the information will be provided by the customer upon their initial purchase and recorded by the salesperson for later entry. 

 

Invoices 

This critical table will maintain information on each invoiced transaction that occurs, including the invoice number, order details, as well as basic information for the customer and salesperson involved in the transaction.  This table will be used along with the Customers and Salespeople tables to generate both the Sales by Customer and Salesperson Performance reports. 

Required inputs for this table are:  unique invoice number, the unique customer ID number, the appropriate salesperson ID number, the order date and the order total.  All of this information will be taken from the invoice generated by the salesperson during the transaction and entered by data entry. 

 

Products 

This table will contain specific data about each individual product being stocked, including a description of the product, our cost and the selling price.  Gross profit in dollars and by percentage will be calculated from this table for display in the Product Profitability report.  This information will also be used to generate the Inventory report along with the Quarterly Sales information. 

Required inputs for this table are:  unique product ID number, a clear description of the product, the product’s cost and the selling price.  This information will be provided by the buyer when each item is added to stock, and kept up to date to reflect price changes or additions to and removals from inventory. 

 

Quarterly Sales 

This table will contain historical sales data for each product being stocked for the prior year of operation, broken down by quarter.  This table will be used to generate the Inventory report, which shall be used by the buyer to ensure that adequate levels of stock are maintained based on a calculated average of past demand. 

Required inputs for this table are:  product ID number, product description, and total units sold for quarters one through four.  The ID and description will have been previously generated, and total units will be entered using line item details from invoices by data entry personnel. 

 

Salespeople 

This table will contain pertinent data about each member of the sales force to include their full names, date of hire and compensation information.  This table will be used to generate the Salespeople report, as well as in conjunction with the Invoices table to generate the Salesperson Performance report.  This information will be used to determine which salespeople generate the most in sales and keep compensation in line with performance. 

Required inputs for this table are:  a unique salesperson ID number, their first and last name, date of hire and current salary.  This information will be entered by Human Resources for each new hire, and updated accordingly when any changes occur.