How to Create a Stock Management Database in Microsoft Access – Full Tutorial with Free Download

an agent who maintains an inventory from which he or she buys and sells securities is called a:
This is a topic that many people are looking for. https://granthamandira.org/ is a channel providing useful information about learning, life, digital marketing and online courses …. it will help you have an overview and solid multi-faceted knowledge . Today, https://granthamandira.org/ would like to introduce to you How to Create a Stock Management Database in Microsoft Access – Full Tutorial with Free Download. Following along are instructions in the video below:


in this video we will guide you through step by step instructions for creating your own stock management database in Microsoft Access weve also included some hints and tips based on our many years of experience creating stock management databases for real businesses there is a free download of the database well be creating in this video available on our website see the description below for the link note that in order to follow this tutorial or use the free sample you must have a full version of Microsoft Access installed on your computer in this video well be working in Access 2010 but the methods will be broadly the same in other versions although in Access 2003 and older the interface will be significantly different well also be assuming a passing familiarity with how Microsoft Access works step 1 make a plan it might sound obvious but one of the most important points when creating any database is deciding exactly what you need it to do and what its for a well-designed database will be simpler to maintain and to adapt later on if your requirements alter and getting the design right is much easier once you understand the purpose of the database in this tutorial well be using the example of a database managing supplies of parts we want it to be able to perform three main tasks log orders from customers track our stock levels and warn us when parts need to be reordered there are other questions to ask yourself regarding the specifics of how your business works does each customer order one type of item at a time or do your customers order a range of items from you all at the same time do you have just one supplier for each item you stock do you order a range of items from each supplier or does each supply just provide you with one type of item your design needs will also be different if you stock items for manufacturing rather than to sell on keep the answers to all of these questions in mind as we go through the next step step two define the tables required information in a database is held in tables by this point you should have some idea of the information the database needs to hold to achieve your goals you need to categorize this information into a set of tables to start with you dont even need to do this in Access just make a list by hand or in another program each table should deal with information related to just one area or subject for example a table for information on customers should contain no information on sales but of course data within one table is related to data in other tables using relationships that you define allowing information on sales to particular customers to be accessed later on the tables within our example database are parts customers orders part types purchase orders and suppliers to keep things simple were going to assume that each customer orders just one part at a time and that we order just one part from a supplier at a time of course this might not be true for you your customers may order a whole selection of items together and you will probably buy a range of items from your suppliers in one go in this case youll need a table that holds all the parts needed for an order probably called order items weve made a similar sort of assumption about customers assuming theyre all individual people or small companies for large business customers with multiple addresses or phone numbers to store you would perhaps need a more advanced set of tables to hold all the information consistently but all of this is a more advanced topic really so well just show the simple way for now using tables with more basic goals step three invent fields within the tables within a table information is held in fields basically a field is the specific piece of information about the thing the table is responsible for typical fields in the parts table for example might be part number part description supplier and stock level if you picture a table as a tabular grid then the fields would be the column headings with each row representing an entry in the table or record all tables should have a unique identifying field called the primary key that cannot be the same for any two records or ever be empty so in our parts table the part number is unique for each part and every part has one so we can use that but for other tables where this may not be the case we can invent ID numbers or codes for internal use in the system to make sure it can uniquely identify any entry in the table in Access there is something called an auto number field that you can use as the primary key if theres no other obvious choice or preference which just assigns a new sequential number to each record in the table for each field in the table you need to pick a data type to show what type of data it will hold such as a number text a date or time or currency within each type you can further specify the exact nature of the data such as the number of characters for text you might already use part numbers within your business and the format you use is likely to help you decide the data type for the fields part number your part numbers might be something like this in which case you might choose to use an eight character text string now were going to imagine that our business deals with large numbers of different parts which we classify into different types we have a table called part types which lists the different available types we want to make our parts table have a field that can link to the list of part types allowing us to assign a predetermined type to each part the field in the parts table needs to be the same as the primary key field the thing that identifies the record to the system in the part types table which weve made a one-character code the parts table would also be related in a similar way to the suppliers table so that you can find out who supplies a particular part 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 can make sense to store peoples names as first name and surname separately rather than as one field so that you can easily store and list names in alphabetical order of surname

