How To Create An Inventory Database In Access 2010

Important: Microsoft no longer recommends creating and using Access 2010 web databases. For more information, see Access 2010 web databases roadmap.

The Access 2010 Goods web database template creates a ready-to-use database that lets you track orders, products, invoices, inventory, and other data associated with running a business that sells goods. This article shows you how to start using the Goods web database.

The following video shows a brief scenario of how this template might be used.

Your browser does not support video.

If you haven't already, download the Goods Template.

Read on to learn how to use the template to make your own database and start using it to help run your business.

Start the database for the first time
Getting started: Add existing data to your Goods database
- Enter your company information
- Enter employee information
- Enter products, categories, and suppliers
- Enter inventory levels
- Enter orders
Dashboard: View orders and purchasing status
Products: Manage products, inventory, and suppliers
- Manage inventory
- Check to see which products need restocking
- View discontinued products
- Create categories for products
- Manage supplier information
Orders: Manage customer orders
- Enter a new order
- View and manage orders by status
  - Create an invoice
  - Enter a customer payment
  - Ship an order
  - Mark an order as Completed
- Order status quick reference
Manage customer information
Manage shipper information
Purchases: Restock your inventory
- Create a purchase order
- Submit a purchase order for purchasing
- Receive inventory
- Complete a purchase order
- Manage supplier information
Report Center: View and print reports
Advanced: Manage customers, employees, suppliers, categories, and shippers
Next Steps

Start the database for the first time

When you first create the Goods database, some sample data is included in the database so that you can get an idea of what types of data each form or report contains. A dialog box appears which allows you to choose whether to keep the sample data or delete it so that you can begin entering your own data.

The Goods template welcome dialog box

Click this button to open the database and work with the sample data. This will populate the Goods database with data from the fictional company Northwind Traders.

Click this button to delete the sample data so you can begin entering your own. This also prevents this dialog box from appearing the next time you start the database.

Clear this check box to prevent the dialog box from appearing next time you start the database.

Note:All fields and controls in the Goods template that display currency data use a dollar sign ($) as the currency symbol. If your locale uses a different currency symbol, you should consider adjusting the affected fields and controls before you remove the sample data. For more information, see the See Also section.

Getting started: Add existing data to your Goods database

The Getting Started tab includes links to videos and other resources that will help you begin using the database quickly.

Goods Template Getting Started Tab

In addition to providing assistance for using the database, the Getting Started tab contains additional tabs that let you set up the database for its initial use.

When you are finished viewing the information on the Welcome tab, click Next.

Enter your company information

On the Company Info sub-tab, you can enter information about your company, such as name, location, phone, and email address.

Goods Template Company Information Tab

The company name you enter will be displayed at the top of the database "Main" screen (where you see "Northwind Traders" in the preceding illustration). Your company information will also be displayed on invoices and other reports created by the database.

Type in your company's information into the boxes, click Save and Apply Company Settings, and then click Next.

Note:It is not necessary to enter all of your data by using the Getting Started sub-tabs. Most of the information that you can enter on these sub-tabs can also be entered on the main tabs, such as Products, Orders, and Employees. However, these sub-tabs give you a convenient way to get started entering data.

Enter employee information

On the Employees sub-tab, you can begin adding employees to the database. This will help you track who was the salesperson on each order, who submitted an order, and so on. If you are a sole proprietor, the database will work fine with just a single employee.

Type each employee's information into the datasheet, and then click Next.

Note:You can also view, edit, and add employees on the main Employees tab.

Enter products, categories, and suppliers

On the Products sub-tab, you can begin adding products to the database. These are the products that your business sells. You can also edit the lists of categories and suppliers on this sub-tab.

Type each product's information into the datasheet, and then click Next.
Alternatively, click New Product to open the Product Detail form, into which you can enter the product information.
Click Edit Categories or Edit Suppliers to edit the lists of categories and suppliers.
When you are finished editing product information, click Next.

Note:You can also view, edit, and add products on the main Products tab.

Enter inventory levels

On the Inventory sub-tab, you can enter the amounts of inventory that you have on hand, as well as the minimum and target levels of inventory that you want to keep in stock.

For each product, type the number of units you want in the Initial Level, Reorder Level, Target Level, and Min Reorder Quantity columns.
When you are finished editing inventory information, click Next.

Note:You can also view and edit inventory levels, categories, and suppliers on the corresponding sub-tabs of the main Products tab.

Enter orders

