Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

IDS/ACTG 475 – FINAL PROJECT

FALL SEMESTER 2023

BACKGROUND

Julian Tan founded his floral design business, Julian Tan Designs (JTD), Inc. in Chicago, IL.  His designs    have become very popular in the greater Chicago area, where he executes intricate flower designs for large events, like weddings and funerals.  He started using a small-scale accounting system and a part- time assistant, but he needs a more robust system.

Since the company is growing fast, Julian has retained you to replace JTD’s small accounting system with a bigger database system.  The new system will be built and implemented piecemeal, one cycle at a time, beginning with JTD’s expenditure cycle, which will track purchases, inventory, inventory receipts, accounts payable and cash payments.  The initial feasibility study and user needs have already been analyzed and the logical design work for the expenditure cycle is complete.  You are provided with a narrative and an ERD of the new system.

ASSIGNMENT

1.    Convert the ERD into a database schema and make sure it is normalized.  Do a strict conversion.

2.    Build the SSI, Inc. expenditure cycle database in Access using the procedure and specifications on the next pages.

PROCEDURE  FOR  BUILDING AN ACCESS  DATABASE APPLICATION

1.    Convert the ERD into database tables and build these tables in Access.  Use the table and field names of the common schema for the established tables and fields.  You may add additional tables and fields as needed, as you further develop your database.

2.    Create the relationships between the tables (primary key to foreign key links) and enforce referential integrity for these relationships.  Make sure your tables, data types, metadata and joins are CORRECT before going on to the next step!

3.    Create forms for the maintenance of all resource and agent entities.

4.    Use the maintenance forms to populate the resource and agent tables.

5.    Create forms for the event entities (use the form wizard for forms with subforms). 6.    Create the reports (start with easiest forms, save hardest for last).

7.    Create a navigation form that opens automatically upon startup that provides a user interface with navigation buttons to all functions in the application.

8.    Enter event transactions, process them completely, and test all conditions and scenarios to make sure that the system can handle them.  Make any necessary modifications and add any necessary functionality to make your system work.

SPECIFICATIONS  FOR THE  FINAL  PROJECT

All objects in your database should be properly named using continuous character strings. i.e. Don’t include spaces in the names

TABLES

.    The metadata of table must include:

o Use data type Number for all primary keys and foreign keys in this project.  Set the field size to Long integer.

(Do not use the Autonumber data type; assignments that do not use the proper data type will not be accepted.)

o Metadata for the primary keys of N:N tables should set as follows:

Required: Yes

Indexed: Yes, Duplicates OK

o Input masks for all phone numbers and dates.

o Validation rules and validation text for ALL EVENT ENTITY TRANSACTION DATES.  The rule should allow the entry of only the current date or earlier, but not future dates.  If the date entered is ahead of date of entry, the error message “ Please enter today’s date or earlier” should appear.

o Use the Table and Field names from the Final ERD distributed by your instructor. You may add any other tables and fields that you need and name them what you want.

FORMS

.     Forms should be well designed and in good form.  Although autoforms can be used to begin the process of creating forms, no autoforms should be in the final database.  Autoforms or forms    created by form wizard should be altered to improve fonts, layout, and readability.

.     Fields containing reference data should be protected and the appearance changed in the manner described in Perry Chapter 8 and as demonstrated in lab

.     Forms should include the following internal controls:

o Combo boxes must be used on all ALL EVENT FORMS for ALL FOREIGN KEY input fields  that connect to AGENT & RESOURCE entities.  Combo boxes are typically created when  there is a limited and known list of proper values, including state abbreviations, vendor numbers, employee numbers, and inventory numbers.  Size and format the drop-down lists and their columns properly.

o Dollar totals, where appropriate, such as on the Purchase Order form, Inventory Receipts form, and the Cash Payments form.

o Closed loop verification must be implemented on ALL EVENT FORMS for all vendor numbers, employee numbers, and inventory numbers.

o Navigation buttons can be created using the button wizard in the toolbox.  Buttons required on each form are First Record, Previous record, Next Record, Last Record, Save Record, New Record and Close Form.

o Alternate Form Versions for Approvals – Create alternate versions of the purchase order form, inventory receipts form, and cash payments form. These versions allow Julian Tan to access to approval field, whereas the users of the regular form versions can only view the approval field.  Access to the alternate form versions will be available from the navigation form.  Normally this access would be password protected, but we   will not implement that control.

o The navigation form should open automatically upon startup.  The navigation form should be easy to use and logically organized.  The navigation form(s) should allow access to all transaction processing and report generation.  Your navigation form(s) are the “front end” of your system and it should have the following buttons:

.     Enter Purchase Orders

.     Approve Purchase Orders

.     Enter Inventory Receipts

.     Approve Inventory Receipts for Payment

.     Enter Cash Payments

.     View/Print Purchase Order Document

.     View/Print Receiving Report Document

.     View/Print Monthly G/L Journal Entry Report

.     View/Print Monthly Receipts by Receiving Clerk Report

.     View/Print A/P Subsidiary Ledger Report

.     Open Vendor Maintenance Form

.     Open Inventory Maintenance Form

.     Open Employee Maintenance Form

.     Open Cash Account Maintenance Form

REPORTS

.     Reports should be well designed and in good form.  Although autoreports can be used to begin the process of creating reports, no autoreports should be in the final database.  Autoreports or reports created by report wizard should be altered to improve fonts, layout, and readability.

.     Reports that are business documents should be properly paginated, one document to a page.

.    The following reports are required:

o Purchase Order document (example attached)

o Receiving Report document (example attached)

o Monthly General Ledger Journal Entry Report (example attached – use a parameter query so the user will be able to generate this report only for transactions in a given  month)

o Monthly Receipts by Receiving Clerk Report (example attached – use a parameter   query so the user will be able to generate this report only for transactions in a given month)

o Accounts Payable Subsidiary Ledger Report (example attached)