Creating a Product Inventory Database (Part-I)

For you to be able to do what is being spoken about in this post, you’d need to have an active MSOffice365 Professional (+) subscription in your Microsoft Account. You can head over to Office.Microsoft.com to create a new subscription, if you want, right at this time, or later, at your ease. Just in case you have come here from a search engine, this page is connected to my Facebook Page Microsoft Access – Office365.

Coming back to our post, once you fire up Microsoft Access, you should see the screen roughly like the screen below in Figure 1. In the centre of the screen, you should be able to spot an entry that says “Product Inventory”. For the purpose of simplicity, I am going to guide you through a non-complicated wizard driven creation of a database, because our focus is just to get started with the ease of creating a standard database. Later on, we will be talking about more complicated things, but for now, we need to have a standard database up and running in no time, so that we can concentrate more on the finer aspects of how to MAINTAIN a database. So go ahead and click on that option.

As the next step, you would see a screen appear that looks more or less like the one in Figure 2 below. Notice here that you are given the option of naming your database (File Name in Figure 2) and also the choice to choose the folder where you would like to store the database in your system (in my case it is “D:\Documents\Acc2k7DB” below). This is an important information that you need to remember, should you want to physically locate this file for any reason whatsoever. A word of warning, you should create all your Microsoft Access databases in just one folder in your system, especially the ones that you would be needing from various other applications, like, for example Microsoft Excel. The reason being that Access will not remember (an neither will Excel) where you stored the database should you want to remove it to another location. For the moment, go with the defaults and forget the prompt that asks “Should I create an Access App or an Access Desktop database?” – which is a more advanced topic that may be taken up later. Right, so now let us proceed to the next step (Refer Figure 3).

In Figure 3 below, which would appear as soon as you click the button “Create” that you see at the bottom of the screen in Figure 2 above, which informs you that your database has been created and is now ready to be used.

On clicking the button called “Ok” above, you should see the screen as in Figure 4 below.

Notice that there are a number of tabs that are visible, namely:

1. Dashboard (Main)
2. Products Dashboard
3. Orders Dashboard
4. Purchases Dashboard
5. Report Center Dashboard
6. Advanced Dashboard, and
7. Getting Started Dashboard

Also notice that there are two action buttons called “New Customer Order” and “New Purchase Order”. Also notice that there are two active views that are visible on the screen, the top one that says “Active Orders” and the one below that says “Inventory to reorder”. We are going to discuss all of these one by one as the next steps in this post – in the form of 11 (eleven) steps.

STEP 1 – DASHBOARD (MAIN):

This is the main dashboard screen and we are still talking about Figure 3 above. Concentrate now on the top view named “Active Orders” for the moment. Since we have opted to create the database with sample data included, as we want to understand first what are the things the default database will do, so that we will know if all our requirements from this database are met or not. So just to check, let us click the Order # 1 above. We are rewarded with the screen as in Figure 5 below – “Customer Order Form”

We are not going through many of the self-explnatory things in the above Figure, except for what happens when we click the “New” button next to the Customer Name and when we click the “View Invoice” button. What is obvious is that the customer named “Robert Tham” has 3 orders executed by an employee named “Robert Zare” and that the order is as yet not shipped. Shipment is marked as done only on clicking the “Ship Order” button. On doing so, please note that the button gets greyed out and is disabled – this is by design.

On clicking the hyperlink named “New” you would see Figure No 6.

Figure No 6 needs no further explanation, as you are required to key in information of a new customer only if you need to enter a new one, else just close the form and proceed to click the “View Invoice” button. A word of caution here, please select the row containing the invoice that you want to view, or the first available on screen will be displayed!!

At this point, you can try closing the database and then re-opening it to see the above screen.

(To be continued on 19th June 2017 – ending at 18th June 2017 at 23:51 Hours IST)

STEP 2. PRODUCTS DASHBOARD:

In order to explain the Figure 11 better, let me first explain the top dashboard menu first, as viewed in Figure 12 below:

Notice that there are six menu items on this dashboard, which are:

1. New Product
2. Inventory Levels
3. Needs Restocking
4. Discontinued Products
5. Categories, and
6. Suppliers (or Vendors)

In addition, we are presented with a view of the existing (Sample) Products that already exist in the database. We will discuss this last in this step, don’t worry. For now, let us concentrate on the above 6 tabs on the Products Dashboard. On clicking the first option, you would get the form represented in the Figure 13 below:

Please notice that we have filled up the necessary fields in the fictitious “Sample Diary Product We Want Added” to the Products Table, just so that there is clarity in understanding what we are hoping to achieve. The “Category” and “Supplier” fields are drop-down ComboBox’s that obviously would restrict your entry to the already existing items (or one that you can add e.g. “Supplier” by clicking the “New” link provided) – So the logical question that comes to my mind is this – Why you are not allowed to create a “Category” here? This is by design, because we want you to create the “Category” BEFORE you enter this screen, to avoid confusing the database, as it is a one of the required fields in the Products Table, while the supplier may change from one order to the other, the category is not allowed to be changed. Sounds logical, right?

The next step within the Products Dashboard is the “Inventory Levels” you can see in Figure 12 above, on clicking which you get the view seen in Figure 14 below:

The first thing I want to bring your attention to in Figure 14 is the Item No 46 in this list, which is what we added in Figure 13 above, namely “Sample Dairy Product That We Want Added”. Notice that all the stock levels are zero, because of that same reason – that it has just been added, and no stock existed till now and neither has a Purchase Order been placed for the same yet. Technically, you could just click the “Purchase” link visible in the last column to add the details right here and now. Let us post a hypothetical new PO as displayed in Figure 15 below right now, just to understand how this would work in the real life scenario.

So, that would logically place the order for the particular item, and our new inventory levels for it would look like in Figure 16 below.

Interesting thing to note is that while the order has been placed, the “On Hand” column is still showing “0” because the order has still not been executed, which was omitted to be done, deliberately, again for understanding the process in Figure 15 above, by clicking the “Complete Order” button provided.

(To be continued on 20th June 2017 – ending at 19th June 2017 at 12:21 Hours IST)