Home

Example Application: Georgetown Dry Cleaning Services

   

Introduction

Georgetown Dry Cleaning Services is a fictitious business that takes care of cleaning all types of items for customers. Customers bring their items to the store. Depending on the time they leave them, they can get them back the same day or the day after.

We will create a (very) simple application to give us an idea of how to manage such a business.

Practical Learning: Introducing GDCS

  1. Start Microsoft Access
  2. To create a new database, click File Name and type Georgetown Dry Cleaning Services
  3. Click Create
  4. Close the default table without saving it

The Customers

A cleaning order starts with a customer bringing some clothes for cleaning. To prepare and deliver cleaning orders, we will create a table for customers.

Practical Learning: Creating Customers

  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Click the empty box under Field Name and create the following fields:
     
    Field Name Data Type Field Size Caption
    CustomerID AutoNumber   Customer ID
    PhoneNumber Text 30 Phone #
    FirstName Text 25 First Name
    LastName Text 25 Last Name
  3. Right-click CustomerID and click Primary Key
  4. Right-click the Table1 tab and click Save
  5. Set the Table Name to Customers
  6. Click OK
  7. Click the first empty box under Field Name and type CustomerName
  8. Set its Data Type to Calculated
  9. In the Expression Builder, type LastName + ', ' + FirstName
  10. Click OK
  11. Click the first empty box under Field Name and type Notes
  12. Set its Data Type to Memo
     
    Field Name Data Type Field Size Caption
    PhoneNumber Text 20 Phone #
    FirstName Text 25 First Name
    LastName Text 25 Last Name
    CustomerName Text   Customer Name
    Notes Memo    
  13. Save and close the table
  14. On the Ribbon, click Create and, in the Forms section, click Form Design
  15. Using the Properties window, set its Record Source to Customers
  16. Save the form as Customers
  17. Complete the design as follows:
     
    Georgetown Dry Cleaning Services: Customers
  18. Save and close the Customers form
     
    Georgetown Dry Cleaning Services: Customers
  19. In the Navigation Pane, right-click the Customers form and click Copy
  20. Right-click any area of the Navigation Pane and click Paste
  21. Set the name to NewCustomer
  22. Click OK
  23. In the Navigation Pane, right-click NewCustomer and click Design View
  24. Using the Properties window, change the following characteristics:
    Navigation Buttons: No
    Data Entry: Yes
     
    Georgetown Dry Cleaning Services: New Customer
  25. Save and close the NewCustomer form

The Current Status of an Order

After a customer has droppped some clothes, employees start processing them. At one time, the items will have been cleaned and wait for the customer. At another time, the customer will come to pick the clothes up. This means that, throughout this process, the status of a cleaning order changes. To keep track of these, we will create a small table.

Practical Learning: Creating Order Status

  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Click the empty box under Field Name and create the following fields:
     
    Field Name Data Type Field Size Caption
    OrderStatus Text 40 Order Status
    Notes Memo    
  3. Right-click OrderStatus and click Primary Key
  4. Right-click Table1 and click Datasheet View
  5. When asked to save the table, click Yes
  6. Set the Name to OrdersStatus
  7. Click OK

The Cleaning Items

Our company cleans various types of items. We could provide empty text boxes in a form and let the clerk type the name of an item that is being cleaned but this solution would require too much typing. Another option is to provide a fixed list of items. The problem with this solution is that the list of items may increase. The most logical option is to create a table of items so that, when necessary, a new item can be added to the list.

Practical Learning: Creating Cleaning Items

  1. On the Ribbon, click Create and, in the Tables section, click Table Design
  2. Click the empty box under Field Name and create the following fields:
     
    Field Name Data Type Field Size Format Caption
    ItemID AutoNumber     Item ID
    ItemName Text 40   Item Name
    DefaultPrice Number Double Fixed Default Price
  3. Right-click ItemID and click Primary Key
  4. Right-click Table1 and click Datasheet View
  5. When asked whether you want to save the table, click Yes
  6. Set the Name to Items
  7. Click OK
  8. Close the table
  9. On the Ribbon, click Create and, in the Forms section, click Form Design
  10. Using the Properties window, set its Record Source to Items
  11. Save the form as CleaningItems
  12. Complete the design as follows:
     
    Georgetown Dry Cleaning Services: Cleaning Items
  13. Save and close the CleaningItems form
  14. In the Navigation Pane, right-click the CleaningItems form and click Copy
  15. Right-click any area of the Navigation Pane and click Paste
  16. Set the name to NewCleaningItem
  17. Click OK
  18. In the Navigation Pane, right-click NewCleaningItem and click Design View
  19. Using the Properties window, change the following properties:
    Navigation Buttons: No
    Data Entry: Yes
     
    Georgetown Dry Cleaning Services: New Cleaning Item
  20. Save and close the NewCleaningItem form

