We will create a database that can assist the company to manage its business.

1. Start Microsoft Access
2. In the File Name text box, replace the name with FunDS (DS stands for department store)
3. Click Create
4. On the Ribbon, click File -> Options
5. On the left list, click Current Database
6. Click Overlapping Windows and Compact On Close
7. Click OK
8. Click OK
9. Close Microsoft Access and re-open it with the Fun Department Store database

Employees and cashiers are staff members who create the inventory, process customers purchase orders, and perform other management tasks. We will create a simple table that holds employees information. To keep the database simple, we will use as little information as possible.

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 text with the following:

   CREATE TABLE Employees
   (
       EmployeeID Counter(1, 1) Not Null,
       EmployeeNumber Text(20),
       FirstName Text(25),
       LastName Text(25),
       FullName Text(50),
       Title Text(50),
       Notes Note,
       Constraint PK_Employees Primary Key(EmployeeID)
   );

5. To execute the code, on the Ribbon, click the Run button
6. On the Ribbon, click Create and, in the Forms section, click Form Design
7. Using the Properties window, set the following characteristics:
   Record Source: Employees
   Auto Center: Yes
8. Save the form as Employees
9. Design it as follows:
    
   
10. Save the Employees form
11. Switch it to Form View
     
    
12. Create a few records as follows:
     

    Empl #	First Name	Last Name	Full Name	Title
    60958	Simon	Sielaff	Sielaff, Simon	General Manager
    20858	Becky	Crone	Crone, Becky	Head Cashier
    40295	Catherine	Rosenstock	Rosenstock, Catherine	Cashier
    80284	Bernadette	Wrights	Wrights, Bernadette	Cashier
    27046	Betty	Lorre	Lorre, Betty	Intern
    60960	Lisa	Chicone	Chicone, Lisa	Cashier
    39486	Daniel	Drewise	Drewise, Daniel	Shift Manager
    93842	Steve	Goetsch	Goetsch, Steve	Cashier

13. Close the form

The clothes that FunDS sells are divided in some categories for easy inventory. The most common categories include women, men, girls, and boys. Of course, we will make it possible to add new categories.

1. Replace the text in the Query1 window with the following:

   CREATE TABLE Categories
   (
       Category Text(40) Not Null,
       Constraint PK_Categories Primary Key(Category)
   );

2. To execute the code, on the Ribbon, click the Run button
3. On the Ribbon, click Create and, in the Forms section, click Form Design
4. Using the Properties window, set the following properties:
   Record Source: Categories
   Auto Center: Yes
5. Save the form as Categories
6. Design it as follows:
    
   
7. Save the Categories form
8. Switch it to Form View
9. Create a few records as follows:
    

   Category

   Women

   Men

   Girls

   Boys

   Babies

10. Save and close the Categories form

To further enhance the inventory, and to better assist customers, most commercial stores use categories under main categories. These are referred to as sub-categories. For a department store, sub-categories would include the types of clothes, such as shirts, dresses, or shoes.

1. Replace the text in the Query1 window with the following:

   CREATE TABLE SubCategories
   (
       SubCategory Text(40) Not Null,
       Constraint PK_SubCategories Primary Key(SubCategory)
   );

2. To execute the code, on the Ribbon, click the Run button
3. On the Ribbon, click Create and, in the Forms section, click Form Design
4. Using the Properties window, set the following characteristics:
   Record Source: SubCategories
   Auto Center: Yes
5. Save the form as SubCategories
6. Design it as follows:
    
   
7. Save the Sub-Categories form
8. Switch it to Form View
9. Create a few records as follows:
    

   Sub-Category

   Shirts

   Pants

   Shoes

   Dresses

   Skirts

   Jackets

   Coats

   Suits

   Sweaters

   Belts

   Ties

   Hats

   Handbags

   Watches

   Jewelry

   Accessories

   Beauty & Grooming

10. Close the Sub-Categories form

Manufacturers and people and companies that make clothes that the Fun Department Store company sells. Normally, a department store keeps as much information as possible about the manufacturers. Companies also keep track of their suppliers. To keep our database simple, we will need just the name of the manufacturer. Many manufacturers use different names to categorize the items they make. We will create two fields for names for each manufacturer.

1. Replace the text in the Query1 window with the following:

   CREATE TABLE Manufacturers
   (
       Manufacturer Text(40) Not Null,
       OtherName Text(40),
       Notes Note,
       Constraint PK_Manufacturers Primary Key(Manufacturer)
   );

