Arrow to top of page BACK TO TOP

SOFTWARE THAT FITS
AROUND YOUR BUSINESS


Creating an MS Access Stock Control Database

Are you looking for advice or help with a stock control system?

If yes then call now for a free consultation on 01747 822616

Or

Go to our contact page to send us a message.

If you would like to try and create your own stock control database then read on...

Plus: FREE Stock Control Database sample Download

Here are some step-by-step instructions for creating your own stock management database or stock control system in MS Access.  We have also included some hints and tips based on our many years of experience creating MS Access stock control databases for real businesses.

Before you get started
Please note that in order to run the free access stock control sample you must have a full version of Microsoft Access installed on your computer.

Plan
It might sound obvious, but one of the most important points when creating your MS Access stock control database is deciding exactly what you need it to do, and what it is for.  Getting the design right is much easier once you understand the purpose of the database.  A well-designed database will be simpler to maintain, and to adapt later on if your requirements alter. 

For example, the simple MS Access Stock Control Database we are creating needs to be able to :

  • Log orders from Customers
  • Track stock levels
  • Warn when parts need to be re-ordered

Think about the details of the way your business works. Does each customer order one type of item at a time, or do your customers order a range of parts or items from you all at the same time?  Do you have just one supplier for each part you stock?  Do you order a range of items from each supplier or does each supplier just provide you with one type of item?  Your design will also be different if you stock items for manufacturing rather than to sell on.

Define the tables required
Information in a database is held in tables.  Think about the information that your database will need to hold.   Each table should hold the information related to just one area or subject.  Data within one table is related to data in other tables, using relationships that you define.

The tables within our example database are : Customers, Orders, Parts, Part Types, Purchase Orders and Suppliers.  To keep the database simple we are assuming that each customer orders just one type of part at a time, and that we order just one type of part from a supplier at a time.   You may require something more comprehensive: your customers may order a whole selection of items together, and you will probably have suppliers who provide you with a range of items.

Set up fields within the tables
Within a table, information is held in “fields”.  Typical fields in the Parts table might be Part Number, Part Description, Supplier and Stock Level. The Parts table would then be related to the Suppliers table, so that you can find out who supplies a particular part.

Data types: 
For each field in the table, you define a data type to show the type of data it will hold, such as Number, Text, Date/Time or Currency. Within each type you can further specify the exact nature of the data, such as the number of characters for a Text.  You probably already use part numbers within your business, and the format you use is likely to help you decide the data type for the field Part Number. Your part numbers might be of the form ABCD2222, in which case you might choose to use an 8-character text string. 

Look-up lists:  If you deal with large numbers of different parts, you might want to classify them into Part Types.  You would add a table called Part Types, which lists the different types of Parts, and then add a field to the Parts table to show the Part Type of each part.  Hint – if you want to progress to using Visual Basic with your database, you will find it easier if you have no spaces in the table names or field names. For example, call your table PartTypes not Part Types.

Think about how you will be using the fields, and make sure you define them in the most logical way for your purposes. For example, it usually makes sense to store people’s names as First Name and Surname separately rather than as one field so that you can easily sort and list names in alphabetical order (of surname).  A tip we find useful is to hold postal addresses as one field, rather than split into individual elements of the address such as Address Line 1, Address Line 2, Town, County and Postcode.  This makes it much easier to incorporate addresses into forms and reports, and it eases data entry.
To create a Parts table and define its fields:

  1. In MS Access 2013/2010/2007 click on the Create tab on the Ribbon, then on Table Design. This opens a new table in design view.  Each row in design view represents a field in the table. (in Access 2003, click on Tables in the list of Objects then click the New button which opens a dialog box called New Table. Finally select Design View, and click OK)
  2. Click in first column, top row.
  3. Enter the name of the first field (PartNo)
  4. Use the Tab key to go to the next column where we define the data type.  Click the drop-down and select Text.
  5. Use the Tab key to go to the next column, and enter a description for the new field.
  6. As this will be the primary key for the table, click the primary key button on the toolbar.  The primary key is described in more detail below.
  7. On the General tab, at the bottom of the window, click in the Field Size row, and enter 10 to define a text string with 10 characters.
  8. On the General tab, click in the Caption row, and type the label you want for this field on-screen. (e.g. Part Number).
  9. Click in the second row to define the next field in the table.  And so on.
  10. When you have finished adding fields to the table, close it by clicking on the X in the top right hand corner.  MS Access will ask you to name the table.  Enter “Parts”.

