Assignment
Overview: Your assignment is to develop an entry form that will record credit sales into a transaction file (tblOrdertrans) for a simplified Customer Accounts Receivable system. This transaction file will then be used to perform a batch update of a Customer master file (tblCustBalance). You will need to copy the database from the first Project and modify the data entry form you had created. You should create two additional data tables (tblCUSTBALANCE), and (tblPRODUCTS) and add some records (as described below), in addition to (tblORDERTRANS). Note Well: Please refer to the "Overview of Recommended Steps for Completing the SQL Batch Update Process..." description at the very end of this write-up for a brief general overview of the recommended order of the major steps to follow for completing this project.
Remember to include all the basic data validations at the form level and table level that you developed in Project #1. The file structure of the two new tables (with validations and defaults) should be:
New Tables:
tblCUSTBALANCE table [Note: create this table with NINE records (using Custno's 111,222,...,999), make up YOUR OWN unique names, e.g. friends, celebrities, etc.]
Fieldname Datatype Validation rule Default
Custno(primary key) Number Must be 3 digits 111
Company name Text
Balance Currency 0
Sellingprice Currency 1000
tblPRODUCTS table[Note: you should create this table with five records (11,22,...,55) make up your own product names.]
Prodnum(primary key) Number Must be 2 digits 11
ProdName Text
Sellingprice Currency Must be positive 0
Adjustments to Existing Form: You must also make some small changes to the form design that you created in Project 1. Specifically, you should develop a combobox object for Custno that references the tblCUSTBALANCEtable. This replaces the Custno textbox from Project 1. The combobox should show two fields in the dropdown (Custno and CompanyName). You should also develop a combobox object for Prodnum (Showing Prodnum and ProdName) that references the tblPRODUCTS table. Each of these combo boxes should be" limit to list". Finally, you should slightly modify the Form level validation rule for Transdate to ensure the transaction took place in the past week.
Adjustments to Existing Table: Although your transaction table (tblORDERTRANS) remains relatively the same as in project #1, you may want to delete all the records in it (and eventually add new ones) because of the new field and data restrictions described here. You must also ensure that all records added to tblORDERTRANS has a matching customer number (i.e. parent) in tblCUSTBALANCE. You must add a new field (Time_DatePost) to help maintain the audit trail and track when the transaction was actually posted to master file. This field will not have an object on the input form since the user does not directly enter it. After creating the two new tables (i.e. tblCUSTBALANCE and tblPRODUCTS), be sure to establish the proper relationships (i.e. 1:M) between each of the two tables and tblORDERTRANS. Please note that although we are establishing tblPRODUCTS in this database, we are NOT going to update this table through any form in this particular project (we'll do that later in the semester).
The entire table structure for tblOrderTrans is shown below with changes (from the first project) shown in italic bold.
tblORDERTRANStable and form [Be certain this form ONLY allows additions, not edits or deletions.]
Fieldname Datatype FormObject FormValid Default
Transnum Autonumber Textbox
Custno Num Combobox None (Must be in tblCustbalance table)
Transdate Date Textbox <=date() and >=date()-7 (only on form) Date()
Prodnum Num Combobox None (Must be in tblProducts table)
Qty Num Textbox Can not be negative 1
Newcharge Cur Textbox Can not be Negative 0
Newpayment Cur Textbox Can not be negative 0
Posted Num None-not on form 0
Time_Datepost Date/Time None-not on form
Your form should write the new orders (sales) transactions into the tblORDERTRANS table. You should include a command button on the form that will execute an SQL UPDATE command to perform the batch update (refer to class demonstration). Reminder: You must move off the current record (i.e. to the next or previous record) you are entering in order for it to be recorded in the tblORDERTRANS table. This will ensure it will be used in the SQL update.
For Project #2 (for grading purposes), be sure the default Access database messages that display the number of records to be updated are NOT suppressed (as shown and discussed in class), after you click your command button. For example, the first time you click the command button on your form the message may say that 8 records will be updated (assuming you added 8 new records to the table) when running the SQL batch update. If you were to immediately click the button on your form again (without adding any new transactions), you should receive a similar message that says zero records will be updated. For any future projects, (or if you use this technique in your group project at the end of the semester), you might wish to suppress these preliminary messages. However, you should still provide some feedback that the batch process has been accomplished.
Project Reminders and Notes:
1) Be sure to click "Enable Content" on the Security Warning bar that appears when you first open your database; otherwise, your batch update query may not do anything when run.
2) Be sure you have your database automatically compact itself when closing, otherwise it can get big, very fast. To ensure it compacts, select the File tab (upper left of screen), then click the "Options" button (2nd to bottom on left side of screen), click the "Current Database" (2nd item in the left-hand column), ensure the "Compact on Close" checkbox is checked.
3) Ensure you do not have the Form object, Timer property set to "Me.Refresh" to make the time display dynamic. If you keep it set to refresh as in Project #1, your combo boxes will not work well.
4) For this Project, you still need to make up and enter your own numbers for the Customer NewCharge and New Payment fields for each of the transactions you enter. Simply make up something reasonable. Later in the semester you will learn how to automatically calculate the NewCharge by referring to tblProducts and automatically multiplying the Quantity * Selling Price for the Product the Customer selected.
Overview of Recommended Steps for Completing SQL Batch Update Process by Using an Access Action Query:
1) Create and modify table structure of all necessary tables AND add a few reasonable records to each table, including at least two records in tblORDERTRANS that match a single Customer number in tblCUSTBALANCE (e.g. for Customer # 444).
2) Establish relationships between tables.
3) Write SQL Batch Update Code (in WordPad or other text editor)
4) Begin to create a basic Query in Access, then Copy and Paste the SQL code you wrote in WordPad into the SQL View of the Query.
5) Create cmdButton on your Form that will run the Action Query.
6) Complete all other necessary form and database adjustments required by Project description.
*Use the Access file I attached below.
Attachment:- Data-file.rar