Cleaning Orders

A cleaning order is the central activity of our database. It will keep its information as

  • A (unique) receipt number
  • A way to identity the customer whose cleaning will be processed or has been done
  • Date and time information about the order such as when the customer brought the items to be cleaned, the date/time the items are/were expected
  • The item or list of items to clean

We also need to keep a status of each cleaning order to specify if the items have been dropped, are being cleaning, or have been picked up.

Practical Learning: Creating Cleaning Orders

  1. On the Ribbon, click Create and, in the Queries section, click Query Design
  2. On the Show Table dialog box, click Close
  3. Right-click the Query1 window and click SQL View
  4. Replace the code in the Query1 window with the following:
    CREATE TABLE CleaningOrders
    (
        ReceiptNumber Counter(100001, 1),
        Constraint PK_CleaningOrders Primary Key(ReceiptNumber)
    );
  5. To execute the code, on the Ribbon, click the Run button Run
  6. Close the Query1 window
  7. When asked whether you want to save, click No
  8. In the Navigation Pane, right-click CleaningOrders and click Design View
  9. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption Default Value
    ReceiptNumber AutoNumber     Receipt #  
    CustomerPhone   20   Customer Phone  
    DateDeposited Date/Time   Long Date Date Deposited =Date()
    TimeDeposited Date/Time   Medium Time Time Deposited =Time()
    DateExpected Date/Time   Long Date Date Expected  
    TimeExpected Date/Time   Medium Time Time Expected  
    UnitPriceShirts Number Double Fixed Unit Price Shirts 1.25
    QuantityShirts Number Integer   Quantity Shirts  
    UnitPricePants Number Double Fixed Unit Price Pants 1.95
    QuantityPants Number Integer   Quantity Pants  
    Item1          
    UnitPriceItem1 Number Double Fixed    
    QuantityItem1 Number Integer      
  10. In the top section of the table, set the Data Type of the Item1 field to Lookup Wizard...
  11. In the first page of the wizard, make sure the first radio button is selected and click Next
  12. In the second page of the wizard, click Table: Items
  13. Click Next
  14. In the Available Fields list, double-click ItemName and DefaultPrice
  15. Click Next
  16. Click Next
  17. Click Next
  18. Accept the column label as Item1 and click Finish
  19. Click Yes to save the table
  20. Click the box on the left side of Item1
  21. Press and hold Shift
  22. Click the box on the left side of QuantityItem1
  23. Press Ctrl + C to copy
  24. Click the first empty box under QuantityItem1
  25. Press Ctrl + V to paste
  26. Change the field names to Item2, UnitPriceItem2, and QuantityItem2 respectively
  27. Click the first empty box under QuantityItem2
  28. Press Ctrl + V to paste
  29. Change the new field names to Item3, UnitPriceItem3, and QuantityItem3 respectively
  30. Click the first empty box under QuantityItem3
  31. Press Ctrl + V to paste
  32. Change the new field names to Item4, UnitPriceItem4, and QuantityItem4 respectively
  33. Complete the table with the following fields:
     
    Field Name Data Type Field Size Format Caption Default Value
    ReceiptNumber AutoNumber     Receipt #  
    CustomerPhone          
    CustomerName          
    DateDeposited          
    TimeDeposited          
    DateExpected          
    TimeExpected          
    UnitPriceShirts          
    QuantityShirts          
    UnitPricePants          
    QuantityPants          
    Item1          
    UnitPriceItem1          
    QuantityItem1          
    Item2          
    UnitPriceItem2          
    QuantityItem2          
    Item3          
    UnitPriceItem3          
    QuantityItem3          
    Item4          
    UnitPriceItem4          
    QuantityItem4          
    OrderStatus          
    TaxRate Number Double Percent Tax Rate 0.0750
    DatePickedUp Date/Time   Long Date Date Picked Up  
    TimePickedUp Date/Time   Medium Time Time Picked Up  
    Notes Memo        
  34. In the top section of the table, set the Data Type of the OrderStatus field to Lookup Wizard...
  35. In the first page of the wizard, make sure the first radio button is selected and click Next
  36. In the second page of the wizard, click Table: OrderStatus
  37. Click Next
  38. In the Available Fields list, double-click OrderStatus
  39. Click Next
  40. Click Next
  41. Click Next
  42. Accept the label as OrderStatus and click Finish
  43. Click Yes to save the table
  44. Close the table
  45. On the Ribbon, click Create
  46. In the Forms section, click Form Design
  47. Right-click the form and click Form Header/Footer
  48. Right-click the form's tab and click Save
  49. Set the name to CleaningOrders and press Enter
  50. Double-click the button at the intersection of rullers
  51. In the Property Sheet, click All and change the following characteristics:
    Record Source: CleaningOrders
    Caption: Georgetown Dry Cleaning Services - Cleaning Orders
    Dividing Lines: Yes
  52. In the Tools section, click Add Existing Fields
  53. Design the form as follows:
     
    Georgetown Dry Cleaning Services
    Control Name Caption Control Sourcce Format
    Label Label   Customer Phone #:    
    Text Box Text Box CustomerPhone   CustomerPhone  
    Label Label   Customer Name:    
    Text Box Text Box CustomerName   =IIf(IsNull([CustomerPhone]), "", DLookUp("CustomerName", "Customers", "PhoneNumber = '" & [CustomerPhone] & "'"))  
    Label Label   Date Deposited:    
    Text Box Text Box DateDeposited   DateDeposited  
    Label Label   Time Deposited:    
    Text Box Text Box TimeDeposited   TimeDeposited  
    Label Label   Date Expected:    
    Text Box Text Box DateExpected   DateExpected  
    Label Label   Time Expected:    
    Text Box Text Box TimeExpected   TimeExpected  
    Line Line        
    Label Label   Item Type    
    Label Label   Unit Price    
    Label Label   Qty    
    Label Label   Sub-Total    
    Label Label   Cleaning Order Summary    
    Label Label   Shirts    
    Text Box Text Box UnitPriceShirts   UnitPriceShirts  
    Text Box Text Box QuantityShirts   QuantityShirts  
    Text Box Text Box txtSubTotalShirts   =IIf(IsNull([QuantityShirts]), "", Nz([UnitPriceShirts]) * Nz([QuantityShirts])) Fixed
    Label Label   Receipt #:    
    Text Box Text Box ReceiptNumber   ReceiptNumber  
    Label Label   Pants    
    Text Box Text Box UnitPricePants   UnitPricePants  
    Text Box Text Box QuantityPants   QuantityPants  
    Text Box Text Box txtSubTotalPants   =IIf(IsNull([QuantityItem1]), "", Nz([UnitPricePants]) * Nz([QuantityPants])) Fixed
    Combo Box Combo Box Item1   Item1  
    Text Box Text Box UnitPriceItem1   UnitPriceItem1  
    Text Box Text Box QuantityItem1   QuantityItem1  
    Text Box Text Box txtSubTotalItem1   =IIf(IsNull([QuantityItem1]), "", Nz([UnitPriceItem1]) * Nz([QuantityItem1])) Fixed
    Label Label   Cleaning Total:    
    Text Box Text Box txtCleaningTotal   =IIf(IsNull([QuantityShirts]), 0, Nz([txtSubTotalShirts])) + IIf(IsNull([QuantityPants]), 0, Nz([txtSubTotalPants]))+IIf(IsNull([QuantityItem1]), 0, Nz([txtSubTotalItem1])) + IIf(IsNull([QuantityItem2]), 0, Nz([txtSubTotalItem2]))+IIf(IsNull([QuantityItem3]), 0, Nz([txtSubTotalItem3])) + IIf(IsNull([QuantityItem4]), 0, Nz([txtSubTotalItem4])) Fixed
    Combo Box Combo Box Item2   Item2  
    Text Box Text Box UnitPriceItem2   UnitPriceItem2  
    Text Box Text Box QuantityItem2   QuantityItem2  
    Text Box Text Box txtSubTotalItem2   =IIf(IsNull([QuantityItem2]), "", Nz([UnitPriceItem2]) * Nz([QuantityItem2])) Fixed
    Label Label   Tax Rate:    
    Text Box Text Box TaxRate   TaxRate  
    Combo Box Combo Box Item3   Item3  
    Text Box Text Box UnitPriceItem3   UnitPriceItem3  
    Text Box Text Box QuantityItem3   QuantityItem3  
    Text Box Text Box txtSubTotalItem3   =IIf(IsNull([QuantityItem3]), "", Nz([txtUnitPriceItem3]) * Nz([QuantityItem3])) Fixed
    Label Label   Tax Amount:    
    Text Box Text Box txtTaxAmount   =CLng(CDbl(Nz([txtCleaningTotal])) * CDbl(Nz([TaxRate])) * 100) / 100 Fixed
    Combo Box Combo Box Item4   Item4  
    Text Box Text Box UnitPriceItem4   UnitPriceItem4  
    Text Box Text Box QuantityItem4   QuantityItem4  
    Text Box Text Box txtSubTotalItem4   =IIf(IsNull([QuantityItem4]), "", Nz([UnitPriceItem4]) * Nz([QuantityItem4])) Fixed
    Label Label   Net Price:    
    Text Box Text Box txtNetPrice   =Nz([txtCleaningTotal]) + Nz([txtTaxAmount]) Fixed
    Line Line        
    Label Label   Order Status:    
    Combo Box Text Box OrderStatus   OrderStatus  
    Label Label   Date Picked:    
    Text Box Text Box DatePickedUp   DatePickedUp  
    Label Label   Time Picked:    
    Text Box Text Box TimePickedUp   TimePickedUp  
    Label Label   Notes:    
    Text Box Text Box txtNotes      
    Button Button cmdClose Close    
  54. Preview the form
     
    Georgetown Dry Cleaning Services - Cleaning Orders
  55. Save and close the form
  56. In the Navigation Pane, right-click the CleaningOrders form and click Copy
  57. Right-click any area of the Navigation Pane and click Paste
  58. Set the name to NewCleaningOrder
  59. Click OK
  60. In the Navigation Pane, right-click NewCleaningOrder and click Design View
  61. Using the Properties window, change the following properties:
    Navigation Buttons: No
    Data Entry: Yes
     
    Georgetown Dry Cleaning Services - New Cleaning Order
  62. Save and close the NewCleaningItem form
 
 
 

