For those who want to start programming of simple applications for data management, the best tool, in my point of view and from my experience is Microsoft Access ™. Obviously this is not the cure for all ills, but the beginning is the perfect platform to learn the structure of the tables and how is it created a database for all your needs. In this series of lessons that I am about to write, I will show you how to begin to create small applications for inventory items, which are the most sought after and even easier to do (of course the basic of inventory management). Do not expect me to do, however, that the whole application ( that is Calus software ), but I want to give only the foundations and individual building blocks for the walls, the rest you have to put you with a lot of goodwill. For those unfamiliar with the environment the Access board to follow the courses they need to teach the structure of tables with indexes and links, the SQL language, and then the Visual Basic for Applications (VBA) is used to handle events of our form. Find everything you need here on this blog, just look!
When you create a new application that is based on data management, database structure is essential to ensuring the success of the software. Let’s study our problem and go to create the tables in our database.
We will definitely need a table for our stock items, one more for the units to be connected in article movements and ultimately our customers and suppliers. As for the schedule of items we should include fields such as: internal code or serial, description, bar code, units, price, vat and little else. In the picture below you can see the structure of what I created:
As you can see I added a counter with auto-increment because this way we will have each inventory item identified by a long integer. If you see further down you’ll see how he entered the field “IDUM” a long integer that is not only the unique identifier of the unit of measurement. The table of units then the counter will have the initials of 3-4 characters (kg., gr., etc.) and wanting a description. Minimum stock and taxes are both single-precision is good for this type of values, in fact in this program all the quantities are expressed in a single floating point but double would be fine, even if it takes twice the memory.
Let us then see how they are structured tables of customers and suppliers, I created two tables, but you could also add a boolean to identify whether customers or suppliers and manage only one table, for simplicity, in this tutorial we will work with two:
As I said above, the fields are equal and could only manage a table with a boolean value: positive and negative customer if the supplier. We will follow this path, but the more willing you could use the second. Within these tables, there are fields that are linked to other tables by a unique identifier ( I always put “ID” before the name of this type of camp). To get an overview of the database on which we are going to work, here is the structure:
For the time included only the tables that I pointed out, and those connected, others, such as movements, will be created as they progress in lessons.