The Parts table of our MS Access stock control database contains the following fields :

PartNo

10-character text string

Unique part number to define each part

PartName

50-character text string

Name/description of this part

PartUnit

20-character text string

The unit of measure for this part e.g. Kg, Meters, box of 10

SupplierRef

6-character text string

The supplier of this part

StockLevel

Integer

The number of this part in current stock

MinStockLevel

Integer

The minimum number of this part in stock before you need to re-order

CostPrice

Currency

The cost price of this part

SalePrice

Currency

The sale price of this part

PartType

1-character text string

The type of part this is

PartNotes

255-character text string

Tip : it is often useful to include a Notes field for any other information that you might want to add later.

MS Access Stock Control table
The Parts table in Design View (Access 2013, click for full image in new tab)

MS Access table
The Parts table in Design View (Access 2003)

The MS Access stock control screenshots above show the Parts table from our stock control database in Design view.  Using Design View enables you to define all the fields in your table, specify their Data Types, describe them and define their format. 
Tip – Do complete the Description of the field.  When you use the Form Wizard to create forms for you later, this description will be displayed in the status bar to help users. 
Tip – If your field has an abbreviated name, or has no spaces in it, use the Caption area to write its name in plain English. This caption will then appear on forms produced by the Form Wizard.  For example if your field is called POQty, enter Quantity in the caption.

The Customers table is as follows:


CustomerRef

8-character text string

Unique 8-character reference for this customer based on first 4 letters of surname (e.g. SMIT0001).  This will make it easy to find individual customers. This field is the primary key.

CustomerFirstName

20-character text string

The first name of this customer

CustomerSurname

25-character text string

The surname of this customer

CustomerAddress

255-character text string

The address of this customer

CustomerTel

15-character text string

The telephone number of this supplier

The stock control Customers table above is designed for a business whose customers are predominantly individuals.  When your customers are mainly larger businesses, and you may have several contacts at each business, or each business might have several sites, then you will need a more complex solution.

Primary Keys
The primary key uniquely identifies each record in a table so that MS Access can easily bring together information in more than one table, finding the data in a second table that relates to that in the first. Within each table, one field should be used as the primary key.  You may not have nulls (empty), or duplicate values in primary key fields – so don’t use fields such as names, in case you end up with two Bill Smiths in your database.  MS Access provides an auto-number data type, which is often useful for primary keys, especially for tables where you will have large numbers of records and where you don’t naturally have something to act as the primary key. When you are dealing with a table likely to hold only a limited number of records, for example, Part Types, you will probably find it more useful to use a one- or two-character code to define the record.  This will help you ensure that you don’t accidentally enter the same part type twice.

In the Parts table, the Part No field is designated as the primary key.  You cannot have two parts with the same part number so this will be guaranteed to produce no duplicates.

To set up a primary key, in table design view, click the row selector for the field you wish to make the primary key.  Click the Primary key button on the toolbar.
MS Access primary key
The Primary key button in Access 2003. In Access 2013 it in the table design tab, with the label 'Primary Key' below it.

Relationships
Relationships are set up within the database, to show the way in which one table relates to another.  A one-to-many relationship is the most common kind of relationship.  In this relationship, a record in one table can have more than one matching record in a second table, but each record in the second table can have only one matching record in the first table.  For example, each Part can have only one Part Type, but for each Part Type there are likely to be many parts of that type.

If each part has only one supplier as in our example, then this is another straightforward one-to-may relationship. If each part can be supplied by several different suppliers, then you will need a different design.

In our sample MS Access stock control database, the following relationships between tables are required.

Suppliers - Parts, to specify the supplier of each part
Parts - Purchase Orders, showing the part ordered on a Purchase Order
Parts - Orders, showing the part ordered by a Customer
Customers - Orders, showing the customer for each Order
Part Types - Parts, classifying each part into a particular part type

To set up the relationship between the tables Parts and Part Types, you should do the following.

  1. Set up the field Part Type Code in the Part Types table as a single-character text string defining the part type.
  2. Make this field the primary key
  3. Open the Parts table in Design view.
  4. Add a field Part Type Code to the Parts table. Make sure it is also a single-character text string.
  5. Now click in the Data Type column of the Part type field to display a down arrow. Click this to display a drop-down list, and select Lookup Wizard.
  6. Select “I want the lookup column to look up the values in a table or query”. Click Next.
  7. From the list of tables displayed, select the Part Types table.  Click Next.
  8. Click the fields you want included in your lookup column.  In this case, we will select both fields. Click Next.
  9. A sort order can be selected if required.  Select Description.  Click Next.  
  10. The next step allows you to define the width of the columns in your lookup column and to specify whether you wish the key column (the column containing the primary field key) to be displayed.   By default the key column is not displayed, and in our case we just want to view the description, so leave the tick in the box.  Now set the width of your lookup column by dragging the edge to the position you require. If you have already entered some data in the Part Types table this will be displayed to help you to adjust the column to the width of the likely contents.  Click Next.
  11. Now select the label for your lookup column.  The suggested label will usually be correct.  Click Finish to complete the Lookup Wizard.  You will be asked if you want to save the table so that relationships can be created. Click Yes.
  12. To complete the relationship, select Tools, Relationships, or click the Relationships button on the toolbar to display the relationships window. You will see the Parts table and the Part Types table with a line linking the Part Type field in Parts with the Part Type Code field in Part Types. 
  13. Right mouse over this line, and choose Edit Relationship (or double click on the line).  Tick the Enforce Referential Integrity box.  You should always tick this as otherwise the relationship has little value.   For example if you have defined three different part types in the Part Types table: E – Electronics, S – Software, H – Hardware, ticking the Enforce Referential Integrity box will ensure that you will not be able to define a new part as any part type other than these.  Also, if you try to delete a part type from the Part Types table, when parts in the Parts table have this part type, the database will warn you.
  14. Tick the Cascade Update Related Fields box. This means that you can change the primary key in the primary table (e.g. the Part Types table), and it will be automatically updated in the related table (Parts). 
  15. The third box is Cascade Delete Related Fields. Ticking this means that if you delete a record e.g. Software, from the primary table (e.g. Part Types), then any records in the related table (Parts) with that Part Type will be deleted too. Normally  you would not want this to happen – if you had parts of type Software in the Parts table then you would not want to delete that Part Type, so leave the box unticked. 

MS Access stock control relationships window
The Relationships Window in Access 2013 (click to open full image in a new tab)

MS Access relationships window
The Relationships Window in Access 2003

Forms in MS Access
In MS Access, you use forms to view, enter and edit data, and to control the database.  When you have set up all the tables and relationships in your database, the Form Wizard is very helpful in setting up forms based on your tables, for data entry, viewing and editing.  You can then make changes to the form produced by the Form Wizard, adding and editing features as required. 

Forms in MS Access can also be used to display buttons and links to provide access to all the other forms and reports.  We always set up a form of this type and call it the Front screen.  It makes it easy for people to use the system with no database knowledge.

Queries in MS Access
The query is used to extract data and information from your database.  For example, you might want to know whether you have less than the minimum stock level for any parts, so that you know when you need to order more.  You might want to know all the parts supplied by a particular supplier, or how often a particular customer ordered last year.  Often you will extract the information using a query and then use an MS Access report to present the data in an clear way.

To set up a query to show which parts are below their minimum stock level, and the suppliers from whom they should be ordered:

  1. In the Create tab, click Query Design (Access 2013/2010/2007). Or in Queries click New, then select Design View and click OK (Access 2003).
  2. In the Show Table box, select Parts. Click Add. Select Suppliers. Click Add. Click Close.
  3. The Query Design grid is now displayed with the chosen tables above. Fields to be included in the query are added by dragging them from the table to the grid, or double clicking on them.
  4. The fields we require are Part No and Stock Level from the Parts table, and Supplier Name and Address from the Suppliers table, so select these.
  5. We only want to display parts whose stock level is less than the minimum stock level for this part. This is done by setting a criterion for this field. Enter <= [MinStockLevel] in the criteria row of the Stock Level field (column).
  6. Click the X in the top right hand corner of the window to close the query. Access will ask you if you want to save changes to the query, and will ask you for a name for the query (e.g. Low Stock Levels)
  7. Now double click on the query you have just created, to view the parts with low stock levels.

