The Town Convenience Store
 

Introduction

I was watching "Housesitter" the other day, a wonderful movie with a funny story. A great part of the movie is set in a small town. A certain detail got my attention for a database used by a convenience store. The fact is that the movie is an Americano-American story but the detail that interested me was universal because it applies also to an African village with exact certainty.

In some villages in Africa, which in American is called a town, there is a convenience store where people buy food items and other things that can be easily used. The items include kitchen utilities (paper towels, hand soap, disposable utensils, garbage bags, ice bags, etc), house items (toilet paper, cleaners, dog food, etc), cigarette, etc. Sometimes a person needs something but doesn't have money. He can simply come to the store, gets what he wants, and let the store owner know. The store owner can just shout in a soft and gentle voice, "Should I put it in your account?". As in the Housesitter movie, the customer would just respond, "Oh, Alright", and leave. Because in this village or town everybody knows everybody and certainly the store owner knows everybody by name, almost everybody, at least as reliable as a person can be, has an account. The contrasting aspect of this scenario is that it happens exactly like that in a village in Africa or another country, as well as in a town in America.

To keep track of what everyone owes, the store owner has a way to register the items that the customers take (or borrow, or request, however you want to qualify it). When a customer is ready, she can go to the store any time and pay. This is not like a credit card where you pay a minimum at the end of the month. There are no fees. Only the balance applies.

Payments are made in two main ways. Imagine a customer has taken items for $54.75. One day, the customer comes to the store and pays $22.68. The store owner applies this amount, subtracts it from $54.75. Now the customer owes $54.75 - $22.68 = $32.07. This is considered as the customer's balance. Although the customer still owes money to the store, he can still take other items. The customer may also decide to pay the full amount, which would bring his balance to $0.00.

As nature wants humans to be, there are disputes. For example, a customer may keep taking items, which would make her balance grow so much that at one time she may be overwhelmed. In this case the store owner may tell the customer that she can't take any more items until either she has paid a good part of her balance or she has completely paid what she owes. In the same way, even if the store owner knows a person, he may decide that, for any reason at his discretion, that particular person would not be allowed to "borrow" anything.

We are going to create a database that can help a store owner keep track of the items that  the customers take and their balance.

