Introduction

Welcome! Build Components, FIFO, Margins

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 29. In this course we will continue building on prior Access inventory projects by covering product components, which allows you to track the individual items that make up manufactured products. We will also talk about FIFO (first in, first out) stock rotation, why it is important for managing inventory, and see how to handle items being removed and added back to inventory. Additionally, we will discuss calculating profit margin and markup, and I will show you how to adjust and solve for unit price using basic algebra. Prerequisite courses are strongly recommended.

Navigation

Prev: D29
Next: Lesson 01

Keywords

Access Developer, inventory database, product components, FIFO stock rotation, first in first out, profit margin, markup calculation, assemble products from components, raw materials, vendor management, stock rotation, unit price adjustment, inventory man

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 29, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are continuing on with inventory-type stuff. We are going to learn about product components. That is where you have a product that you manufacture, such as a computer system, that is made up of components that you purchase from one or more vendors. You buy the motherboard, the keyboard, the mouse, the hard drive, the case, whatever, and you assemble those and create your own product. So we will see how to handle that.

Then we are going to look at FIFO Stock Rotation - that is first in, first out. Usually, that has to do with perishable items, but even like my old business, I used to do computer sales. I did not want products sitting on the shelf too long, even though it is the same hard drive. The manufacturer's warranty might run out after a year, so I want to keep track of my oldest stock and sell those first. Most businesses want to do proper stock rotation, first in, first out. We will talk about that too.

This class does build heavily on some previous classes. Of course, my beginner, expert, and advanced series are all a must. Developers 16 on recordsets is very important. Developer 23, where I start inventory, and especially Developer 27 are very important. I am going to consider those hard prerequisites for this class, because I am going back to the database that we built starting in 23 and ending in 27 for today's database.

If you do not know how the inventory database was built from Developer 27, you might be lost today. Make sure you take Developer 23 and 27 before this course.

There it is. There is the big one: Developer 27. We are going to be using that database.

As always, I recommend you get a Microsoft 365 subscription, currently roughly equivalent to Access 2019. The techniques in today's class should work all the way back to 2007 or so. Nothing version-specific in this one.

Questions, as always, post them in the comment down below the bottom of this window that you are watching this video on. I strongly recommend you post your questions in the forum. Do not email me. I get tons and tons of emails every day. I know in my previous videos I said go ahead and email me, but it is getting to the point where I get way too many emails. So post them in the forums.

I have lots of other fantastic students. My Access veterans love helping out. If you have questions, post them there. Chances are one of them will answer your question before I do, because I get so many emails every day. I do not have time for them all.

Of course, there is always the general Access forum. You can post your questions that are not related to today's class in there.

Let us take a closer look at what is covered in today's material.

In lesson one, we are going to learn how to make components that will make up our products. For example, a computer, which is our product, can be made up of multiple components. This is very similar to the product groupings we did earlier. However, these are individual components that we buy - raw materials, for example - from our suppliers. Then we build them together to make a single product that we sell.

In lesson two, we are going back to high school. We are going to calculate the profit margin and markup. Those are simple to do. We are going to make buttons to adjust all three. If you want to say, I want to make this much profit, or this much of a margin, or this much of a markup, you will be able to type in the number. I want a 33 percent markup. Boom. But the only value that we can change is unit price. We will have to use some algebra to solve for price for each of those two equations. Then I will show you how to bring it up to 99 cents if you want to.

In lesson three, we are going to begin looking at first in, first out stock rotation, which is very important if you have perishable goods.

In lesson four, we are continuing with FIFO stock rotation, first in, first out. We are going to see what to do if someone removes an item from an order. We have to put that item back into inventory.

Quiz

Q1. What is a product component in the context of inventory management as described in the video?
A. A subcategory used to organize products by color
B. An individual item purchased from vendors used to build a finished product
C. A purchased product that is sold directly to the customer
D. A customer's return item waiting to be resold

Q2. FIFO stock rotation ensures that:
A. The cheapest items are sold first
B. The oldest stock is sold before newer stock
C. Items near expiration are always discarded
D. The largest items are shipped first

Q3. Why is FIFO important even for non-perishable goods like computer parts?
A. It keeps the store looking tidy
B. It helps sell more expensive products first
C. It helps ensure warranties do not expire on unsold items
D. It guarantees higher profit by selling new stock rapidly

Q4. Which previous Developer classes are considered hard prerequisites for this course?
A. Only Developer 16 and 18
B. Beginner, Expert, and Advanced series, plus Developer 16, 23, and especially 27
C. Only Developer 27 and 28
D. Only Advanced levels and no developer classes

Q5. What version of Microsoft Access is recommended for this class?
A. Access 2003
B. Microsoft 365 subscription (roughly equivalent to Access 2019)
C. Access XP
D. Access 97