If you already have some orders on hand, you can enter basic information about them on the Orders sub-tab.

For each order, enter the information you have, such as Order Date, the employee who took the order and the customer.
If you enter a customer that has not yet been entered into the database, Access prompts you to enter the new customer. Click Yes, and then fill in the information you have. When you close the datasheet, the customer will be available in the Customer drop-down list.
When you are finished editing order information, click Next.

Note:You can also view and edit inventory levels, categories, and suppliers on the corresponding sub-tabs of the main Products tab.

Dashboard: View orders and purchasing status

The Dashboard tab provides a convenient place to view active orders, as well as any products that are running low on inventory and need to be reordered.

Goods Template Dashboard Tab

You can also edit the information that you see on this tab—just click in the cell that you want to edit and begin typing.

To enter detailed information about new customer orders or purchase orders, click New Customer Order or New Purchase Order, and then fill in the form that appears.

Products: Manage products, inventory, and suppliers

The Products tab is where you manage all aspects of the products you sell, such as:

Detailed product information
Inventory levels
Discontinued products
Product categories
Suppliers of your products

When you click the Products tab, it initially displays a datasheet listing of all products currently being sold by your business.

Goods Template Products Tab

You can enter and edit product information directly into the datasheet.

The Product Code field can contain any combination of text and numbers, as long as you use a unique value for each product.
To add a new product, type it into the datasheet or click New Product to open the Product Detail form.
To edit the details of an existing product, click the product code in the datasheet. The Product Detail form appears, in which you can view and edit the information about the product.

Manage inventory

The Goods database template provides automatic tracking of inventory; whenever an order is shipped, or new products are purchased, the database automatically updates the inventory so that you know how much stock you have on hand at any time. The database will also display products that need to be reordered, based on thresholds you specify.

On the Products tab, click Inventory Levels. This datasheet displays current inventory levels and other thresholds for each product as follows:

On Hand The number of items that you currently have in stock.
Allocated The number of items that have been ordered by customers, but not yet shipped.
Available The difference between the number of items on hand and the number allocated.
Shrinkage This is the number of items that have been lost due to damage, spoilage, loss, and so on.
On Order The number of items that you have ordered to replenish stock, but which have not yet been received.
Current Level The number of available items minus the number of items on backorder, plus the number of items currently on order.
Target Level The number of items that you want to have on hand to accommodate the predicted level of orders.
Below Target The current number of items at which you are below your target level.
Purchase Click thePurchaselink to enter a new purchase order. For more information about purchasing, see the section Manage purchases.

Check to see which products need restocking

To see at a glance which products need to be ordered, on the Products tab, click Needs Restocking. This view is the same as the Inventory Levels datasheet, except that the records have been filtered to only those that are at or below their specified reorder level. To quickly reorder a product, scroll to the right and click the Purchase link. For more information about purchasing, see the section Manage purchases.

View discontinued products

On the Products tab, click Discontinued Products to view a list of products that have been marked as discontinued.

To reinstate a discontinued product, click its product code, and then in the Product Detail form, clear the Discontinued check box.

Create categories for products

Categorizing your products helps you to organize them on forms and reports. On the Products tab, click Categories to customize your list of categories. Enter each category on its own line.

To delete a category, click the gray box just to the left of the ID, and then press DELETE.

Note:Access prevents you from deleting any categories that are connected to products. For example, you cannot delete the "Beverages" category if there are any products that are still assigned to that category.

Manage supplier information

Suppliers are the businesses and individuals from whom you purchase your inventory. To keep track of your suppliers' contact information, on the Products tab, click Suppliers.

Click an ID to open an existing supplier record and edit their information.
Click (New) at the bottom of the ID column to enter a new supplier, or just begin typing on the last row of the datasheet.
To delete a supplier, click the gray box just to the left of the ID, and then press DELETE.

Note:Access prevents you from deleting any suppliers that are connected to products. For example, you cannot delete Supplier X if any products are still listed as coming from that supplier. Delete the supplier's products first, and then delete the supplier.

Orders: Manage customer orders

The Orders tab is where you go to enter new orders, view orders by status, and track information about customers and shippers.

Goods Template Orders Tab

Enter a new order

When a new customer order comes in, you use the Customer Order form to enter the details.

On the Orders tab, click New Order. Alternatively, on the Dashboard tab, click New Customer Order.

