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
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:

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
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