Practical Learning: Creating the Application
  1. To start, create a blank database named ConvenienceStore1
  2. Like any other store-based business, create a table in Design View with the following columns:
     
    Column Name Data Type Other Properties
    StoreItemID AutoNumber Caption: Item ID
    Primary Key
    ItemTagNbr Text Caption: Item #
    ItemName Text Caption: Item Name
    UnitPrice Currency Caption: Unit Price
  3. Save the table as StoreItems and fill it up with the following sample items
     
    Item ID Item # Item Name Unit Price
    1 87485 Kandy Paper Towel - 2Pack $2.15
    2 24058 Soda Bottle - 2L $1.25
    3 68456 Video K7 - 4Pack $7.88
    4 84674 Solista Condoms - 12Pack $10.95
    5 58475 Molly Toilette Paper - 4Pack $2.55
    6 90752 CD Cleaner Kit $12.95
    7 48538 Gang Magazine $3.95
    8 81484 Washington Post Newspaper $0.28
    9 69002 Soda 12Pack Can $4.50
    10 64857 Universal Remote Control $48.25
    11 75465 Soda 2-Litter $1.45
    12 18554 150-Sheet Notebook $2.15
    13 28365 Rubber Band Transparent Tape $2.65
    14 74556 Multicolor Pencils $1.75
    15 60635 Ball Point Pens $1.85
    16 85476 Turtle Mini Calculator $12.55
    17 85576 Bag O' Ice $2.95
    18 46254 10-Pack Hot Dog $2.75
    19 26855 Sneakers Chocolate Bar $0.55
    20 47795 Jumbo Sneakers Bar $0.95
  4. Close the StoreItems table
  5. When a customer takes an item for the first time, an account is opened for her. Therefore, create a new table in Design View with the following fields:
     
    Column Name Data Type Other Properties
    CustomerID AutoNumber Caption: Customer ID
    Primary Key
    FullName Text Caption: Full Name
    Address Text  
    City Text Default Value: Silver Spring
    State Text Default Value: MD
    ZIPCode Text Caption: ZIP Code
    Default Value: 20904
    Country Text Default Value: USA
    HomePhone Text Caption: Home Phone
    EmailAddress Text Caption: Email Address
    Notes Memo  
  6. Save the table as Customers and fill it up with a few sample records
  7. Close the Customers table
  8. When a customer shops, you can use a table to register what the customer took. Instead of using a notebook that has a piece of paper for each customer, you can create a general table for all customers, using relational database. In this table, you must be able to locate a customer's account, and then fill it up with the selected item. To support this scenario, we will create a junction table that uses a many-to-many relationship. This table will join the customer to the items she takes. This table will also allow us to keep regular track of the customer's activities.
    Start a new table in Design View with the following columns:
     
    Column Name Data Type Other Properties
    CustomerOrderID AutoNumber Caption: Customer Order ID
    Primary Key
    OrderDate Date/Time Caption: Order Date
    Format: Short Date
    Input Mask: 99/99/00
    CustomerID Number Field Size: Long Integer
    Caption: Customer
    Default Value: Delete 0
  9. Save the table as CustomerOrders
  10. Add a new column with the Field Name as Store Item and set its Data Type to Lookup Wizard
  11. In the first page of the wizard, make sure the top radio button is selected and click Next
  12. In the list of Tables, click StoreItems and click Next
  13. Select all items from the list Select All
  14. Click Next and Finish
  15. When asked to save the table, click Yes
  16. Complete the table with the following two fields:
     
    Column Name Data Type Other Properties
    CustomerOrderID AutoNumber Primary Key
    OrderDate Date/Time Format: Short Date
    Input Mask: 99/99/00
    CustomerID Number Field Size: Long Integer
    StoreItemID Number Column Heads: Yes
    Columns Widths: 0";0.55";2";0.85"
    List Width: 3.45"
    Quantity Number Field Size: Integer
    Default Value: 1
    UnitPrice Currency Caption: Unit Price
  17. Save and close the table (no need to enter any record in it)
  18. When a customer performs a payment, the store owner must register the payment. The store owner must also subtract the payment from the customer's current balance. This is done by locating the customer's account.
    Create a new table in Design View with the following columns:
     
    Column Name Data Type Other Properties
    PaymentID AutoNumber Caption: Payment ID
    Primary Key
    CustomerID Number Field Size: Long Integer
    Caption: Customer
    PaymentDate Date/Time Caption: Pmt Date
    Format: Short Date
    Input Mask: 99/99/00
    PaymentAmount Currency Caption: Pmt Amt
  19. Save the table as Payments and close it (no need to enter any record in it)
  20. Using AutoForm, generate a form based on the Customers table and design it as follows:
     
    Convenience Store
     
  21. Save the form as Customers and close it
  22. Start a new form in Design View and save its as sbfCustOrders
  23. Change its Characteristics as follows:
    Record Source: CustomerOrders
    Default View: Continuous Forms
    Navigation Buttons: No
    Dividing Lines: No
  24. Add the Form Header and the Form Footer sections to the form
  25. Add a label to the left side of the Form Header section and set its Caption to Date
  26. From the Field List, add the OrderDate field in the left side of the Detail section and delete its label
  27. Add another label in the Form Header section to the right of the previous label and set its Caption to Item #
  28. From the Field List, drag StoreItemID and drop it in the Detail section. Delete its label
  29. Design the form as follows:
     
  30. Add a new label in the Form Header section and set its Caption to Item Name
  31. From the Toolbox, add a new Text Box in the Detail section. Delete its label and set its Properties as follows:
    Name: txtItemName
    Control Source: =[StoreItemID].[Column](2)
  32. From the Field List, add the Quantity and the UnitPrice fields as follows:
     
     
  33. When an item has been selected in the Item # combo box, you can automatically fill out the Unit Price text box. To do this, generate an AfterUpdate event for the combo box and implement it as follows:
      
    Private Sub StoreItemID_AfterUpdate()
    Me![UnitPrice] = Me![StoreItemID].Column(3)
End Sub
  34. Add a new label to the Form Header section to the right of the others. Set its Caption to Sub-Total
  35. Add a new TextBox control to the Detail section and delete its label
  36. Set its Properties as follows:
    Name: txtSubTotal
    Control Source: =Nz([Quantity])*Nz([UnitPrice])
    Format Currency
  37. Add a new TextBox control to the Form Footer section and delete its label
  38. Set its Properties as follows:
    Name: txtItemsTotal
    Control Source: =Sum(Nz([Quantity])*Nz([UnitPrice]))
    Format Currency
    Visible: No
     
  39. Reduce the txtItemsTotal text box' height completely
     
  40. Save and close the form
  41. Using the Payments table, design a Continuous Form as follows:
     
  42. Save it as sbfPayments
  43. Add a new TextBox control to the Form Footer section and delete its label
  44. Set its Properties as follows:
    Name: txtPayments
    Control Source: =Sum(Nz([PaymentAmount]))
    Format Currency
    Visible: No
  45. Reduce the txtPayments text box' height completely
     
  46. Save and close the sbfPayments sub-form
  47. Using the Customers table, design a new form as follows:
     
  48. Save the form as CustomersOrders
  49. Using the Control Wizard, add both sub-forms you designed as follows:
     
  50. Add the following text boxes in the lower section of the form:
     
      
    TextBox Name: txtTotalPayments
    Control Source: =Nz([sbfPayments].[Form]![txtPayments])
    Format: Currency
    TextBox Name: txtTotalOrders
    Control Source: =Nz([sbfCustOrders].[Form]![txtItemsTotal])
    Format: Currency
    TextBox Name: txtCurrentBalance
    Control Source: =Nz([sbfCustOrders].[Form]![txtItemsTotal]) - Nz([sbfPayments].[Form]![txtPayments])
    Format: Currency
  51. Save the form and switch it to Form View
     
  52. Test it by entering a few records
     
     
  53. Use the Command Button Wizard to add a Close button
  54. Close the application
 
Home Copyright © 2004 FunctionX, Inc.