or first name a tip we find useful is to hold postal addresses as one field rather than split them into individual elements of the address such as address line 1 line 2 Town County and postcode this makes it much easier to incorporate the addresses into forms and reports and it eases data entry because access is happy to store the multiple lines of the address in one field while thinking about what fields you need you should make sure they all have unique names unless two fields actually contain the same information only in this second case should you give them the same name like with Parts type code earlier on something to keep in mind with your names is that if you want to progress to using SQL queries or Visual Basic for applications code with your database youll find life easier if you have no spaces in the table names or field names so thats why weve been writing part types as one word rather than having the space another tip to keep in mind is that it is bad practice to give a field a name that is already being used behind the scenes by access for something else these so called reserved words include things like name date level and money among many others you can look up a full list of Accesss reserved words online to make sure none of your fields use one this can help avoid confusion in the database engine between predefined words and your field names which if left unchecked can sometimes cause serious errors step four making the tables in access now we know what we want to store its time to open up access and make somewhere to store it well show you how to make a table and its fields using our parts table in the sample database as an example first open up access and create a new database were using the 2002-2003 file format .MDB this will allow certain extra features for our database that cant be used in the newer accdb format which we shall detail later on it will start with a table already open use the button in the top left to switch it to design view naming it parts when prompted along the way for the rest of your tables you can open new tables in design view via the table design button in the create tab in design view each row you see represents a field in the table start in the first row by entering the first field name which for us is part no press tab to move into the data type column which for us is going to be a 10 character text string so for now we pick text now we can tab into the next column and add an optional description of the field which will appear in accesss status bar in the bottom left when the field is used in the future so its good to provide a reminder of what is meant to be entered this part no is going to be the primary key for this table so we need to click on the key icon in the ribbon to establish this now we can get into the field properties at the bottom of the screen Im going to set the size of my text field to 10 characters since that is the maximum size I want to allow I can also add a caption that can have the field name but using spaces part no this is what will actually appear to the user when using the table later so you should give everything a caption to help describe the field if the actual field name doesnt make it clear enough so thats all for the first field now I can go through adding all the fields I planned then save the table once Ive got everything from my design set up when there are multiple fields that will be the same thing and hence should have the same name in your design as mentioned earlier make sure they have the same data type and size in all cases as this will make setting out relationships a little easier later on once youve got one table set up you can go through the rest of your planned tables setting them all up too youll see on new tables appearing in the object browser on the left as you make them step 5 set up relationships relationships are set up within the database to show the way in which one table relates to another a so called one-to-many relationship is the most common kind in this relationship a record in one table can have more than one matching record in a second table but for each record in the second table there can only be one matching record in the first table for example each part can only have one part type but for each part type is likely that there will be many parts of that type if each part has only one supplier as in our example then this is another straightforward one-to-many relationship in our database the following relationships between tables are required suppliers relates to parts so as to specify the supplier of each part parts relates to purchase orders to show the part ordered on a purchase order parts relates to orders to show the part ordered on an order customers relate to orders to show the customer for each order and part types relates to parts since were classifying each part into a particular part type as an example well show you how to set up the relationship between the tables parts and part types before you start throwing relationships its a good idea to write some sample information into your tables that feature entries which are as long as you think youll ever use this will help out a little with some things youll need to adjust later on first make sure you have set up the field part type code in the part types table as a single character text string and at this field is the primary key now open the parts table in design view if you havent already add a field part type code to the parts table and make sure it is also a single character text string now in the data type column of this part type field click on lookup wizard select I want the lookup column to look up the values in a table or query since well be looking at values in the part types table then click Next from the list of tables displayed select the part types table and click Next again on this screen you pick the fields you want included in

your lookup column if you pick more than one thing it still means only one piece of information will be stored in the field but other information will be shown while you are picking it in this case well select both fields were going to store the code while showing the full type name to help people pick the right one click Next to continue to a sort order screen for us only the actual type description is going to be visible so well sort by that 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 youve already entered some data into the parts types table as we suggested earlier this will be displayed to help you adjust the column to the width of the likely contents once youve chosen a width click Next now select the name for your lookup column youll want to call it by the information actually being stored in there rather than what is displayed so here Ill leave it as part type code click finish to complete the lookup wizard youll be asked if you want to save the table so that relationships can be created go ahead and do that there is actually one more thing to do though select tools in the ribbon then 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 code filled in parts with the part type code field in part types if you dont use the add tables button to get both of these tables into the diagram right click this line and choose edit relationship or double-click on the line in the pop-up that appears tick the enforce referential integrity box you should almost always tick this as otherwise the relationship has little value for example if you have defined three different part types in the parts 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 thats been assigned to a part already and hence is in use by the parts table the database will warn you youll also want to tick the Cascade update related fields box this means that you can change the primary key in the primary table which is the part types table here and it will automatically be updated in the related table the parts table here the third box is cascade delete related records ticking this means that if you delete a record eg s for software from the primary table then any records in the related table the parts with that part type will be deleted too normally you would not want this to happen so leave the Box unticked there will probably be examples in your database where you do want to tick the Cascade delete related records box it normally applies when one table forms supplementary information for another for example if you had orders and order items listing multiple items on an order then you would want to delete order items if you deleted an order so now youve seen how to set up a relationship you should go through the table setting up the relationships you had planned in your design once youve done them all we can move on to the final part of our database step 6 create a reorder query in general queries are used to extract data and information from your database in our example we want to know whether we have less than the minimum stock level for any parts so that we know when we need to reorder more you can extract all sorts of other information with queries too 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 access report to present the data in a clear way for here well just be doing the query so now lets go through how to set up a query to show which parts are below their minimum stock level and tell us the suppliers from whom they should be reordered in the create tab click query design we need to choose which tables we want to query information from in this case we know that well need information on the stock level of parts which is in the parts table and details of the supplier for those parts which is in the suppliers table adding these tables to the query is easy in the show table box select parts click Add to include that table in our query select suppliers and do the same 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 in the tables we only need to include fields that contain the information we want to output in our query so lets say we want our query to be a list of part numbers with their stock level plus the supplier the part is from and the address for that supplier the fields we require are part number stock level from the parts table supply name and the address from the suppliers table so select all of these youll see them appear in our query at the bottom now 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 less than or equal to min stock level in the criteria row of the stock level field now the query will only display rows where this is true since Im not actually going to be displaying the minimum stock level I dont need to add it to the query fields it just needs to be somewhere in the table that are the data source for my query 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 your changes and will ask you for a name