2. To execute the code, on the Ribbon, click the Run button
3. On the Ribbon, click Create and, in the Forms section, click Form Design
4. Using the Properties window, set the following characteristics:
   Record Source: Manufacturers
   Auto Center: Yes
5. Save the form as Manufacturers
6. Design it as follows:
    
   
7. Save the Manufacturers form
8. Switch the form to Form View
9. Create a few records as follows:
    

   Manufacturer	Other Name	Notes
   Ralph Lauren	Polo Ralph Lauren	Names include Ralph Lauren, Lauren by Ralph Lauren, Polo Ralph Lauren
   Polo Ralph Lauren	Ralph Lauren	Names include Ralph Lauren, Lauren by Ralph Lauren, Polo Ralph Lauren
   Lauren by Ralph Lauren	Ralph Lauren	Names include Ralph Lauren, Lauren by Ralph Lauren, Polo Ralph Lauren
   Kenneth Cole	Kenneth Cole New York	Names include Kenneth Cole, Kenneth Cole Reaction, Kenneth Cole New York
   Kenneth Cole New York	Kenneth Cole	Names include Kenneth Cole, Kenneth Cole Reaction, Kenneth Cole New York
   Kenneth Cole Reaction	Kenneth Cole	Names include Kenneth Cole, Kenneth Cole Reaction, Kenneth Cole New York
   Calvin Klein	CK Calvin Klein	Names include Calvin Klein, CK Calvin Klein
   CK Calvin Klein	Calvin Klein	Names include Calvin Klein, CK Calvin Klein
   Anne Klein	AK Anne Klein	Names include Anne Klein, AK Anne Klein
   AK Anne Klein	Anne Klein	Names include Anne Klein, AK Anne Klein
   Nautica
   Tommy Hilfiger
   Cole Haan
   Giorgio Armani
   Timex
   Johnston & Murphy
   Citizen
   Coach
   Guess
   Seiko
   Clarks

10. Close the Manufacturers form

Probably the most important part of a department store is the list of items it sells. To keep an inventory, we will use the following information for each item sold in the store:

• Item Number: This is be a number that uniquely identifies each item of the department store
• Date Entered: This is the date when the item's information was created in the inventory
• Manufacturer: This field represents the make of the item
• Category: This field identifies the type of item
• Sub-Category: This is a sub-type of item
• Item Name: This is a short name of the item
• Size: Most clothing items have a size. Some do not
• Unit Price: This is the price of one sample of the item
• Discount Rate: Some items receive a discount. There are various reasons for this. One reason is that the item may have been in the store for a while, in which case it would be discounted for fast(er) sale
• Pictures: We will try to provide one or more pictures of the item, when available

1. On the Ribbon, click Create
2. In the Forms section, click Form Design
3. Using the Properties window, change the following characteristics:
   Caption: Fun Department Store - New Store Item
   Auto Center: Yes
   Record Selectors: No
   Navigation Button: No
   Dividing Lines: Yes
4. Save the form as NewStoreItem
5. On the Ribbon, click Design and, in the Controls section, click More.
   Make sure the Use Control Wizards option is selected .
   In the Controls section of the Ribbon, click the Combo Box
6. Click the form (somewhere under the Header bar)
7. In the first page of the wizard, make sure the first radio button is selected and click Next
8. In the second page of the wizard, click Table: Manufacturers
9. Click Next
10. Double-click Manufacturer and OtherName
11. Click Next
12. Click Next
13. Click Next
14. Click Finish
15. In the Controls section of the Ribbon, click the Combo Box
16. Click the form (somewhere under the Header bar)
17. In the first page of the wizard, make sure the first radio button is selected and click Next
18. In the second page of the wizard, make sure Table: Categories is selected and click Next
19. Double-click Category
20. Click Next
21. Click Next
22. Click Next
23. Click Finish
24. In the Controls section of the Ribbon, click the Combo Box
25. Click the form (somewhere under the Header bar)
26. In the first page of the wizard, make sure the first radio button is selected and click Next
27. In the second page of the wizard, click Table: SubCategories
28. Click Next
29. Double-click SubCategory
30. Click Next
31. Click Next
32. Click Next
33. Click Finish
34. Complete the design of the form as follows:
     
    

    Control Name Caption Text Box ItemNumber Item Number: Text Box DateEntered Date Entered: Combo Box ManufacturerID Manufacturer: Button NewManufacturer New... Combo Box CategoryID Category: Button NewCategory New... Combo Box SubCategoryID Sub-Category: Button NewSubCategory New... Text Box ItemName Item Name: Text Box ItemSize Item Size: Text Box UnitPrice Unit Price: Text Box DiscountRate Discount Rate: Text Box Notes Notes: Button cmdSubmit Submit Button cmdReset Reset Button cmdClose Close

