May 31 2022 07:20 PM
May 31 2022 07:20 PM
I'm relatively new to Access and only vaguely familiar with SQL and VBA.
I'm attempting to create a simple warehouse management system for a small business. So far I've had success with this database for simple tasks such as managing employees through forms, queries, and tables, but my biggest challenge yet is the actual management of the inventory. I'm trying to track serialized inventory, as my usual software is now being phased out. I have a small inventory in the warehouse and my technicians keep some on their vehicles. Altogether at any given time it hovers around 1,000 total items. These items are serialized and are "single use", so I will most likely never see the same serial number twice. What I would like to do is create a transactions table that shows when an item was issued to a technician, and create a history of this item. The fields I need in this table are "Serial Number", "Item Type", "Date Added", "To Tech", "From Tech", and "Transferred By". I have attempted it so far by using 3 tables:
Another thought I had is to simply have one table with all inventory items available to issue, and then create a query to update the items based on the serial number entered in the [Serial_Number] text box on the "Transactions" form. However, in practice I don't see how this could work in real time. The issue I am having is that when these items are issued it has to happen quickly, as I generally issue items in bulk and up to 50 items at a time.
My issues are:
My questions are:
I realize this question may be vague in some ways, I do apologize, but I am nearly at my wits end trying to figure this out. At any given time I've had 10+ tabs open with Google searches and video tutorials, and I've even purchased a course on Udemy to better understand Access, but it doesn't seem like the resources I have are ever trying to solve *this* problem. I would really appreciate some insight into this, and thank you for your help! Please let me know if there is more information I can provide that may give you some context. Thanks again.
Jun 03 2022 04:15 AM
Your first concept is almost correct
ID(AA:PK) , Product,[Other details like S/N,descriptions...etc]
ID (AA:PK), ItemID,RosterID,Start_Transaction,End_Transaction
Essentially the available Items are the Items that are not in an unfinished Transaction
I am attaching a small database to take a look