MS Access Database

Retail cigar store database needed. I buy my cigars from several wholesale vendors. The cigars are not unique to the vendor. I buy cigar type A from Vendor #1 and Vendor #2 depending on the price at the time.

Vendor Form
Item Form
Invoice Form
All forms to include buttons to Save, Clear or Print form.

Vendor Report: (Filtered by Vendor)
Total spent for time period specified
Invoice list
Items purchased list

Item Report: (Filtered by Brand)
Item List showing Name, Barcode, SKU, Total purchased, Box Quantity, Total Unit Quantity (Box Quantity x Quantity Purchased), Last Purchased Date

Item Report: (Filtered by Barcode)
Item List showing from invoices:
Top Section: Brand, Name, Barcode, SKU, Box Quantity
Line Items: Date, Vendor, Invoice Number, Quantity Purchased, Box Cost, Sell Box Amount, Unit Cost, Sell Unit Amount

Invoice Report: (Filtered by Invoice Number)
Report of a completed invoice with all line items and amounts

All Reports to include buttons to Save, Clear or Print the report

In the future I would like to input the cigar quantity sales information from the POS system to verify an inventory total based from what was purchased.


Table Examples

VENDOR Table
Name
Sales Rep
Phone (format / mask)
Email
Address
City
State (2 letter uppercase)
Zip Code
Notes

 

ITEM Table
Item Barcode (validate if barcode exists)
SKU (validate if SKU exists)
Brand
Name
Box Quantity
Last Purchase Date (from last invoice)
Last Vendor (from last invoice)
Box Cost (from last invoice)
Sell Box Amount (from last invoice)
Unit Cost (from last invoice)
Unit Sell Amount (from last invoice)
Notes


INVOICES Table
Vendor (validate if exists, if not add new vendor)
Invoice Number (manual input from vendor invoice)
Invoice Date (datepicker)

Item Barcode (validate if exists, if not add new Item)
Vendor Item Number (manual input from vendor invoice)
Sales Tax Total (manual input from vendor invoice)
Cigar Tax Total (manual input from vendor invoice)
S&H (manual input from vendor invoice)
CC Fee (manual input from vendor invoice)
Invoice Total (calculated)

Brand (auto fill from ITEM table)
Name (auto fill from ITEM table)
Quantity Purchased (manual input from vendor invoice)
Item Cost (manual input from vendor invoice)
Item Total (calculated = Quantity Purchased * Item Cost)
Box Cost (calculated = Item Cost + (percent of total fees based on percent of sub-total
invoice amount)
Sell Box Percent Markup (80% standard with manual input override)
Sell Box Amount (calculated = Box Cost + Sell Box Percent Markup – roundup
to xxx.99)
Unit Cost (calculated = Box Cost / Box Quantity)
Unit Percent Markup (10% standard with manual input override)
Sell Unit Amount (calculated = (Sell Box Amount + Sell Unit Percent Markup) / Box
Quantity – roundup to xxx.99)

(ability to add multiple items per invoice)
Invoice Notes