Inventory Database

Hello, I have a problem with the database I am creating.  I have a list of parts that I need to keep and inventory on.  My problem comes when I am trying to figure out how to show a transaction (issue a part to someone) and then get the qty of the equipment decreased by the qty issued. Also to throw another wrench into the mix, my Items table can have for example: 3 Monitors @ $150 each and then 2 Monitors @ $200. The difference would come from the monitors being purchased at different time and the amounts are different. So, when for example I issue 4 monitors to a dept. I need to know how much to charge them and then deduct the qtys from my inventory.  I Items will be distributed from oldest to new, so that will determine the order they will be issued in. I have two tables:

tblItems
-------------------
ID - autonumber
Item - text
qty - number
cost - currency
datepurchased - date/time
category - text (which is the type of item)

tblTransfer
-------------------
ID - autonumber
Dept - text (who was issued the item)
qty - number (the number recived)
datetransfered - date/time

I hope this is enough info the get you started.  Thanks for the help!!
LVL 1
BCUSTAsked:
Who is Participating?
 
Marc333Connect With a Mentor Commented:
BCust,
I think there are a few different ways to go about this.  My first idea/suggestion is to take your transaction table and break it up into two tables.  The first will be to store the primary information, such as transaction ID, Transaction date and department.  The second table will house all of your detail.  Then you will link the two tables by the Transaction ID.  Additionally, you'll get much of the detail for the items from your tblItems table.  So for example:

tblTransfer
ID: 1234
Dept: Admin
DateTrans: 4/1/06


tblTransferDetails
ID: 1111 <- this ID is unique to this transaction Detail
TransID: 1234 <- this ID links this tranaction detail to the main transaction
Item: Moniter
InvID: 54321  <- ***
itemcost: $200

ID: 1112 <- this ID is unique to this transaction Detail
TransID: 1234 <- this ID links this tranaction detail to the main transaction
Item: Hard Drive
InvID: 54322  <- ***
itemcost: $150

The form that will be used to create your transactions should have the Transaction information up at the top with a subform in the middle to house all the detail.  

*** To make sure you can update your inventory, you'll need to link your detail table to the tblItems.  You'll want to possibly use a dropdown field for Item (in the subform), then use some code to find the oldest one of that kind of item, and then input the ID of that item into the InvID.  When you're user pushes the button to update the system, you'll use the InvID to go through your tblItems and delete that item from the Inventory.  That way your inventory stays up-to-date and you'll be able to use a query to compute the inventory totals.  





 
0
 
clarkscottCommented:
I write Point of Sale software and, as Marc333 states, you will require 2 tables.  A transaction Header table and transaction Detail table.  The header data is the date, time, department.  The detail is the item, price, etc.

I expect you have some sort of RECEIVING process.  If not, you need to keep track of what's being received in similar fashion.  A Receive Header (date, time, from where, etc) and a Receive Detail (item, your cost, etc.)

Simply query the Receiving Tables for what's been received (sum queries) and the same for Transaction tables (sum queries).  Using these 2 tables will help you track your inventory.

In the 'old days' (before super fast PCs) I'd suggest maintaining an 'Inventory Count' field in your inventory table.  This field would be updated after each 'receive' and 'sold' transaction.  But the programming is 'busy' since mistakes can cause this value to become unreliable - and you can 'sum query' large data pretty fast now days.


Scott C.
0
 
BCUSTAuthor Commented:
Marc333
I do like the idea with the two tables. It does make the overall function better. I still have a problem, the way I have setup my received items is say when I get 2 monitors in at once I input that into one record with the qty = 2. Now I don't know if i will be able to leave it that way or not.  The only other way I can see the working is if I input each piece of equipment as a single record. What are your suggestions?
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Marc333Commented:
Well, there question there is pretty simple.  In your details table, do you need to break down the cost of each monitor or can you just have a summary?  Put another way, after inputting the data, will you ever need to come back to a transaction and know, this transaction had two monitors, one that costs $150 and one that costs $200... OR is it enough to say, this transaction had two monitors and those totaled $350.  If you need to know the breakout, then I suggest having each item as a seperate line item.  And generally, I do prefer this in most cases unless a transaction and have large numbers, say 5, 10 or 20 monitors.  This could definately become frustrating to your users.  

In that case, I would use some recordset code to create a sorted query by date, filtered for the object you're adding, in this case, monitors, sorted by the oldest date.  (if you're not sure how to do this, let me know and I'll post some example code).  Then go through and add up the oldest monitors for the number you need and add this total to the subform for the total amount.  

The biggest concern with this is validating that you delete out the monitors you added up when you update.  But, you could probably validate this by making sure that the total costs of the monitors deleted matched the total in your subform.  





0
 
Marc333Commented:
Sorry, first sentence should state, the question here is pretty simple.  
0
 
clarkscottCommented:
Even with a QTY field of more than 1 (2, 5, 25).... a sum query will add this value correctly.

(That's how my Point of Sale works)

Scott C.

0
 
BCUSTAuthor Commented:
After some more looking into this database, I am going to have to put each new item in a seperate line. The reason for this is so I can track my serial numbers on the items to ensure that we are getting rid of the oldest items from the inventory and not just the database. By addind the items this way I think I should be able to get what I need accomplished. I am pretty much use what marc333 had to say the first time. Thanks for all the Help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.