help w/ query... inventory deduction by lot

[b_c]

alright...

...by now you probably have guessed (from all my other posts) that i'm building a small pos system for a friend that talked me into it w/ some scooby snacks.

anyway...he had one last request:

he has the same product set up at 3 different registers w/in his store. for security reasons, he's labeled these locations w/ "lot" indicators, so a unique invID can have upwards of 4 lots available w/ a TotalRun tracking all lots added together.

so...here's what it looks like in my table:

InvID
OrigQty
BackStockRunQty
ARunQty
BRunQty
CRunQty
TotalRunQty


on the sales side, he wants to be able to select the lot that he's selling from and have it deduct from the appropriate column in the InvID row.

example:

selling
invID: 1
lot: A (could also either be B, C, or BackStock) <---this is what's getting me, i can deduct if this wasn't in there and just dealing w/ invID.
qtySold: 20


^^^ how would i use a query to update the appropriate cell?


please help...this is truly one of my last hurdles.



cheers,

b_c

 

[Galaxiom]

Ah.... the old "last request" that ideally requires a different table structure. The pain probably won't stop there if you let this one by.

Unless you want to use VBA to construct the query that deducts the quantity you will have to have a box on the form for each lot field and enter the quantity to be taken from each. Then hide the boxes that don't apply to the current workstation.

I expect you have already been told why deducting at each transaction is not a reliable way to maintain stock quantities.

You probably have already been berated enough for the normalisation faults in your table too, so I won't go there either.

 

[b_c]

Ah.... the old "last request" that ideally requires a different table structure. The pain probably won't stop there if you let this one by.

Unless you want to use VBA to construct the query that deducts the quantity you will have to have a box on the form for each lot field and enter the quantity to be taken from each. Then hide the boxes that don't apply to the current workstation.

I expect you have already been told why deducting at each transaction is not a reliable way to maintain stock quantities.

You probably have already been berated enough for the normalisation faults in your table too, so I won't go there either.

i see what you're saying...i'm willing to learn if you're willing to teach...

1. i'm not against using vba
2. i'm open to another way for maintaining stock qty's
3. normalization? i understand it (or i thought). what's a better way?


thanks, glaxiom...



regards,

b_c

 

[b_c]

i see what you're saying...i'm willing to learn if you're willing to teach...

1. i'm not against using vba
2. i'm open to another way for maintaining stock qty's
3. normalization? i understand it (or i thought). what's a better way?


thanks, glaxiom...



regards,

b_c

#3
...so would it be better if i had a table per LOT? like i said, i thought i had a grasp on "normalization", but i guess i'm boogering it up somehow.


...and i think you're right glax...i have a feeling this won't be the "last request" lol...



any guidance would be appreciated. if i didn't have separate lots and was running only originalQTY and runQTY would i still have a normalization prob? i've created a model w/ those two fields and the update query performs like a charm when the sales side runs. (builds an SO, first, then inventory deducts as a batch when register completes trans).



cheers,

b_c

 

[Galaxiom]

The value in the total field in the table can be derived from the other fields hence is a normalization anomaly.

Putting the same kind of information into different fields often leads to problems with queries having to be directed to different fields such as you have described. Generally the way around this is to store the quantities for each Lot in the same field but as different records. Another field designates the LotID of the records.

This way the record to be updated is selected by the LotID associated with the terminal while the affected fields are always the same so don't require a dynamic query. Totalling stock for all Lots is simply the sum of the quantity field with records grouped by ItemID.

It is best not to adjust the stock quantity at every invoice because this can lead to errors particularly in multiuser environments. Calculate the stock quantity by maintaining an opening stock figure, adding the purchases and subtracting the sales. This is much more reliable and eliminates the need to update the stock table at every invoice.

Update the opening stock figure periodically using the same calculation technique. The date of the update is also recorded and the on the fly stock quantity calculation uses this date to determine how far it has to count back through the invoice and purchase history.

The frequency of the update depends on the size of the operation but can be done during the end of day process with nobody in the system. Otherwise update the figure to some time in the recent past so that a new transaction can't slip in during the calculation.