Q6. Where should students post their questions according to the instructor?
A. By sending a letter to the instructor's office
B. In the forum under the video window
C. By calling the instructor directly
D. By emailing the instructor at any time

Q7. What will lesson one primarily focus on?
A. Creating a sales invoice form
B. Making and managing product components from supplier purchases
C. Setting up email alerts for low inventory
D. Designing marketing campaigns for products

Q8. The calculation of profit margin and markup, as discussed in lesson two, involves:
A. Calculating taxes for each transaction
B. Solving algebraic equations for unit price based on a desired margin or markup
C. Estimating shipping costs based on product size
D. Using macros to automate product ordering

Q9. In FIFO stock rotation, what happens if an item is removed from an order as described in lesson four?
A. The item is deleted from the inventory permanently
B. The item is restocked and returned back into inventory
C. The item is returned to the customer
D. The item is transferred to another warehouse automatically

Q10. Which of the following statements is TRUE about posting general Access questions?
A. They should be emailed directly to the instructor
B. They should only be posted under the current lesson's video
C. They can be posted in the general Access forum
D. They are not allowed at all

Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-C

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Developer Level 29, and I am excited to continue working on our inventory system. For those of you following the series, we will be expanding our database to handle product components. This is especially useful if you are involved in any kind of manufacturing. Imagine you are building your own products, such as assembling computers. Each finished product consists of parts you purchase individually, like motherboards, keyboards, mice, hard drives, and cases, all coming from one or multiple vendors. We will cover how to manage those connections within your database.

Another key concept in today's class is FIFO stock rotation, which stands for "first in, first out." Although you usually hear about FIFO with perishable goods, it is also important for non-perishables like computer hardware. For example, if you buy hard drives in bulk, you want to sell the oldest ones first, since warranties often only last for a year. Proper FIFO rotation ensures your stock does not sit too long, and that you do not accidentally sell old products after their warranties have expired. We will cover how to implement that logic in your Access database.

This class builds heavily on several previous courses. If you have not been through my beginner, expert, and advanced series, I strongly advise completing those first. In addition, Developer 16 is especially important, because it introduces you to recordsets, and you will need a solid grasp of those for what we are working on now. Developer 23 is where we began the inventory tracking system, and Developer 27 contains the database we will be using today. Think of Developer 23 and 27 as prerequisites: if you are not familiar with the inventory tracking system we set up in those videos, you might get lost in this lesson. Please make sure you have completed those courses before proceeding.

We are using the Developer 27 database as our starting point today, so make sure you have access to it before moving forward.

As always, I recommend using a Microsoft 365 subscription, which is essentially the same as Access 2019. Everything I show you should also work back to Access 2007, as there is nothing in this lesson that depends on a specific version.

If you have questions, I recommend posting them in the forum underneath the video window rather than emailing me directly. My inbox is overflowing, and I simply cannot keep up with all the individual emails. Besides, there are many seasoned Access students and veterans in the forum who are happy to help and might answer your question even faster than I can. For questions not related to this class, feel free to use the general Access forum.

Now, let me take you through today's material, lesson by lesson.

In the first lesson, we will discuss how to handle component products, which are items you purchase from suppliers to assemble into a finished product. For example, when you create a computer system, your product consists of a set of components - the raw materials purchased from your vendors - that combine into the single product you eventually sell. This is related to product groupings, which we previously discussed, but here we are focusing specifically on the individual components that make up a finished assembly.

The second lesson shifts gears a bit, taking us back to concepts from high school math: margin and markup. We will review how to calculate both, and I will show you how to build command buttons in your database to adjust all three: profit, margin, and markup. You will be able to specify, for example, that you want a 33 percent markup. However, the only field you can change directly is the unit price, so we will do some algebra to solve for it based on your selected margin or markup. I will also cover how to round your price up to end in 99 cents, if you prefer.

For lesson three, we will get into the details of FIFO stock rotation, which is a must for anyone dealing with perishable goods or products with warranties. We will begin putting the logic in place to track which items arrived in your inventory first and ensure those are the ones sold first.

Lesson four continues with that FIFO theme, addressing what to do when an item is removed from an order. In that case, you will need to put that item back into inventory, which introduces a few more complexities to our inventory tracking.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Creating product components for inventory
Assembling manufactured products from components
Managing components purchased from vendors
Tracking raw materials in inventory
Calculating profit margin and markup
Creating buttons to adjust profit, margin, and markup
Solving for unit price based on margin and markup
Rounding prices up to 99 cents
Implementing FIFO stock rotation
Handling removal and return of items to inventory
Managing first in, first out for perishable goods

Article

In this tutorial, we are going to explore some advanced inventory management techniques in Microsoft Access with a focus on product components and FIFO (First In, First Out) stock rotation. These concepts are essential if you are building a database for a business where you manage inventory, manufacture products from various parts, or need to keep an eye on stock rotation for warranty or perishable goods. I will show you how to set up your tables and forms, work with profit calculations, and handle inventory adjustments using VBA.

Let us start by discussing product components. Imagine you manufacture computers. You do not sell just the parts; you buy motherboards, keyboards, mice, hard drives, cases, and other components from different vendors, then assemble them into the final product you sell. To manage this in Access, you need a way to track both the final products and the separate components that go into each one.

Typically, you will have a Products table and a Components table. To specify which components go into which products and in what quantities, use a junction table, such as ProductComponents. This table might have fields like ProductID, ComponentID, and QuantityRequired. For example, to build Computer Model A, you might need one motherboard, two RAM sticks, one hard drive, and so on. Each row in the ProductComponents table would reference the main product, the component, and how many of that component are required per finished product.

When you make a sale or assembly, you can use this structure to subtract the correct amount of each component from your inventory automatically. If you build one computer, you subtract one motherboard, two RAM sticks, etc., from your stock. This system allows you to keep track of raw materials on hand and ensures you have enough inventory to build your products.

Now, let us take this a step further and talk about profit margin and markup. These are essential calculations to know how much you are making on each product. The profit margin is the percentage of the selling price that is profit. For example, if you pay $100 for a part and sell it for $150, your profit margin is (150 - 100) / 150 = 33.33 percent. Markup, on the other hand, is the percentage increase from your cost to the selling price. In this case, the markup would be (150 - 100) / 100 = 50 percent.

Let us say you want to let the user quickly adjust the selling price based on a desired margin or markup. You can use a simple form in Access and some VBA to accomplish this. For example, if you have fields for Cost and Price, create textboxes for Margin and Markup, and buttons to recalculate the price based on either.

Here is a simple function in VBA to update the unit price based on a target margin:

Private Sub SetMargin_Click()
Dim cost As Currency
Dim margin As Double
cost = Me.Cost
margin = Me.Margin
Me.Price = Round(cost / (1 - margin), 2)
End Sub

If you want to round the price up to the nearest 99 cents, you could add:

Me.Price = Int(Me.Price) + 0.99

Similarly, here is how you would calculate the price for a given markup:

Private Sub SetMarkup_Click()
Dim cost As Currency
Dim markup As Double
cost = Me.Cost
markup = Me.Markup
Me.Price = Round(cost * (1 + markup), 2)
End Sub

This way, the user can enter a target markup or margin, click a button, and the correct unit price will be calculated and displayed right on the form.

Next, let us discuss FIFO stock rotation. FIFO stands for First In, First Out. This method is important for any business that wants to make sure the oldest inventory is sold first, which is especially key for perishable goods or for managing items with warranties that start when you receive them. For example, if you bought ten hard drives in January and another ten in March, when you sell a hard drive, you want to sell the ones that have been in stock the longest, so you do not get stuck with expired warranty periods or aged products.

To implement FIFO in Access, you generally need a way to track batches of inventory with their arrival dates or lot numbers. This means creating an InventoryBatches table where each batch of incoming items is recorded along with the quantity, arrival date, and possibly the purchase price.

When you fill an order, you write VBA code to pull quantities from the oldest batch first. Here is a basic pattern for this kind of VBA logic:

Dim QtyNeeded As Integer
Dim rs As DAO.Recordset

QtyNeeded = Me.QuantityToShip
Set rs = CurrentDb.OpenRecordset("SELECT * FROM InventoryBatches WHERE ProductID = " & Me.ProductID & " AND QtyOnHand > 0 ORDER BY ArrivalDate")

Do While QtyNeeded > 0 And Not rs.EOF
If rs!QtyOnHand >= QtyNeeded Then
rs.Edit
rs!QtyOnHand = rs!QtyOnHand - QtyNeeded
rs.Update
QtyNeeded = 0
Else
QtyNeeded = QtyNeeded - rs!QtyOnHand
rs.Edit
rs!QtyOnHand = 0
rs.Update
rs.MoveNext
End If
Loop

rs.Close
Set rs = Nothing

This code loops through the inventory batches for the given product, using up the oldest stock first until the order is filled or inventory runs out.

But what happens if an order is changed and you need to put an item back into stock? You then add the returned quantity back to the correct batch, ideally to the batch with the earliest arrival date that still has room, or simply create a new batch entry for the returned item with the return date as its arrival.

With this system in place, you will always know which items to pick for each order to ensure proper stock rotation and accuracy in your inventory. This structure also allows you to track inventory costs and profits by lot, since some inventory might have been purchased at different prices.

As you can see, with a little setup in your tables and some straightforward VBA, you can create a robust inventory system in Microsoft Access capable of handling product components, profit calculation, and accurate FIFO stock rotation. By breaking down products into their essential parts and carefully managing the flow of stock, you will have precise control over your inventory, costs, and sales, leading to better decision-making for your business.