POS/410 – SQL FOR BUSINESS

1. POS/410 – SQL FOR BUSINESS Instructor: Steve Westlund 6/9/2009 – 7/13/2009Team D: Robert York, Arthur Hameister, Joe Brown, Robert Kepford, Greg Larralde

2. SR-kf-009, “SQL Accounting Database.” • Organization, Location and Requestor • Description of Request • Background of Request • Expected Results/Impact when Completed

3. Project Plan • Review Chart of Accounts • Make Modifications • Design an SQL Database • Ensure Flexibility of Reporting

4. Chart of Accounts • Create SQL database Chart of Accounts report using a SQL SELECT statement with a COMPUTE clause to program the control break for the intermediate and final totals. • Test Chart of Accounts report.

5. Task List • Analyze current chart of accounts spreadsheet. Modify if needed. • Design an SQL database containing fields in the Kudler Foods Chart of Accounts (Account, Description, Short Description & add balance field). • Create a query that will display all of the database fields. • Add test data to the database. • Run a report totaling the balance field using test data.

6. Query • Create an Account Number Query. • Test the query. • Fine tune query until it is perfect.

7. Query Continued The first part of the query creates the database, switches to that database and creates a chart of accounts table.

8. Query Continued The second part of the query inserts rows into the table with data.

9. Query Continued The third part of the query continues to populate data and then selects all rows from the Chart of Accounts table.

10. Query Results First Chart Second Chart

11. Report Query The next query uses the SUM and LEFT functions to pull data by the first two numbers of the Account ID and show subtotal and grand total amounts.

12. Query Results