|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Welcome Welcome! Build Components, FIFO, Margins 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. NavigationKeywordsAccess 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
More InformationTranscriptWelcome 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. IntroWelcome 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. QuizQ1. 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. SummaryToday'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 ListCreating 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 Primary Topicsproduct components, inventory management, FIFO stock rotation, profit margin calculation, markup calculation Secondary TopicsMicrosoft 365 version compatibility, database prerequisites |
||
|
| |||
| 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 PermaLink How To Build Product Components, Manage FIFO Stock Rotation, and Set Margins in Microsoft Access |
