How should I create a transactions table?

Copper Contributor

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: 

  • Roster - All technicians available to be issued serialized items
    • Issued to "Tech ID"
  • Inventory - All serialized items available to issue
    • Issued from "Warehouse ID" 
    • Ideally I would like this inventory to be separate due to a power query I'd eventually like to set up that allows me to import directly instead of copy/pasting all serial numbers
  • Transactions - A list of items from "Inventory" joined with "Roster" to create a transaction log of what/where/when

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:

 

  • I need to issue several items at a time with a barcode scanner, so I cannot choose a serial number from a combobox and update the information from the form
  • I am unsure if creating a transactions log is practical
  • I am unsure how to properly set up table relationships

 

My questions are:

 

  • What is the best way to approach this to get the result I'm looking for?
  • If the best way to do this is to have a "Transactions" table, how can I use a form to write new records while also updating records in the "Inventory" table?
  • How should these tables be related?

 

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.

1 Reply

@JL2022 

Your first concept is almost correct
AA -->AutoNumber
1st Table
Items
ID(AA:PK) , Product,[Other details like S/N,descriptions...etc]
2nd Table
Roster
ID(AA:PK),Technician
3rd Table
Transactions
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