Revenue

							             Revenue Cycle
• Receive order from customer
  – Update sales order records
• Pick items to fill order
  – Update inventory records
• Ship goods to customer
  – Update accounts receivable records
• Bill customer and collect cash
  – Update accounts receivable and cash records
        Entities and attributes
• Entities
  – An object, event or agent about which data are
    collected
• Relationship
  – An association between entities
• Attributes
  – An item of data that characterizes an entity or
    relationship
   Entity-Relationship (E-R)
            Models
Relationships describe the way in which the entities in the
database are logically linked with each other.

The relationships map and define the way in which the
data can be extracted from the database in the future
       What are the entities in Pipefitters?

•   Customer
•   Sales Order
•   Sale
•   Inventory
•   Cash Receipts
    1   N       M

    1
1           1       N


            1       M
N               N
        N
   Tables for the Revenue Cycle
• One table for each of the entities (5)
• One table for each of the N to M
  relationships
• No tables for 1-M or 1-1 relationships.
• Open tblCustomer in the Chp6.mdb and
  identify the attributes of the customer entity.
         tblCustomer attributes
•   Customer Number
•   Name
•   Address
•   City
•   State
•   Zip Code
•   Telephone
•   Credit limit
•   Primary Contact
Create a simple form to enter data using AutoForm

Make sure you are on the Database screen and have highlighted
the table tblCustomer

Click on Insert then on AutoForm

Now you have a perfectly usable form for entering data into the
Customer Table.

.
    Add a control to prevent changing “credit limit”

• Put the cursor on the “credit limit” cell
• Right click then click on Properties
• Click on Data
• Click on Enabled and change it to No
• Click on Locked and change to Yes
• Click on Format and click on Back Color
• Click on the Build button on the right and select a color
• Close the Properties window and try changing the credit
  limit on the AutoForm
• Close the form and change its name to frmCustomer
    1   N       M

    1
1           1       N


            1       M
N               N
        N
              tblInventory
•   Item Number
•   Composition
•   Type
•   Diameter
        Create an form using AutoForm

•   Click on Forms
•   Click on New
•   Choose tblInventory in the window
•   Click on AutoForm: Tabular
•   Click on File, Save as: frmInventory
              tblSalesOrder

• Sales Order number (primary key)
• Date
• Customer Number (foreign key from
  tblCustomer)
• Customer PO Number
            tblSalesOrderInventory

•   Primary Key of tblSalesOrder
•   Primary Key of tblInventory
•   Quantity
•   Price

What is the primary key of this table.
                 Creating the relationships
                     tblSalesOrder

                     SalesOrderNumber
tblCustomer          CustomerNumber       tblSalesOrder-
                                          Inventor
CustomerNumber
                                          SalesOrderNumber
                                          InventoryItemCode
                       tblInventory


                     InventoryItemCode
                      Steps

• Click on Tools
• Click on Relationships
• Add tblCustomer, tblSalesOrders,tblInventory and
  tblSalesOrders-Inventory
• Click on the related key, hold down the mouse
  button and drag the attribute name from one table
  to the other
  Create a sales order entry form

Create a sales order entry form using the knowledge you
got from doing the Nurse Pat form. Use the Wizard and the
four tables you have now built, including the relationship.
        Creating an Invoice(Report)
What information appears on an invoice?
• Invoice number
• Customer name, address
   – Shipping address it different from order address
• Sales order number
• Shipping date
• Items information
    –   Item number
    –   Quantity
    –   Description
    –   Unit Price
    –   Total Price
    –   Total Due
                    tblSalesOrder      tblSalesOrder-Inventory
                                       SalesOrderNumber
                    CustomerNumber
                                       InventoryItemCode
                    SalesOrderNumber


tblCustomer

CustomerNumber                         tblInventory

                                       InventoryItemCode
                 tblSales


                 InvoiceNumber
                 tblSalesOrder      tblSalesOrder-Inventory
                                    SalesOrderNumber
                 CustomerNumber
                                    InventoryItemCode
                 SalesOrderNumber


tblCustomer

CustomerNumber                      tblInventory

                                    InventoryItemCode




                 tblSales

                 CustomerNumber
                 InvoiceNumber
                 tblSalesOrder      tblSalesOrder-Inventory
                                    SalesOrderNumber
                 CustomerNumber
                                    InventoryItemCode
                 SalesOrderNumber


tblCustomer

CustomerNumber                      tblInventory

                                    InventoryItemCode




                 tblSales           tblSales-Inventory
                                     InvoiceNumber
                 CustomerNumber      InventoryItemCode
                 InvoiceNumber
    Creating a Sales Entry Form
• Copy frmSalesOrder
• Open the Design view
• Click Record Source then Build
• Select Query Show Table menu command double click on
  tblSales and Delete
• Drag InvoiceNumber to cell 1 make ascending
• Repeat with InvoiceDate SalesOrderNumber
• Change the Subform to tblSales-Inventory
            Building the invoice
• Invoice will be built from a query
• Go to page 318 in the textbook
• Note that the query creates a dynaset. This will appear
  when you RUN the query. The dynaset looks like a table
  but is only temporary.
• When the Invoice is created using the rptInvoice, the query
  is run but then the dynaset is closed when the report is
  completed.
           Making the rptInvoice
•   In the Database window, click on Reports, then on New
•   In the T/Q window select qryInvoice
•   Click on >> to move all of the attributes to the report
•   Click on next and accept the default by tblSales
•   We are not going to add any additional grouping so click
    next
•   Select InventoryItemCode to sort the fields by
•   Click Summary options and click under Sum on Extension
•   Choose a Layout option click Next and choose a Style
•   Save as rptInvoice and click finish