Simple Store Keeping Inventory Database (1 Viewer)

[techeart]

I am trying to create a simple store keeping In and Out inventory database using Access, I thought I had made it but looks like I am missing something here, if anyone could help please.

The store works on SRV (Store Receiving Voucher) and SIV (Store Issue Voucher). Products will be added based on SRV and will be issued out based on SIV. So far I have created the tables as you can see in the figure. One thing I am not understanding is where to keep the record of the Current Quantity of each product, lets say an Item has been added or issue out, it should be added or deducted accordingly from that specific products overall quantity. Right now I have a sample field within products table as you can see with the name QtyOnHand but that doesn't seem to be logical. Thanks.

 

[mousemat]

Qty on hand should be a calculated field, based upon a query which will add up all of the SRV's and deduct all of the SIV's to give you the qty on hand figure

 

[techeart]

Thanks for the quick response. Correct me if I am wrong but that query would then needs to be run every time manually in order to fill the calculated field? Also if you can be a little more detailed on what exact calculation to perform on that field?

Qty on hand should be a calculated field, based upon a query which will add up all of the SRV's and deduct all of the SIV's to give you the qty on hand figure

 

[mousemat]

As a very quick setup i have created a small database based on your dataset.

Very simply, create a query to give you a sum of products IN, another query for products OUT and a third to give you the qty on hand.

its not a pretty database, just something i managed to do in my lunch break, have a look at it though and see where it fits in with your structure.

 

[techeart]

Hey thanks a lot, I think that will do it. I will check and apply it to my own db and upload later if you could check it in your dinner break maybe haha. Thanks again

 

[mousemat]

Glad to be of help. Yes, i'd be happy to check it for you.

Like i said, it's a very dirty databases i have provided, but gives the idea.

Calculated totals are best displayed on forms which then become more relevant to what is happening.

 

[techeart]

Hi, and thanks.

Here is a db I created in which I want to insert Items to SRV and Issue using SIV, but the problems I am facing are below:

1: Records are added nicely to tblSRV and tblIN using frmSRV-SIV but when I try to add fields to the tab page SIV in frmSRV-SIV, it asks me how does the SIV table relates to SRV, well they don't relate so they should be separated. I am assuming it won't allow both of the tables to be added on the same form? If so then they can be on different form no problem.

2: The database you provided gives sum of the total in and out for products but I want is that there will be an initial total quantity or current qty for each product which has to be added to that sum of In and Out. It should pull that initial qtyonhand and add all the ins and outs to it but also not removing the previous qtyonhand because I would also be creating monthly report based on date, how much qty was available for this specific product until end of month. Right now there is an excel sheet which provides for each product beginning inventory of the month + total In last month - total Out last month = ending inventory. This cycle repeats each month as beginning inventory to be the ending inventory of last month and so on. I want to achieve this same thing using this db. Also the qtyonhand I assume should be changed to totalqty, added to another table with a specific date field?

Sorry if any of this doesn't make clear sense but I am attaching the db I created if you can check it for me?

 

[techeart]

Anything yet?