Inventory to Point of Sale DB 1

[jestrada101]

Hello,
Could someone guide me to where I can join a point of sale table(e.g. Order Details Table that maintains how many items have been sold in QTY) and an Inventory table that maintains how many items are in STOCK or on order... If someone could just explain how this works, I could take it from there.. Having a hard time seeing how they interact.

Thanks!
je

 

[koresnordic]

I assume you have the item in both tables. Just use the relationship screen to join the two item codes together.



Graham

 

[jestrada101]

Thanks. Is there a way for the INVENTORY table to update based on what has been ORDER or RETURNED... or would this be something that the actual code need to do?

Thanks!
JE

 

[koresnordic]

You would need to write some code or an update query to do this. There would need to be a flag on the transaction to say that it has been actioned, or the transaction goes into a seperate table and the stock is updated as a batch.



Graham

 

[jestrada101]

Thanks.. this is what I was looking for.

JE

 

[danvlas]

Inventory table...should not exist...
Suppose you have Purchases table and Sales/Orders table, both containing the product code.

Total purchases by product MINUS total sales by product will give you the inventory on the fly.

Returns can be assimilated as sales of negative quantities.
Claims can be assimilated as purchases of negative quantities.

Inventory-to-date: create a PurchasesToDate query and a SalesToDate query. Join them by product code and you have the InventoryToDate query.

I admit...it will run a little slower. But the advantages are huge, as all data will reconcile without needing code for updates, additions or deletions. No matter who and how updates data, the result will be correct (if the data entered into the tables is correct).

Updating tables, especially in a multi-user environment, can be a pain. Why not just adding data? that poses far less problems...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[jestrada101]

When you say.." Why not just adding data? that poses far less problems..." What do you mean by this?

Thanks!
JE

 

[danvlas]

Well, just add regular sales and purchase data. Mostly invoices and invoice details.

Updates are limited to ONE user at a moment.
Additions are allowed for more users at a time without one stepping on otherr's toes...


Everything (OK, almost everything) in between can be retrieved by queries.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[ZmrAbdulla]

Hi danvlas,

Can I ask you a doubt?
I have a database that keeps record of Items Purchased and Issued. Both are related and Item Name is called as per entry from another table Items.
My Inventory is as you said all Purchases MINUS all Issues.
The probelm is I am unable to get the record of any item that is not issued but purchased. This means I should have entries in both records.
How can I get the record of the items purchased not issued in the same query?

Thanks
Zameer

 

[MichaelRed]

To slightly disagree, in a busy environment, the 'slightly' slower can easily become a bottle neck. In retail sales, customers get enough frustration to cause them to take the business elsewhere, Adding a second (or so) to each item transaction when customers may have numerous items and many customers are 'in the que' can easily cause the customer to shop elsewhere.

Further, 'real time' rolling inventory is not all that useful. If the customer has it 'in hand' and wants to purchase it, you would be somewhat nuts to say (with a smile?) "sorry but that item is not in stock, so I cannot sell it to you". In many (most?) retail situations oders are based on mechanical verification of incentory, not just a calculated value. "Losses" occur from several sources, so the perpetual inventory is often in error. To use it as THE basis for purchases is an un-common practice.

In systems I have been invloved in, each POS terminal maintained the daily transactions, which were used for batch updates to the master inventory at the end of the day.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Golom]

Another thing to think about (as if you don't already have enough) is that "in stock" is not necessarily just "purchases - sales". In the real world there are stock takes and stock adjustments. For example, if you bought 20 units; sold 5 but have only 10 remaining in stock then you need to apply a stock adjustment. That creates a discontinuity in the nice, neat "purchases - sales" math. The other problem is that, if you go the "purchases - sales" route then you need to keep all historical purchase and sale information in the active database. In large volume operations that can amount to multi-MB or GB of data and the processing to get stock levels goes from "slight delay" to "sometime next week."

In short, even though the data purist may say "never store computed results" ... the realities of real world mandate (in this case at least) that you maintain "in-stock" levels as separate stored values.