Hello, I know it seems too much to read but i really need help and i really appreciate your time and effort!
I'm currently designing a database for a small designing and printing business, i did all my tables and linked them with needed relationships.
My problem is: The products i have in the products table involve the use of material ofcoarse, to take an example, i have posters as a product it involves the use of paper and ink and lamination, those are the materials, I cant find a way to link those together so that when a product is ordered by a client the material used are deducted from the number in stock.
What i need: - Help in a way that the "Quantity" Entered in the "Selling Transaction Form" Is deducted from the the "number in stock" in the "Material's Table", which also appears in the "Stock order Form"; - Also, the "Quantity ordered" from supplier in the stock details Table to be added to the "number in stock" -Finally i want a message to alert to the need of ordering stock , and if possible use mail merge to automatically create messages to suppliers using details from the supplier's table: this is when the "number in stock" <= to the "reorder level" in the material's table.
I already posted my problem previously and thanks to my answerer (Sinndho) I got a detailed description of the solution but it involved the use of the SQL in which i dont have the slightest knowledge!! this is the SQL i was asked to use :
1) Is an item simple or composite.
Composite = (SELECT COUNT [SysCounter] FROM [Composition] WHERE [ItemID] = <some ItemID>) > 1 ' Returns 1 if it's a simple item, >1 if it's a composite item.
2) Retrieve all simple items and their quantity for a composite item (should be a recursive function as a composite item can be made of other composite items).
RecordSet = (SELECT [ElementID], [Quantity] FROM [Composition] WHERE [ItemID] = <some ItemID>) ' the query returns 1 or more ItemID's
For Each ElementID In Recordset.ElementID
subRecordSet = (SELECT [ElementID] FROM [Composition] WHERE [ItemID] = ElementID)
' We end up with a list of the primary keys of all simple items that compose a composite item
' or just one primary key if the item we are analyzing is a simple item.
(author: 08-12-09, 00:29 Sinndho Registered User Join Date: Mar 2009
so if anyone can explain what this means and how exactly i can do it i would really appreciate it !!