This article gives an example of the answer to the question:
What is Microsoft Access used for?
Microsoft Access is used for creating databases that are software tools to help businesses manage their work and assist with their admin tasks. Access is the database application within Microsoft Office. Stock control systems are one example of what Access is used for. The database will contain details of the items being stocked, their current stock levels, and movements into and out of stock. The stock control system will allow you to track where your stock has come from, where it has gone, and to manage your stock levels. Microsoft Access is frequently used to create a stock control database system.
How to create a stock control database
This article provides a walk-through for creating a Microsoft Access Stock Control or Inventory Management Database. If you want to read about how to create your own stock control database - also known as stock management, inventory control, inventory management - then read on for detailed explanations and step-by-step instructions for how to do this using the commonly-available database package, Microsoft Access (part of Office 365).
Above you will also find a link to our YouTube video which walks you through the same process, if you would prefer to receive the information that way.
In this article we'll guide you through some step-by-stepinstructions for creating your ownstock management database or inventory control system in Microsoft Access.We have also included some hints and tips based onour many years of experience creating Access databasesfor real businesses. To download the database we'll be making in thistutorial for free, click on the link above. You can also follow thistutorial using our YouTube video that explains each step and providesdemonstrations to help you do it yourself; click on the link above toopen the video in a new tab/window. This article references using Access 365/Access 2019 throughout, although the principles apply toearlier versions as well. You can see the earlier 2010 version of thispage here.
We also have a follow-up tutorial with an additional free database download, which walks throughMany-to-Many relationships and how they can be used in stock systems, so why not take a look at this after you have finished this tutorial? The Many-to-Many relationships tutorial also has a related YouTube videowhich walks you through the same process.
FREE ACCESS STOCK CONTROL DATABASE SAMPLE DOWNLOAD
Before you get started
Please note that in order to follow this walk-through, or run the freefinished database linked above, you must have a full version ofMicrosoft Access installed on yourcomputer.
It might sound obvious, but one of the most important points whencreating yourstock control database is deciding exactly whatyou need it to do, and what it is for.Getting the designright is much easier once you understand the purpose of thedatabase.A well-designed database will be simpler tomaintain, and to adapt later on if your requirements alter.
For example, the simple database weare creating needs to be able to:
- Log orders from customers
- Track stock/inventory levels
- Warn when parts need to be re-ordered
Think about the details of the way your business works. Doeseach customer order one type of item at a time, or do your customersorder a range of parts or items from you all at the same time?Do you have just one supplier for each part you stock/in your inventory?Do you order a range of items from each supplier or does eachsupplier just provide you with one type of item? Your designwill also be different if you stock items for manufacturing rather thanto sell on. Keep the answers to these questions in mind as we go intothe next step.
Define the tables required
Informationin a database is held in tables. By this point you should have someidea of theinformation the database needs to hold to achieve yourgoals.You need to categorisethis information into a set of tables.To start with you don’teven needto do this in Access, just make a list by hand or in another program.
The tables within our example database are: Parts, Customers, Orders, PartTypes,PurchaseOrdersand Suppliers.Tokeep things simple, we aregoing to assumethat each customer orders just one part at a time, and that we orderjust onepart from a supplier at a time. Of course this might not be true foryou; yourcustomers may order a whole selection of items together, and you willprobably buya range of items in one go. In this case you’ll need a table that holdsall theparts linked to each order, probably called Order Items.
We’ve made asimilarsort of assumption about customers, assuming they are all individualpeople orsmall companies; for large business customers with multiple addressesor phonenumbers to store you would perhaps need a more advanced set of tablesto holdall the information consistently. But all this is a more advancedtopicreally, so we’ll just show you the simple way for now using tables withmorebasic goals.
Please note that we do have a further article and video about this more advanced topic - known as many-many relationships.
Set up fields within the tables
Within a table, information is held in “fields”.Basically a field isthe specificpiece of information about the thing the table is responsible for.Typicalfields in the Parts table for example might be PartNumber,PartDescription,Supplier and StockLevel. If you picture a table as a tabular grid,then thefields would be the column headings, with each row representing anentry in thetable, or record.
Alltables should have a unique identifying field called the primary keythatcannot be the same for any two records or ever be empty. So in ourparts table,the PartNumber is unique for each part and every part has one so wecan justuse that. But for other tables where this may not be the case, we caninvent IDnumbers or codes for internal use in the system to make sure it canuniquelyidentify any entry in any table. In Access there is something called anauto-numberfield that you can use as the primary key if there is no other obviouschoiceor preference, which just assigns a new sequential number to eachrecord in thetable.
For each field in the table, you need to pick a data type to show thetype ofdata it will hold, such as Number, Text, Date/Time or Currency. Withineachtype you can further specify the exact nature of the data, such as thenumberof characters for a Text.You might already use part numberswithin yourbusiness, and the format you use is likely to help you decide the datatype forthe field Part Number. Your part numbers might be something likeACBD2222, in which case you might choose to use an8-character textstring.
Nowwe’regoing to imagine that our business deals with large numbers ofdifferent partswhich we classify into different types.We have a tablecalled PartTypes, which lists the different types of Parts. We want tomake our Partstable have a field that can link to the PartTypes so that eachpart can beassigned a type. The field in the parts table needs to the same as theprimarykey field (the thing that identifies the record to the system) in thePartTypestable, which we’ve made a 1-character code. The Parts table would alsoberelated in a similar way to the Suppliers table, so that you can findout whosupplies a particular part.
Making our design on paper.
Thinkabout how you will be using the fields, and make sure you define themin themost logical way for your purposes. For example, it can make sense tostorepeople’s names as First Name and Surname separately rather than as onefield sothat you can easily sort and list names in alphabetical order (ofsurname). A tip we find useful is to hold postal addresses asone field,rather than split them into individual elements of the address such asAddress Line1, Address Line 2, Town, County and Postcode. This makes itmuch easierto incorporate addresses into forms and reports, and it eases dataentrybecause Access is happy to store the multiple lines in one field.
Whilethinking what fields you need, you should make sure they all haveunique names,unless two fields actually contain the same information. Only in thiscaseshould you give them the same name, like with PartTypeCode earlier.Somethingto keep in mind with your names is that if you want to progress tousing SQLqueries or Visual Basic for Applications (VBA) code with your database,youwill findlife easier if you have no spaces in the table names or field names. Sothat iswhy we’ve been writing PartTypes as one word, rather than having thespace.
Anothertip to keep in mind is that is it badpractice to give a field a name that is already being used behind thescenes byAccess for something else. These so-called ‘Reserved Words’ includethings like‘name’, ‘date’, ‘level’ and ‘currency’, among many others. You can look upa fulllist of Access’s reserved words online to make sure none of your fieldsuse one.This can help avoid confusion in the database engine between predefinedwordsand your field names, which if left unchecked can sometimes causeseriouserrors.
Create yourtables in Access
To create a Parts table and define its fields:
- Click on the Create tab on theRibbon, then on Table Design. This opens a new table in designview. Each row in design view represents a field in thetable.
- Click in first column, top row.
- Enter the name of the first field (PartNo)
- Use the Tab key to go to the next column where we definethe data type. Click the drop-down and select Text.
- Use the Tab key to go to the next column, and enter adescription for the new field.
- As this will be the primary key for the table, click theprimary key button on the toolbar.
- On the General tab, at the bottom of the window, click inthe Field Size row, and enter 10 to define a text string with 10characters.
- On the General tab, click in the Caption row, and type thelabel you want for this field on-screen (e.g. Part Number).
- Click in the second row to define the next field in thetable. And so on.
- When you have finished adding fields to the table, close itby clicking on the X in the top right handcorner.Accesswill ask you to name the table. Enter “Parts”.
The Parts table of our example stock control database (inventory database)contains the following fields:
10-character text string
Unique part number to define each part
50-character text string
Name/description of this part
20-character text string
The unit of measure for this part e.g. Kg, Meters, boxof 10
6-character text string
The supplier of this part
The quantity of this part currently in our stock or inventory
The minimum number of this part in stock (inventory) before you need to re-order
The cost price of this part
The sale price of this part
1-character text string
The type of part this is
255-character text string
Tip : it is often useful to include a Notes field forany other information that you might want to add later.
The Parts table in Design View (Access 2019/365)
The screenshot above shows the Partstable from our stock control database in Design view. UsingDesign View enables you to define all the fields in your table, specifytheir Data Types, describe them and define their format.
Tip – Do complete the Description of the field. When you usethe Form Wizard to create forms for you later, this description will bedisplayed 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 captionwill then appear on forms produced by the Form Wizard. Forexample if your field is called POQty, enter Quantity in the caption.
The Customers table is as follows:
8-character text string
Unique 8-character reference for this customer based onfirst 4 letters of surname (e.g. SMIT0001). This will make iteasy to find individual customers. This field is the primary key.
20-character text string
The first name of this customer
25-character text string
The surname of this customer
255-character text string
The address of this customer
15-character text string
The telephone number of this customer
The Customers table above is designed for abusiness whose customers are predominantly individuals as assumedearlier.Whenyour customers are mainly larger businesses, and you may have severalcontacts at each business, or each business might have several sites,then you will need a more complex solution.
Once you've got the hang of it, go through the rest of yourplannedtablessetting them up too. You’ll see your new tables appearing in the objectbrowser onthe left as you make them (press F11 to bring this browser up if youcan't see it).
Relationships are set up within the database, to show the way in whichone table relates to another. A one-to-many relationship isthe most common kind of relationship. In this relationship, arecord in one table can have more than one matching record in a secondtable, but each record in the second table can have only one matchingrecord in the first table. For example, each Part can haveonly one Part Type, but for each PartType there are likely to be manyparts of that type.
If each part has only one supplier as in our example, thenthis is another straightforward one-to-many relationship. If each partcan be supplied by several different suppliers, then you will need adifferent design.
In our example database, the followingrelationships between tables are required.
Suppliers - Parts, to specify the supplier of each part.
Parts - PurchaseOrders, 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.
PartTypes - Parts, classifying each part into a particular part type.
Asanexample we’ll show you how to set up the relationship between thetables Partsand PartTypes. Before you start doing relationships it is a good ideato writesome sample information into your tables that features entries which areas longas you think you’ll ever use. This will help out a little with somesettingsyou’ll need to adjust later.
- Set up the fieldPartTypeCode in the Part Types table asa single-character text string defining the part type.
- Make this field theprimary key
- Open the Partstable in Design view.
- Add a fieldPartTypeCode to the Parts table. Make sure itis also a single-character text string.
- Now click in theData Type column of the Part type field todisplay a down arrow. Click this to display a drop-down list, andselect Lookup Wizard.
- Select “I want thelookup column to look up the values in atable or query”. Click Next.
- From the list oftables displayed, select the PartTypestable. Click Next.
- Click the fieldsyou want included in your lookupcolumn. In this case, we will select both fields. Click Next.
- A sort order can beselected if required. SelectDescription. Click Next.
- The next stepallows you to define the width of the columnsin your lookup column and to specify whether you wish the key column(the column containing the primary field key) to bedisplayed.By default the key column is notdisplayed, and in our case we just want to view the description, soleave the tick in the box.Now set the width of your lookupcolumn by dragging the edge to the position you require. If you havealready entered some data in the PartTypes table this will bedisplayed to help you to adjust the column to the width of the likelycontents. Click Next.
- Now select thelabel for your lookup column. Thesuggested label will usually be correct. Click Finish tocomplete the Lookup Wizard.You will be asked if you want tosave the table so that relationships can be created. Click Yes.
- To complete therelationship, select Tools, Relationships,or click the Relationships button on the toolbar to display therelationships window. You will see the Parts table and the PartTypestable with a line linking the PartType field in Parts with thePartTypeCode field in PartTypes.
- Right mouse overthis line, and choose Edit Relationship(or double click on the line). Tick the Enforce ReferentialIntegrity box. You should always tick this as otherwise therelationship has little value. For example if youhave defined three different part types in the Part Types table: E –Electronics, S – Software, H – Hardware, ticking the EnforceReferential Integrity box will ensure that you will not be able todefine a new part as any part type other than these. Also, ifyou try to delete a part type from the PartTypes table, when parts inthe Parts table have this part type, the database will warn you.
- Tick the CascadeUpdate Related Fields box. This means thatyou can change the primary key in the primary table (e.g. the PartTypestable), and it will be automatically updated in the related table(Parts).
- Thethird box is Cascade Delete Related Fields. Tickingthis means that if you delete a record e.g. Software, from the primarytable (e.g. PartTypes), then any records in the related table (Parts)with that part type will be deleted too. Normallyyou wouldnot want this to happen – if you had parts of type Software in theParts table then you would not want to delete that part type, so leavethe box unticked.Therewill probably be examples in your database where you do want to ticktheCascade Delete Related Records box.Itnormally applies when one table forms supplementary information foranother –for example if you had Orders and OrderItems tables (listing multipleitemson anOrder), then you would want to delete all OrderItems if you deleted anentire Order.
The Relationships Window in Access 2019/365
Sonowyou’ve seen how to set up a relationship, you should now go through thetablessetting up the relationships you had planned in your design. Onceyou’ve donethem all we can move onto the final part of our database.
Creating a reorder query
Ingeneral queries are used to extract data and information from yourdatabase.In our example we want to know whether we have less than the minimumstocklevel for any parts, so that we know when you need to ordermore. You canextract all sorts of other information with queries though. You mightwant toknow all the parts supplied by a particular supplier, or how often aparticularcustomer ordered last year. Often you will extract theinformation usinga query and then use an Access report to present the data in a clearway, buthere we’ll just be doing the query.
Sonow let’sgo through how to set up a query to show which parts are below theirminimumstock level, and tell us the suppliers from whom they should bereordered.
To set up a query to show which parts are below their minimumstock level, and the suppliers from whom they should be ordered:
- In the Create tab, click Query Design.
- In the Show Table box, select Parts. Click Add. SelectSuppliers. Click Add. Click Close.
- The Query Design grid is now displayed with the chosentables above. Fields to be included in the query are added by draggingthem from the table to the grid, or double clicking on them.
- The fields we require are PartNo and StockLevel from theParts table, and SupplierName and Address from the Suppliers table, soselect these.
- We only want to display parts whose stock level is lessthan the minimum stock level for this part. This is done by setting acriterion for this field. Enter <= [MinStockLevel] in thecriteria row of the Stock Level field (column).
- Click the X in the top right hand corner of the window toclose the query. Access will ask you if you want to save changes to thequery, and will ask you for a name for the query (e.g. LowStockLevels)
- Now double click on the query you have just created, toview the parts with low stock levels.
The query design grid in Access 2019/Access 365
Thequeryshould be visible in the object browser on the left. Double click onthe queryto view the parts with low stock levels once you have some working datain thesystem.
One morething about the query: the lines between the tables in the querydataset arecalled Joins. Joinsareautomatically created between tables when there are fields that alreadyhave arelationship between them orbetween a primary key and another field with the same name. Usuallyyou would want a join here,but there will be cases where you don’t want to join these fields forvariousreasons, so keep in mind that you may need to check all the joins oncethey arecreated.
JoinProperties in queries are very important when your query uses more thanonetable. If the query does not seem to give you the results you expect,checkthese by right-clicking on the line joining the two tables in Designview.Here you can choose whether you only want to seeparts that have asupplier, or all parts with low stock regardless of whether they have asupplier (by picking “Include all records from ‘Parts’ and only thoserecordsfrom ‘Suppliers’ where the joined fields are equal.”). You might wantto usethis option in this example. The 3rd option is the reverse of this, soshow allsuppliers even if they don’t supply any low stock parts, which in thiscasewouldn’t make any sense, so just ignore that one.
Sonow wehave completed the database as per our design. We can enter all thedata weneed and it will tell us when to reorder products. Of course thepotential usesof a database are many, so now we’re going to discuss briefly a fewways youmight want to expand on this database to make it more useful and moreuser-friendly.
Next Page >
If you decide thatbuilding your own stockcontrol databaseis not for you after all, we at Software-Matters are happy to offeradvice aboutalternatives via our free initial consultation.Contact us on01747 822616 or fill in an enquiryform here and we will get back to you.
If you enjoyed this article or found it useful, why not tell others about it?
Software-Matters is a UK software development team specialising in Microsoft Office and based in Gillingham, Dorset, and close to the county borders of Somerset, Wiltshire and Hampshire. Nearby cities include Bournemouth, Poole, Southampton, Bristol, Bath and Salisbury.