The Customer Order Form appears.
Select a customer from the drop-down list. If this is a new customer, click New, fill in the customer's information on the Customer Details form, and then click Save and Close.
Fill in the information on the Customer Order Form, being sure to also fill in the fields on the Shipping Information and the Payment Information tabs.
When you have finished filling out the order, click Save & Close.

View and manage orders by status

Each order moves through several different statuses, which are automatically updated as you perform the tasks of entering, invoicing, and shipping an order:

New
Invoiced
Shipped
Completed

When you first click the Orders tab, the datasheet displays all active orders (all those with a status other than Completed). After an order has been taken, the process flow is as follows:

Create an invoice

When an order is first entered, it is marked as New. The next step with a new order is to send an invoice.

To invoice an order:

On the Orders tab, click Need Invoicing to see all orders that are ready for invoicing.
Double-click the order that you want to invoice.
In the Customer Order Form, check that all the information is correct.
On the Shipping Information tab, verify that a shipper has been selected in the Ship Via box.
Click Invoice Order, and then click Yes to confirm.

The Goods database marks the order as Invoiced, and displays an invoice for you to print.

Enter a customer payment

Some businesses require payment before an order can be shipped. Others allow shipment to occur before payment is received. The Goods database allows for either scenario.

To enter a payment:

On the Orders tab, click Awaiting Payment.

Note:This filters the datasheet to show orders that are marked as Invoiced or Shipped. If your business requires payment before shipping, and you see an order marked as Shipped in this view, you will probably want to investigate why the order was shipped before payment was received.
Double-click the order for which you want to enter a payment.
In the Customer Order Form, click the Payment Information tab.
Select a value from the Payment Type list, and fill in any other payment information you have.
Do one of the following:
- If the order has not yet been shipped, and you want to ship the order now, click Ship Order .
- If the order has already been marked as Shipped and you now consider the order complete, click Mark as Completed to complete the order.
- To close the Customer Order Form without making any further changes, click Save & Close.

Ship an order

As mentioned in the previous section, some businesses require payment before an order can be shipped, whereas others allow shipment to occur before payment is received. Therefore, the Goods database does not enforce a rule requiring payment before shipping.

To ship an order:

On the Orders tab, click Ready To Ship to see all orders that have been marked as Invoiced.
Double-click the order that you want to ship.
If your business requires payment before an order can be shipped, click the Payment Information tab and verify that payment information has been entered.
Click Ship Order, and then click OK on the confirmation message.
Do one of the following:
- If you now consider the order complete, click Mark as Completed to complete the order.
- To close the Customer Order Form without making any further changes, click Save & Close.

Mark an order as Completed

After an order has been shipped and payment has been received, you can mark it as Completed.

Click the Orders tab, and then scan the list for any order that has dates in both the Shipped Date and Payment Date columns. This indicates that an order has been shipped and that payment has been received.
Double-click the order that you want to mark as Completed.
In the Customer Order Form, click Mark as Completed, and then click OK on the confirmation message.
Click Save & Close.

Marking an order as Completed removes it from most of the views in the database. However, you can still view completed orders by clicking the Orders tab, and then clicking Completed Orders.

Order status quick reference

The following table provides a quick reference for which orders are displayed in each view, as well as the actions you can take on those orders:

If you click…	…and the order status is…	…you can perform the following task(s) on the Customer Order form:	Notes:
Orders	New	Invoice Order	A shipping company must be specified before order can be invoiced.
Orders	Invoiced	View Invoice or Ship Order
Orders	Shipped	View Invoice or Mark as Completed
Need Invoicing	New	Invoice Order	A shipping company must be specified before order can be invoiced.
Ready to Ship	Invoiced	View Invoice, Ship Order
Awaiting Payment	Invoiced	View Invoice, Ship Order or Mark as Completed
Awaiting Payment	Shipped	View Invoice, Mark as Completed	Shipping and payment information must be entered before order can be marked as completed.
Completed Orders	Completed	None

Manage customer information

The Goods database stores contact information about all your customers. You can add new customers as part of the process of entering a new order, or you can manage your customers by using the following procedure:

On the Orders tab, click Customers.
To edit an existing customer, click in the field you want to edit and then start typing.
To add a new customer, click (New) at the bottom of the ID column, or just begin typing in the last row of the datasheet.

Manage shipper information

The Goods database stores contact information about all your shippers. You can manage your customers by using the following procedure:

On the Orders tab, click Shippers.
To edit an existing shipper, click in the field you want to edit and then start typing.
To add a new shipper, click (New) at the bottom of the ID column, or just begin typing in the last row of the datasheet.