Tip: Join Properties in queries are very important when your query uses more than one table. If the query does not seem to give you the results you expect, check these by right-clicking on the line joining the two tables in Design view.   Here you need to choose whether you only want to see parts that have a supplier, or all parts with low stock regardless of whether they have a supplier (“Include all records from ‘Parts’ and only those records from ‘Suppliers’ where the joined fields are equal.”), or a 3rd option that is the reverse of the above and is not appropriate here. 

Reports in MS Access
MS Access reports allow you display information to the user in a convenient way which can be viewed on screen and then printed if required. Normally the information for the report will come from a query.  The Report Wizard will help create simple reports.  More detailed reports are beyond the scope of this article.

User-Level Security
The simplest way of protecting the database is to set a password. 

  1. To set or change the password, the database must be opened for exclusive use.  To do this, open MS Access, use File, Open to select the database.  Click the Open box to the right of the Open button, and select Open Exclusive.
  2. Select Tools, Security, Set Database Password (Access 2003). Or select the Database Tools tab in the Ribbon and click on Set Database Password (Access 2007). Or go to the File menu, select the Info submenu and click on Set Database Password (Access 2010/2013)
  3. Enter the password you require, and re-enter to verify.  The password is now set.

When creating a large stock control database, or one that holds sensitive information, you may require more complex security.  For example, you might want to restrict access to some of the information in the database, or you might want to let some users view the information in the database, but not to change it.  MS Access allows you to define types of user, and apply levels of security, so that you can specify what actions are available to each type of user.  This is beyond the scope of this article, but it is called user-level security.

Automation
MS Access allows you to write Visual Basic (VBA) code which runs as and when directed.  This enables you to automate many processes.  For example, you might want your system to control your stock levels automatically whenever a delivery is received, or you might want the Customer Reference field to be built automatically from the surname of the customer.  Using Visual Basic to automate features can make using an Access stock control database much easier and simpler for its users, but it does require programming knowledge.

If you decide that building your own bespoke MS Access stock control database is not for you after all, we are happy to offer advice about alternatives via our free initial consultation.  Contact us on 01747 822616 or fill out an enquiry form here and we will get back to you.

 

Follow SoftwareMatters on Twitter
Make an enquiry
and start saving
money NOW!!
Follow SoftwareMatters on Twitter

Would you like the stock control sample that accompanies this article?

DOWNLOAD OUR FREE MS ACCESS STOCK CONTROL SAMPLE

Free MS Access Stock Control Sample Download

Can't decide between off-the-shelf or bespoke software?

DOWNLOAD OUR FREE SOFTWARE COMPARISON TOOL

Use the free softweare comparison tool now

Open large screenshot

Database Examples

Bespoke print management database

Articles

Management Software Tools

Bespoke v's off-the-shelf software

Microsoft Access 2007 benefits

Microsoft Excel 2007 benefits

Brief history of spreadsheets and Excel

CALL NOW ON
01747 822616
for a free consultation

Download Access Runtime

Visit our downloads page to Download Access runtime

NOTE: Please note that you should not install a runtime version of Access if you already have a full version installed as they may conflict. Software-Matters takes no responsibility for the contents of third-party websites.

WE BELIEVE IN:
...honesty
...integrity
...giving you the best solution quickly and cheaply
...keeping things simple
...looking after our customers

Case Studies:

Homeblue bulletAbout Usblue bulletContact Usblue bulletTerms
Bespoke Database Designblue bulletManagement Software Designblue bulletWeb Software and Designblue bulletExcel Design and Spreadsheetsblue bulletSimple Accounts Software

Valid XHTML 1.0 Transitional Valid XHTML 1.0 Transitional Valid CSS!