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!!
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!!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
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.
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.
Sorry, first sentence should state, the question here is pretty simple.
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.
(That's how my Point of Sale works)
Scott C.
ASKER
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.
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.