Purchases: Restock your inventory

If your business obtains its products from wholesalers or other businesses, it is necessary to make purchases to restock inventory. The Goods database lets you create and track purchase orders, and automatically updates inventory numbers as purchased items are received. All of these tasks can be done from the Purchases tab:

Goods Template Purchases Tab

Create a purchase order

Creating a purchase order is the first step in buying new inventory.

On the Purchases tab, click New Purchase Order.
In the Purchase Order form, select a supplier. If you are ordering from a new supplier, click New, fill out the information in the Supplier Details form, and then click Save & Close.
On the Purchase Details tab, select the products that you want to order, and enter quantities for each.
If the purchase order is ready to be submitted, click Submit Order. Otherwise, to keep the purchase order in New status so that you can make changes to it before submitting it, click Save & Close.

The database creates the purchase order and marks it as New.

Submit a purchase order for purchasing

The next step for a new purchase order is to submit it for purchasing. This step updates the inventory totals so that you know how much stock is currently on order.

On the Purchases tab, click Awaiting Approval.
Double-click a purchase order to open the Purchase Order form.
Check that the products, quantities, and prices are accurate, and then click Submit Order.

The database marks the purchase order as Submitted and updates the On Order quantity for the products being ordered. If an order is large enough to put a product's inventory above its Reorder Level setting, the product is removed from the Needs Restocking list.

Receive inventory

When new inventory is received, the next step is to mark it as such. This updates the inventory totals so that you know how much inventory is on hand.

On the Purchases tab, click Inventory Receiving.
Double-click a purchase order to open the Purchase Order form.
On the Purchase Order form, click the Inventory Receiving tab.
For each product received, select the Post To Inventory check box.
If all items have been received and payment information has been entered on the Payment Information tab, you can click Complete Order to mark the purchase order as Completed.
If some items on the purchase order have not been received yet, click Save & Close. You cannot complete the order until all items have been received and posted to inventory.

Complete a purchase order

If all the items on a purchase order have been posted to inventory, and payment information has been entered, you can mark the purchase order as Completed. This removes the purchase order from the active purchase order lists, and moves it to the Completed Purchases list so that you can refer to it later if needed.

To mark a purchase order as Completed:

On the Purchases tab, click Inventory Receiving.
Double-click a purchase order to open the Purchase Order form.
Click the Payment Information tab and make sure that the correct payment information has been entered.
On the Purchase Order form, click Complete Order.

Manage supplier information

Suppliers are the businesses and individuals from whom you purchase your inventory. To keep track of your suppliers' contact information, on the Purchases tab, click Suppliers.

Click an ID to open an existing supplier record and edit their information.
Click (New) at the bottom of the ID column to enter a new supplier, or just begin typing on the last row of the datasheet.

To delete a supplier, click the gray box (record selector) just to the left of the ID, and then press DELETE.

Note:Access prevents you from deleting any suppliers that are connected to products. For example, you cannot delete Supplier X if any products are still listed as coming from that supplier.

Report Center: View and print reports

The Goods database template includes six reports, which you can view and print by clicking the Report Center tab:

Goods Template Report Center Tab

To preview a report:

Under Select a Report, click the report that you want to view.

Access displays the report in the preview pane. Some reports let you customize filters and grouping options at the top of the preview pane.

To print a report:

Click Open in New Tab, and then on the File tab, click Print, and select the print options that you want.

Advanced: Manage customers, employees, suppliers, categories, and shippers

The Advanced tab provides a convenient place to manage the following lists:

Customers
Employees
Suppliers
Categories
Shippers

Goods Template Advanced Tab

Each list is displayed as a datasheet, where it is easier to spot inconsistencies and make bulk changes to data.

Next Steps

As you begin to use the Goods database to manage your business, you will most likely think of new ways in which you want to query or filter your data. You may also discover that you want to modify the database to better suit your needs. The links in this section take you to more information about the typical tasks and modifications that you can do.

For information about finding, filtering, or querying data in Access, see the following:
- Locate records in an Access database
- Apply a filter to view select records in an Access database
- Introduction to queries
For more detailed information about creating new database objects, see the following:
- Video: Create basic queries
- Introduction to forms
- Introduction to reports in Access
- Video: Create basic reports
- Create mailing labels in Access
And finally, an important article about safeguarding your data: Protect your data with backup and restore processes