35. On the form, right-click the Reset button and click Build Event...
36. In the Choose Builder dialog box, click Code Builder and click OK
37. Implement the event as follows:

    Private Function SetDateEntered(ByVal Days As Integer) As Date
        SetDateEntered = DateAdd("d", Days, Date)
    End Function
    
    Private Sub cmdReset_Click()
        ItemNumber = CStr(Int((999999 - 100000 + 1) * Rnd + 100000))
        DateEntered = SetDateEntered(-Int(180 * Rnd + 1))
        ManufacturerID = ""
        CategoryID = ""
        SubCategoryID = ""
        ItemName = ""
        ItemSize = ""
        UnitPrice = ""
        DiscountRate = "0.00"
    End Sub

38. In the Object combo box, select Form
39. Implement the Load event as follows:

    Private Sub Form_Load()
        cmdReset_Click
    End Sub

40. In the Object combo box box, select cmdNewManufacturer
41. Implement the event as follows:

    Private Sub cmdNewManufacturer_Click()
    On Error GoTo cmdNewManufacturer_Error
        
        ' Display the Manufacturers form as a dialog box
        DoCmd.OpenForm "Manufacturers", , , , acFormAdd, AcWindowMode.acDialog
        
        ' After using the Manufacturers form, when the user closes it,
        ' refresh the Manufacturer combo box
        Manufacturer.Requery
        
    cmdNewManufacturer_Exit:
        Exit Sub
        
    cmdNewManufacturer_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume cmdNewManufacturer_Exit
    End Sub

42. In the Object combo box, select cmdNewCategory
43. Implement the event as follows:

    Private Sub cmdNewCategory_Click()
    On Error GoTo cmdNewCategory_Error
        
        DoCmd.OpenForm "Categories", , , , acFormAdd, AcWindowMode.acDialog
        
        Category.Requery
        
    cmdNewCategory_Exit:
        Exit Sub
        
    cmdNewCategory_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Please report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume cmdNewCategory_Exit
    End Sub

44. In the Object combo box, select cmdNewSubCategory
45. Implement the event as follows:

    Private Sub cmdNewSubCategory_Click()
    On Error GoTo cmdNewSubCategory_Error
        
        DoCmd.OpenForm "SubCategories", , , , acFormAdd, AcWindowMode.acDialog
        
        SubCategory.Requery
        
    cmdNewSubCategory_Exit:
        Exit Sub
        
    cmdNewSubCategory_Error:
        MsgBox "An error occured when trying to update the list of sub-categories." & vbCrLf & _
               "=- Please report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume cmdNewCategory_Exit
    End Sub

46. In the Object combo box, select ManufacturerID
47. In the Procedure combo box, select NotInList
48. Implement the event as follows:

    Private Sub ManufacturerID_NotInList(NewData As String, Response As Integer)
    On Error GoTo ManufacturerIDNotInList_Error
        Dim NewManufacturerID As Long
        
        If IsNull(ManufacturerID) Then
            ' Set the value of the combo box empty
            ManufacturerID = ""
        Else
            ' If the foreign key currently has a value,
            ' assign that value to the declared value
            NewManufacturerID = ManufacturerID
            ' Set the foreign key to null
            ManufacturerID = Null
        End If
        
        ' The combo box is ready to receive a new value.
        ' To make it happen, display the Manufacturers form
        ' as a dialog box so the user would not use
        ' the Store Items form while the Manufacturers form is opened
        ' When opening the Manufacturers form, create a new record
        ' and display the new manufacturer in it
        If MsgBox("The '" & NewData & "' manufacturer does not exist in the database. " & _
                  "Do you want to add it?", _
                  vbYesNo, "Fun Department Store - FunDS") = vbYes Then
            DoCmd.OpenForm "Manufacturers", , , , acFormAdd, AcWindowMode.acDialog, NewData
        
            ' After using the Manufacturers dialog box, let the user close it.
            ' When the user closes the Manufacturers form, refresh the ManufacturerID combo box
            Manufacturer.Requery
        
            ' If the user had created a new manufacturer,
            ' assign its ManufacturerID to the variable we had declared
            If ManufacturerID <> 0 Then
                ManufacturerID = NewManufacturerID
            End If
            
            ' Assuming that the manufacturer was created, ignore the error
            Response = acDataErrAdded
        Else
            ' If the manufacturer was not created, indicate an error
            Response = acDataErrContinue
        End If
        
    ManufacturerIDNotInList_Exit:
        Exit Sub
        
    ManufacturerIDNotInList_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume ManufacturerIDNotInList_Exit
    End Sub

49. In the Object combo box, select CategoryID
50. In the Procedure combo box, select NotInList
51. Implement the event as follows:

    Private Sub CategoryID_NotInList(NewData As String, Response As Integer)
    On Error GoTo CategoryIDNotInList_Error
        
        Dim NewCategoryID As Long
        
        If IsNull(CategoryID) Then
            CategoryID = ""
        Else
            NewCategoryID = CategoryID
            CategoryID = Null
        End If
        
        If MsgBox(NewData & " is not a valid category of this database. " & _
                  "Do you want to add it?", _
                  vbYesNo, "Fun Department Store - FunDS") = vbYes Then
            DoCmd.OpenForm "Categories", , , , acFormAdd, AcWindowMode.acDialog, NewData
        
            Category.Requery
        
            If CategoryID <> 0 Then
                CategoryID = NewCategoryID
            End If
            
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
        End If
        
    CategoryIDNotInList_Exit:
        Exit Sub
        
    CategoryIDNotInList_Error:
        MsgBox "An error occured when trying to update the list." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume CategoryIDNotInList_Exit
    End Sub

52. In the Object combo box, select SubCategoryID
53. In the Procedure combo box, select NotInList
54. Implement the event as follows:

    Private Sub SubCategoryID_NotInList(NewData As String, Response As Integer)
    On Error GoTo SubCategoryIDNotInList_Error
        
        Dim NewSubCategoryID As Long
        
        If IsNull(SubCategoryID) Then
            SubCategoryID = ""
        Else
            NewSubCategoryID = SubCategoryID
            SubCategoryID = Null
        End If
        
        If MsgBox(NewData & " is not a valid sub-category of this database. " & _
                  "Do you want to add it?", _
                  vbYesNo, "Fun Department Store - FunDS") = vbYes Then
            DoCmd.OpenForm "SubCategories", , , , acFormAdd, AcWindowMode.acDialog, NewData
        
            SubCategory.Requery
        
            If SubCategoryID <> 0 Then
                SubCategoryID = NewSubCategoryID
            End If
            
            Response = acDataErrAdded
        Else
            Response = acDataErrContinue
        End If
        
        Exit Sub
        
    SubCategoryIDNotInList_Error:
        MsgBox "An error occured when trying to update the sub-categories." & vbCrLf & _
               "=- Report the error as follows -=" & vbCrLf & _
               "Error #: " & Err.Number & vbCrLf & _
               "Error Message: " & Err.Description
               Resume Next
    End Sub

55. Return to Microsoft Access
56. In the Navigation Pane, right-click the Manufacturers form and click Design View
57. In the Properties window, click Event and double-click On Load
58. Click its ellipsis button to switch to Microsoft Visual Basic
59. Implement the event as follows:

    Private Sub Form_Load()
        ' When this form opens, find out if it received an external 
        ' value from another object (such as the StoreItemEditor form).
        If Not IsNull(Me.OpenArgs) Then
        	' If it did, put that value in the Manufacturer text box
            Me.Manufacturer = Me.OpenArgs
            ' Since our database allows up to three different names for 
            ' a manufacturer, the user will optionnally fill the other two text boxes
        End If
    End Sub

60. Return to Microsoft Access
61. In the Navigation Pane, right-click the Categories form and click Design View
62. In the Event section of the Properties window, double-click On Load
63. Click its ellipsis button and implement the event as follows:

    Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
            Me.Category = Me.OpenArgs
        End If
    End Sub

64. Return to Microsoft Access
65. In the Navigation Pane, right-click the SubCategories form and click Design View
66. In the Event section of the Properties window, double-click On Load
67. Click its ellipsis button and implement the event as follows:

    Private Sub Form_Load()
        If Not IsNull(Me.OpenArgs) Then
            Me.SubCategory = Me.OpenArgs
        End If
    End Sub