query Ill call mine low stock levels again having no spaces in the name can help later if developing the database further the query should be visible in the object browser on the Left double click on the query to view the parts with low stock levels once you have some working data in the system one last thing about the query the lines between the tables in your query data set are called joins joins are automatically created between tables when there are fields that already have a relationship between them or between a primary key and another field with the same name usually you would want to join here but there will be cases where you dont want to join these fields for various reasons so keep in mind that you may need to check all the joins once they are created 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 can choose whether you want to see only 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 you may wish to go for this in this case the third option is the reverse of this so show all suppliers even if they dont supply any low stock parts which in this case wouldnt make any sense so just ignore that one so now we have completed the database as per our design we can enter all the data we need and it will tell us when to reorder products of course the potential uses of a database are many so now were going to discuss briefly a few ways you might want to expand on this database to make it more useful and more user-friendly forms in access you use forms to view enter and edit data and to control the database when youve set up all the tables and relationships in your database the form wizard is a very helpful tool in setting up forms based on your tables for data entry viewing and editing the data you can then make changes to the form produced by the form wizard adding and editing features as required forms can also be used to display buttons and links to provide access to all the other forms and reports this allows you to build a user interface for your system we always set up a form of this type and call it the front screen setting up a clear top-level form like this makes it easy for people to use the system with no database knowledge here are a few examples of complex forms used in our commercial databases reports Microsoft access reports allow you to 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 you create simple reports for a variety of purposes an example of a useful report in our database could be a purchase order document to send to suppliers with details of an order detailed reports like this are beyond the scope of this video but are certainly something to look into if you want to bring the data from your database back into the real world in a presentable form security you might want to make sure that no one who isnt trusted can tamper with your data the simplest way of doing this is to protect the database with a password to set or change the password the database must be opened for exclusive use to do this open up access then use file and open to select the database click the arrow box on the right of the Open button and select open exclusive once opened go to the file menu again and then select the info submenu and click on set database password 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 from the database but not be able to change it if you followed our example from the beginning and saved your database as a dot MDB file rather than the newer accdb format 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 again beyond the scope of this video but its called user level security if you want to look for more information automation access allows you to write custom code in its visual basic for applications language known as VBA you can set up a piece of code to run when you perform certain actions or just on command this enables you to automate many processes for example you might want your system to adjust your recorded 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 VBA to automate features can make using an access database much easier and simpler for its users but it does require programming knowledge so its a more advanced step to take in your databases development so thats all for this tutorial hopefully well have helps you setup your simple database ready to start putting it to use the database weve shown you how to make is probably too basic to use for long even for a small operation this system is a building block upon which many more tables relationships and user interfaces can be laid to construct a complete piece of stock management software of course if youve decided that building your own stock control database is not for you after all we at Software-Matters are happy to give advice about alternatives via our free consultation offer contact us on 01747 822616 or fill in an inquiry form on our website linked in the video description and well get back to you otherwise we wish you the best of luck using and maybe further improving your new stock control database if you want more Excel access and VBA tips then subscribe to this channel and if you found this video helpful please leave a like or a comment to let us know thanks for watching

tags:
How-to (Website Category), stock database, how to use access, access tutorial, stock control, how to make a stock control database, Microsoft Access (Softwar…
Thank you for watching all the articles on the topic How to Create a Stock Management Database in Microsoft Access – Full Tutorial with Free Download. All shares of https://granthamandira.org/ are very good. We hope you are satisfied with the article. For any questions, please leave a comment below. Hopefully you guys support our website even more.

Leave a Comment