Stock update from MS access form (1 Viewer)

[Shiva Kemkar]

Hello,
I am responsible for managing the sample stock and finding it difficult to find what sample went out and when and which sample came in to store.
want to create a access data base to record the information.

I have created a table with the fields below for material entry.
Lab_Sample_ID
Supplier
Sample_Name
Sample_QTY

every time the samples are out, i should be able to record the information using form and reduce the sample_QTY and the total stock to reduce.
similar way to update the stock if the material is in to store,

I have 2 more tables created to record the out and in of the material
Lab_Sample_ID
Sample_Qty_out; Date

Lab_Sample_ID
Sample_Qty_In; Date
Kindly share some code which can do this. Thanks

 

[Galaxiom]

Combine In and Out into the same table. Record Out as a negative number.

When you need to know the quantity on hand, simply query the Sum of the movements in an aggregate query grouped by the SampleID.

This is easier and far more reliable than incrementing and decrementing a quantity.

 

[arnelgp]

If you are unable to combine... you can call an update query

Update materialTable set sample_qty = nz(sample_qty, 0) + dsum("nz(sample_qty_in,0)","tableIn","sample_id=""" & sample_id & """") - dsum("nz(sample_qty_out,0)", "tableOut","sample_id=""" & sample_id & """")

 

[Shiva Kemkar]

Hello Galaxiom,

yes, i can group In and out table. aggregate query, just saw few examples on google. As i am new to access programming, unable to understand the trick.

Can you support here pls. may be a sample code with major steps.

 

[Minty]

Galaxiom is saying only have one table.

tblSampleMovements - fields;
TransactionID, QtyInOut , TransactionDate

In-bound stock would be a positive number, out-bound stock would be a negative, you stock level is the sum() of those transactions.

 

[arnelgp]

For combined transact table:

Update materialTable set sample_qty = nz(sample_qty, 0) + dsum("nz(InOutField,0)", "transactTable", "sample_id=""" & sample_id & """")

 

[Galaxiom]

Yes. What Minty is saying.

Don't go down the track of storing the quantity at all. It usually goes wrong.

Often another field will be included to indicate the type of movement for transactions. For example stock adjustments for discarded or lost items.

Putting domain functions into queries is not a good idea.

 

[arnelgp]

using negative value is not a good idea. Ive never seen such real world app that uses that.

 

[Galaxiom]

using negative value is not a good idea. Ive never seen such real world app that uses that.

Having never seen something done does not necessarily make it a bad idea. I have also seen many things done in real world apps that can objectively be shown to be bad ideas, like putting domain functions in queries.

There are many ways to deal with stock movement transactions and they all involve trade offs.

Another more common way is to use the MovementTypeID field I mentioned previously. A code designates movement In or Out so the numbers are all positive. The disadvantage is that the movements have to be grouped and Outs subtracted instead of simply summing the field.

 

[Shiva Kemkar]

Hello arnelgp, Galaxiom,

i am trying with your suggestions. since things are new i am taking more time to fix it on my screen.
will post if i am unable to fix it on my own.

arnelgp, like before i may ask some basic questions again, so please be kind to me
Thanks both

 

[arnelgp]

I am always kind to the OP but not to those who think themselves as the absolute.

 

[Shiva Kemkar]

Hello,
I now have the basic structure in access. please see attached snapshot.
i was actually trying to follow the comments posted before. but did not understand.
please let me know as how to start from here.

i should be able to register the new sample with qty, then reduce the qty if the samples are out and also add when the sample is back.