Bagel Factory

This page is written by the project coworkers Begum Dogan and Nagehan Albayrak to elaborate the Bagel Factory Project - Point of Sale System

THE BAGEL FACTORY

The Bagel Factory is a small sized corporation in Binghamton, NY. It is an established quick service restaurant on Binghamton's West Side.Mr. Rick Frisbie and his team produces delicious bagels. They make bagels from scratch and bakes them on site daily for their retail location, as well as for whole-scale customers throughout the area. Currently the Bagel Factory uses a standard electronic cash register, with multiple programmable options, as its Point Of Sale system. The owner of The Bagel Factory, Mr. Frisbie, wants to standardize the process of Point of Sale, use technological developments and take a step in his business for future expansion. He is looking to upgrade the Point of Sale to a touch screen unit with customizable reporting capabilities.

PURPOSE OF THE PROJECT

In our project, we are going to implement a Point of Sale system to The Bagel Factory. Point of Sale system (POS) refers to the area of a store where customers can pay for their purchases. The term is normally used to describe systems that record financial transactions such as electric cash registers or an integrated computer system that records the data that comprises a business transaction for the sale of goods or services. The process of POS is supposed to be like this:

• Customer is in the line to order.
• When customer gives the order, the worker enters the order to the database by touching the buttons on the touch screen.
• The worker prints the ticket that includes the order of the customer.
• When the customer comes to take his/her order he/she gives the ticket. Salesperson takes the order’s detail by using the barcode reader.
• The price appears on the main monitor
• For the customer, the price appears on the LCS monitor.
• The salesperson prints the bill by printer.
• The money is kept in cash drawer.
• The database and the software runs in the main computer.

HARDWARE

As a result of our meetings with both Mr. Rick Frisbie, we have made a list of required hardware for this project.

• Server Computer
• Touch Screen
• Printer
• Barcode Reader
• Credit Card Scanner
• Cash Drawer
• LCD Monitor

At the end of the semester we have the list below as our hardware:

• Touch Screen
• Computer
• Receipt Printer
• Cash Drawer

SOFTWARE

• POS software tracks your items, recording sales and printing receipts for your customers
• Software talks to your POS peripherals including the receipt printer, the cash drawer, the monitor
• Uses MS Access 2007 and VBA to develop the software

Database Item Tables

• BFBagels  : Breakfast Bagels
• BFOmlettes  : Breakfast Omlettes
• BFMuffins  : Breakfast Muffins
• BFSideOrders  : Breakfast Side Orders
• BFToppings  : Breakfast Bagel Toppings
• BFBagelVarieties : Bagel Varieties (not used this semester)
• BFCCFlavors  : CreamCheese Flavors (not used this semester)
• LunchSub  : Lunch Subs (small)
• LunchSubL  : Lunch Subs (large)
• LunchWrap  : Lunch Wraps
• LunchPanini  : Lunch Panini
• LunchSideSalad  : Lunch Side Salads
• LunchWrapBread  : Lunch Wrap Breads (not used)
• LunchBagelSandItems : Lunch Bagel Sandwiches Items
• Beverages  : Beverages
• BreadType  : Bread Type (not used)
• MealDeal  : Meal Deal Items
• Extra  : Extra Items
• Misc  : Miscellaneous Items
• Customer  : Keeps customer number to use as order number on the receipt (not used)

Other than menu item tables,

• Current table : Keeps the current order's information
• Archive table : Keeps all of the orders' information
• Cancelled order table : Keeps the cancelled orders' information for safety of the company

In this database, we do not use ID numbers in the tables as primary keys, because Mr. Frisbie does not want it as a relational database. The missing part of the database is CustomerID. Mr. Frisbie wants an ID number for each customer to track the order easier and keeps the order number as CustomerID.

Embedded SQL Examples

We used Embedded SQL code to get the data.

For comboboxes, we used AfterUpdate event and the event includes the code:

CodeDb.Execute "INSERT INTO [Current] (Item,[Price w/ tax],Taxable) SELECT Beverages.Item, Beverages.Pricesss, Beverages.Taxable FROM Beverages WHERE Beverages.Item=" & "'" & Me.Combo63.Column(1) & "'"

For buttons that open a new form:

DoCmd.OpenForm "MakeChanges"

For radiobuttons and buttons that work together (coffee selection):

Dim str_small As String
Dim str_large As String
str_small = "INSERT INTO Current(Item,[Price w/ tax],Taxable) VALUES ('Small Coffee','$1.50','N')"
str_large = "INSERT INTO Current(Item,[Price w/ tax],Taxable) VALUES ('Large Coffee','$1.75','N')"
Select Case Frame20.Value
Case 1
CodeDb.Execute str_small
Case 2
CodeDb.Execute str_large
End Select

Graphical User Interface for POS System

• Breakfast Menu View

When buttons are clicked, new forms, each including a combobox related to the item table, are opened

• Lunch Menu View

Each combobox includes the related items. By using after update event, selected item is inserted into the current table.

• Beverages, Miscellaneous, Meal Deal

After the size selection, order the hot beverages. By using after update events of soft beverages, Miscellaneous and Meal Deal comboboxes, selected item is inserted into the current table.

• Current Subform

The current order appears in CurrentSubForm. The total price without tax is calculated and shown as Sum. According to the Taxable field value, the tax is calculated and shown as Tax. When enter amount is clicked a new keyboard form opens and change due is calculated.

Receipt

• Future Expansions