68. Return to Microsoft Access
69. Close all forms
70. When asked to save, save
71. Replace the text in the Query1 window with the following:

    CREATE TABLE StoreItems
    (
        StoreItemID COUNTER(100001, 1) NOT NULL,
        Constraint PK_StoreItems Primary Key(ItemID)
    );

72. To execute the code, on the Ribbon, click the Run button
73. In the Navigation Pane, right-click StoreItems and click Design View
74. Change the list of fields as follows:
     

    Field Name	Data Type	Field Size	Format	Caption
    StoreItemID				Store Item ID
    ItemNumber	Number			Item Number
    DateEntered	Date/Time		Long Date	Date Entered
    Manufacturer	Text		40
    Category	Text		40
    SubCategory	Text		40	Sub-Category
    ItemName	Text	80		Item Name
    ItemSize	Text	40		Item Size
    UnitPrice	Number	Double	Fixed
    DiscountRate	Number	Double	Percent	Discount Rate
    Pictures	Attachment
    Notes	Memo

75. Save and close the StoreItems table
76. In the Navigation Pane, right-click NewStoreItem and click Copy
77. Right-click any part of the Navigation Pane and click Paste
78. Type StoreItems as the name of the form
79. Click OK
80. In the Navigation Pane, right-click the StoreItems table and click Design View
81. Using the Properties window, change the following characteristics:
    Record Source: StoreItems
    Caption: Fun Department Store - Store Items
    Record Selectors: Yes
    Navigation Buttons: Yes
    Allow Additions: No (the new records will be added using the NewStoreItem form; but this form can be used to update a record; this means that, for example, this form can be used to add the picture(s) of an item)
82. On the form, right-click the Submit button and click Build Event
83. In Microsoft Visual Basic, delete the code of the Click event of cmdSubmit, the Load event of the form, and the Click event of cmdReset
84. Return to Microsoft Access
85. Delete the Reset button
86. Change the characteristics of the Submit button as follows:
    Name: cmdNewStoreItem
    Caption: New Store Item...
87. Complete the design of the form as follows (appropriately set the Control Source of each control, it is the same as the name of the control):

88. Save the form
89. Right-click the New Store Item button and click Build Event...
90. In the Choose Builder dialog box, double-click Code Builder
91. CliClick the ellipsis button and implement the event as follows: 
    

    Private Sub cmdNewStoreItem_Click()
        DoCmd.OpenForm "NewStoreItem"
    End Sub

92. Save and close the StoreItems form
93. In the Navigation Pane, right-click the Submit button and click Build Event...
94. Double-click Code Builder
95. Implement the event as follows:

    Private Sub cmdSubmit_Click()
        Dim curFunDS As Database
        Dim rstStoreItems As Recordset
        
        Set curFunDS = CurrentDb
        Set rstStoreItems = curFunDS.OpenRecordset("StoreItems")
        
        rstStoreItems.AddNew
        rstStoreItems("ItemNumber").Value = ItemNumber
        rstStoreItems("DateEntered").Value = CDate(DateEntered)
        rstStoreItems("ManufacturerID").Value = ManufacturerID
        rstStoreItems("CategoryID").Value = CategoryID
        rstStoreItems("SubCategoryID").Value = SubCategoryID
        rstStoreItems("ItemName").Value = ItemName
        rstStoreItems("ItemSize").Value = ItemSize
        rstStoreItems("UnitPrice").Value = CDbl(UnitPrice)
        rstStoreItems("DiscountRate").Value = CDbl(DiscountRate)
        rstStoreItems("Notes").Value = Notes
        rstStoreItems.Update
        
        cmdReset_Click
        
        Set rstStoreItems = Nothing
        Set curFunDS = Nothing
    End Sub
    
    Private Sub cmdClose_Click()
        DoCmd.Close
    End Sub

97. In the Navigation Pane, double-click NewStoreItem
98. Create the Store Items
99. Close the form

We will consider a shopping session one more items that a customer purchases. Normally, a customers selects items in the store and brings them to the cashier who will vallidate the purchase. For our database, we will create a unique receipt number. For our inventory, we will need to keep track of who (the employee) processed the purchase, the date and time the purchase occured, and the total the customer paid.