Application Simulation

A simulation allows us to test the tables and form with possible or actual values.

Practical Learning: Simulating the Application

  1. In the Navigation Pane, double-click the OrdersStatus table
  2. Create the following records:
     
    Order Status Notes
    Processing Cleaning Order The order is being processed and the items are not yet ready
    Ready For Pick Up The items have been cleaned and the customer can pick them up
    Processing (Problem With Order) This can signal any problem, such as a customer complaining about something
    Finalized (Picked Up) The items have been cleaned and the customer has picked them up
  3. Close the table
  4. In the Navigation Pane, double-click the NewCleaningItem form
  5. Create the following items (most dry cleaning store use the same price for shirts and another price for other types of items; they also have a third different type for comforters; just for fun, we are using different orices in our application):
     
    Item Name Unit Price
    Tie 1.25
    Coat 3.25
    Dress 2.45
    Jacket 3.15
    Swede 6.5
    Sweater 2.3
    Silk Shirt 2.25
    Comforter 19.95
    Women Suit 8.95
    Regular Skirt 2.35
    Men's Suit 2Pc 8.5
    Men's Suit 3Pc 9.95
    Skirt With Hook 2.4
  6. Close the NewCleaningItem form
  7. In the Navigation Pane, double the NewCustomer form
     
    Georgetown Dry Cleaning Services: New Customer
  8. Create the following records:
     
    First Name Last Name Phone Number
    James Newfield 240-210-2844
    Ahmed Adama 202-286-1117
    Catherine Lopez 301-840-0025
    Elliott Wuah 202-290-3740
    Annette Belsam 301-927-9475
    Edith Nwabugo 202-692-4079
  9. Close the NewCustomer form
  10. In the Navigation Pane, double-click the NewCleaningOrder form
     
    Georgetown Dry Cleaning Services - New Cleaning Order
  11. Create a record as follows:
    Georgetown Dry Cleaning Services - New Cleaning Order
  12. Create another record as follows:
    Georgetown Dry Cleaning Services
  13. Create another record as follows:
    Georgetown Dry Cleaning Services
  14. Close the NewCleaningOrder form
 
 
   
 

Home Copyright © 2010-2012 FunctionX Home