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

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

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.

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:
     
    
18. Save and close the Customers form
     
    
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
     
    
25. Save and close the NewCustomer form

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.

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

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.

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:
     
    
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
     
    
20. Save and close the NewCleaningItem form

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.

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

    Control Name Caption Control Sourcce Format Label   Customer Phone #:     Text Box CustomerPhone   CustomerPhone   Label   Customer Name:     Text Box CustomerName   =IIf(IsNull([CustomerPhone]), "", DLookUp("CustomerName", "Customers", "PhoneNumber = '" & [CustomerPhone] & "'"))   Label   Date Deposited:     Text Box DateDeposited   DateDeposited   Label   Time Deposited:     Text Box TimeDeposited   TimeDeposited   Label   Date Expected:     Text Box DateExpected   DateExpected   Label   Time Expected:     Text Box TimeExpected   TimeExpected   Line         Label   Item Type     Label   Unit Price     Label   Qty     Label   Sub-Total     Label   Cleaning Order Summary     Label   Shirts     Text Box UnitPriceShirts   UnitPriceShirts   Text Box QuantityShirts   QuantityShirts   Text Box txtSubTotalShirts   =IIf(IsNull([QuantityShirts]), "", Nz([UnitPriceShirts]) * Nz([QuantityShirts])) Fixed Label   Receipt #:     Text Box ReceiptNumber   ReceiptNumber   Label   Pants     Text Box UnitPricePants   UnitPricePants   Text Box QuantityPants   QuantityPants   Text Box txtSubTotalPants   =IIf(IsNull([QuantityItem1]), "", Nz([UnitPricePants]) * Nz([QuantityPants])) Fixed Combo Box Item1   Item1   Text Box UnitPriceItem1   UnitPriceItem1   Text Box QuantityItem1   QuantityItem1   Text Box txtSubTotalItem1   =IIf(IsNull([QuantityItem1]), "", Nz([UnitPriceItem1]) * Nz([QuantityItem1])) Fixed Label   Cleaning Total:     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 Item2   Item2   Text Box UnitPriceItem2   UnitPriceItem2   Text Box QuantityItem2   QuantityItem2   Text Box txtSubTotalItem2   =IIf(IsNull([QuantityItem2]), "", Nz([UnitPriceItem2]) * Nz([QuantityItem2])) Fixed Label   Tax Rate:     Text Box TaxRate   TaxRate   Combo Box Item3   Item3   Text Box UnitPriceItem3   UnitPriceItem3   Text Box QuantityItem3   QuantityItem3   Text Box txtSubTotalItem3   =IIf(IsNull([QuantityItem3]), "", Nz([txtUnitPriceItem3]) * Nz([QuantityItem3])) Fixed Label   Tax Amount:     Text Box txtTaxAmount   =CLng(CDbl(Nz([txtCleaningTotal])) * CDbl(Nz([TaxRate])) * 100) / 100 Fixed Combo Box Item4   Item4   Text Box UnitPriceItem4   UnitPriceItem4   Text Box QuantityItem4   QuantityItem4   Text Box txtSubTotalItem4   =IIf(IsNull([QuantityItem4]), "", Nz([UnitPriceItem4]) * Nz([QuantityItem4])) Fixed Label   Net Price:     Text Box txtNetPrice   =Nz([txtCleaningTotal]) + Nz([txtTaxAmount]) Fixed Line         Label   Order Status:     Combo Box OrderStatus   OrderStatus   Label   Date Picked:     Text Box DatePickedUp   DatePickedUp   Label   Time Picked:     Text Box TimePickedUp   TimePickedUp   Label   Notes:     Text Box txtNotes       Button cmdClose Close

54. Preview the form
     
    

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
     
    

62. Save and close the NewCleaningItem form