Database Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesDatabase Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old February 1st, 2007, 11:41 AM
billynastie billynastie is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2007
Posts: 1 billynastie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 15 m 4 sec
Reputation Power: 0
Sql Database help

Hi im new at this so any help would be of use.

i have created the following table into mysql
Products
Purchase Orders
Dispatch

What i require is for somebody to point me in the right direction to execute the follow.

I need to input and amount into the field Qty Rec in the purchase orders table which also inserts the same amount into the field stock level on the products table and add it to the existing figure which is already there and deletes the amount from the field qty ordered in the purchase orders table and also from the fields on order from the products table.

I need to input an amount in the fields QTY Ordered on the purchase orders table which insert the sam amount and add it to the existing figure in the field on order from the products table.

And lastly I need to input an amount in the field qty sold on the dispatch table which the takes that amount off the figure which is in the fields stock level in the products table.

And help would be most appreciated on this.
email

Thanks in advanced.

Reply With Quote
  #2  
Old February 5th, 2007, 03:03 AM
stallyon stallyon is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jan 2007
Posts: 31 stallyon User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 45 m 33 sec
Reputation Power: 12
you'd be best off using code rather than update or append queries.

what front end program are you using to access the tables?

Reply With Quote
  #3  
Old January 11th, 2011, 12:42 AM
fdtoo fdtoo is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Sep 2004
Posts: 3 fdtoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 21 m 38 sec
Reputation Power: 0
I am putting my suggestion here although this an old thread, in anticipation of someone looking for some feedback on this particular issue.

To iterate what the book on accounting database design (from smashwords) has taught, the way to handle inventory, is to set up a stock_movement table and a stock balance table. The stock movement table will store all your product activity (namely receipt and purchase). The stock balance is used to store the stock quantity balance and costing mechanism, which would help in automating the costing of cost of goods sold for each product sold.

Your Oty Rec should not be created in your purchase order table, it should be created in the stock movement table. Likewise, Qty Ordered should not be deleted from your purchase order table, as this conflict with accounting practices.

Good Luck

Reply With Quote
  #4  
Old March 17th, 2011, 02:30 AM
alexsmth114 alexsmth114 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Mar 2011
Posts: 10 alexsmth114 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 h 17 m 20 sec
Reputation Power: 0
(I am putting my suggestion here although this an old thread, in anticipation of someone looking for some feedback on this particular issue.

To iterate what the book on accounting database design (from smashwords) has taught, the way to handle inventory, is to set up a stock_movement table and a stock balance table. The stock movement table will store all your product activity (namely receipt and purchase). The stock balance is used to store the stock quantity balance and costing mechanism, which would help in automating the costing of cost of goods sold for each product sold.

Your Oty Rec should not be created in your purchase order table, it should be created in the stock movement table. Likewise, Qty Ordered should not be deleted from your purchase order table, as this conflict with accounting practices.

Good Luck) Well said bro, I agree with you!!..

Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesDatabase Development > Sql Database help


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap