Bought Vs Used calc in Access

[omer1234]

We Buy stuff from different places, so ............　
Table Basic..................It keeps a track of how much we bought (Start)　
Table Consumed ................is created from where my employees can go and enter How much they used of what.........Qty....ConsumeF (form)　
　
I would like Form Consume F to only show How much is left of what ingredient to consume............So picture 1 (Start) - Picture 2 (Qty)　
　
　
　
Notice, when there are similar DDDate, how do I hndle the situation and make it unique in my form?.....DDDate reflects the expiration date or code from supplier so I dont want to change it...........In my form can I do 2 drop downs? (First pick DD date and then pick FirstN)　
It seems simple but I could use your help　
Thanks

 

[plog]

You need to step back and not worry about forms for now, you have a serious table issue. You tables simply aren't set up properly to track your inventory.

Credits and debits to inventory need to be stored in the same field. You have that data not only in 2 different fields, but in 2 different tables. Adding to that issue is that you are tracking your inventory at 2 different levels--credits (addition to inventory) are stored granularly--you know exactly where each unit comes from. However, your debits (removal from inventory) are tracked categorically--once units are in inventory they are all treated the same.

My first question is do you need to track credits so granularly? Do you really care where each unit comes from? If so, that's fine, you will just need more complex table system.

Ultimately you need to end up with a table that has a structure similar to this:

tblInventory
inv_ID, autonumber, primary key
ID_Product, number, foreign key to product table
inv_Date, date, date inventory transaction occured
inv_Qty, number, represents how many units of Product was transacted (negative numbers denote debit from inventory)

With a table like that, determining inventory is a simple Totals Query.

 

[omer123456]

Hi, yes I do need to track where everything's consumed

So if there is a better way, I am open to it

I have x lbs of starting inventory ..... let's say 100 lbs
I tell my employees to use 2 lbs in batch

They actually use 5 lbs in a batch ..... because they don't always follow instructions

I am left with 95 lbs


That's what I am trying to do, please feel free to post an attachment, I would love to see how

Thx in advance

 

[The_Doc_Man]

You can find many posts with many attachments if you use the Search feature of the forum to find articles on "inventory" - I'm guessing a few dozen major posts with general discussions and a LOT more posts with detailed but minor point discussions.

 

[gemma-the-husky]

inventory is straight forward in some instances, and not so in others.

if you need to trace how you issued a given consignment then it's not easy at all. Inventory is generally based on stock items being homogenous. This is what plog just explained.

ie. you have 100 widgets, and but 200 more, so you now have 300 widgets. It doesn't matter which widgets you issue from stock. All you are bothered about is the total.

on the other hand, if you need to know that the widgets were from the old consignment of 100 widgets, rather than the new shipment of 200, then it's very difficult.

Your first post read as if you needed the latter idea.

 

[omer123456]

Any solutions from anyone?.....Come on guys take the challenge

 

[plog]

Using your sample data in in post #3 and my solution in post #2 and assuming we are talking about wheat. This is what it would looke like:


tblProducts
Product_ID, Product_Name
17, Wheat

tblInventory
inv_ID, ID_Product, inv_Date, inv_Qty
1, 17, 1/1/2017, 100
2, 17, 3/28/2017, -5


Then to get your current inventory its this simple SQL:

SELECT Product_Name, SUM(inv_Qty) AS Inventory
FROM tblInventory
INNER JOIN tblProducts ON Product_ID = ID_Product
GROUP BY Product_Name

 

[omer123456]

Can you post a sample d/b? So I can see it live

Thx a lot

 

[plog]

No, sorry.

 

[gemma-the-husky]

http://access-templates.com/access+...ompany+inventory+management+database-562.html

try this excellent old MS inventory database example.