Can I find anyone here to help me?

[Accountant]

Hello my friends .
This is my first participation in this great forum .
I created a program to my jop, working according to the cost accounting system


First:
This program is for small factory, manufactures door and window . The factory plan is: Three stages pass
The first stage :
Purchase of raw materials Such as (all kinds of wood - nails - glue - other materials).
The second stage : Is the manufacturing process Consisting of :
1- Raw materials purchased.
2 - Administrative expenses required for the manufacturing process.
3- Forming wood in other factories if necessary.
After the manufacturing process, it is sold directly to the customer or displayed at the factory exhibition , This is the third stage
The problem I can not solve is the second stage
I worked group tables to my knowledge
1- table : Add_doc .. Purchase invoice header
2- table : Produce_doc …Manufacturing invoice head, this invoice was used to control the withdrawal of materials from the warehouse .
3- table : issue_doc ... Head of sales invoice after production .
4- table : Movements … Table of movement of raw materials inside and out of the warehouse.
5- table : Forming … This is to record the cost of forming wood in other factory so that it is added to the cost of the product .
6- table : Administrative_expenses … To record administrative expenses during the work of the plant to be added to the cost of the product as well .
7- table : Customers … Customer registration .
8- table : Suppliers … Suppliers registration .
9- table : Names … Registering the names of the items only .
If there is an adjustment in a table you can do this or create a new table. Important I want to help solve the problem of programming the second stage, which is manufacturing. Thank you very much

[Accountant]

This move materials from the first factory entry until production The Word file attachment explains this

[Accountant]

problem
table Purchases bill and sales bill and Production linked Movements
Worked forms Purchases To record purchases and form RetPurchases To record purchase returns and form Production To record the raw materials needed for production and at the same time to record the quantity of products to be produced and from salee To record sales and form Resales To record sales returns
Because of this I consider that I deal with a single table that duplicates data, especially quantities, and the balance appears wrong
Tables, relationships and forms worked in the attach

[ArviLaanemets]

I.e. you want to build an ERP database for small firm. This isn't a simple task in case of small firm too. Let us look what kind of data structure you need.

The start is articles (materials or products) registry. Article type will determine, is it purchased material or produced (you may have more types there of-course). When you will have some componets you produce and then use later to produce in other products, then probably you need an additional field to determine, is the article sold to clients (is product), otherwise all produced articles are products.

Purchases and sales orders: Client orders from you certain amount of your products (articles) and you order from supplier some amount of materials (articles) you need, so you have to register purchase and selling orders. Depending on how different is info in those orders, you must have tblOrders and tblOrderRows with a field to differ between order types (purchase or selling), or tblPurchaseOrders, tblPurchaseOrderRows, tblSellingOrders, tblSellingOrderRows. NB. Getting order or sending order doesn't mean you will buy or sell products ordered. You or supplier have to decide, what to do about it and inform the other side about your decision. So in your Order(s) table(s) you must have some status field too, where you can store this decision.

Purchases and sales invoices: Very similar to Orders, but here are real quantities and prices. All payments are made based on supplier invoices, and you send your invoices to client to get paid for products. Invoices also must have a status field, where you can see p.e., are they paid or not. And it is good idea to have the Order Number registered with invoice too (NB! There may be several invoices linked with one order - p.e. when order includes delivery schedule or such schedule is agreed mutually).

Arriving and sending: Now we are in Store. In Store, you have to register incoming and outgoing products - for this you need Storage Movement(s) table(s) (The number of storage movements tables doen't depend on number od stores - you must decide it based on your data structure only). The minimum number of Stores is 1 - in this case you must have at least 2 fields to register movement type (purchase, sale, producing) and direction (incoming, outgoing). You can have separate Material Store and Product Store, but then for both of them Movement table must contain producing movement type too (getting materials, returning unused materials, returning faulty materials for one, getting products form production, returning faulty products to production, delivering products to client for another), unless you have separate movement tables for materials and products. And you must have to identify the Store too. All entries in Storage Movements are article (material or product) based, but must have fields which link the entry to purchase or selling invoice, or to production order.
Article balance in store is calculated as sum of all (incomings - outgoings). To avoid summing all entries for many years and to correct any entry errors in past, periodically inventories are made. My advice is to register Store Inventory Date and real amount of article into separate table, and whenever some difference is found through inventory, an income correcting entry with this date is made. Then to the Store balance for an article is LastInventoryQty + SUM(incomings - outgoings) for movement date later then last inventory date
It may be reasonable to have substores, p.e. for faulty materials and products, or for picking materials (where store workers assemble complects of materials for production orders).

Product: To produce something, you have to know:
1. Which parts (articles) you need at every production step and how much of them, are they produced in next steps or gotten from Store, etc.. It's often named BOM list.
2. Which operations you do at every production step (when any). Where you do the operation (workstation). How much time does workstation need to set up the operation (usually once for production order). How many time is needed to make a single operation.
2. You need a workstations table, where p.e. cost of working hour of this workstation is stored.

Ordering production: This is made through Production Order. Usually a separate production order is made for every workstation and operation. P.e. you need to produce 10 tables. Your BOM list has rows to produce table plate and getting material for them from store, to produce 4 legs and getting material from store, and getting some furniture articles from store. So you have a production orders for store to assemble needed quantity parts to for 3 workstations, a production order to one workstation to produce 10 table plates, a production order to second workstation to produce 40 legs, and a production order to third workstation to assemble 10 tables.

Reporting production: This is usually made in Report table, which is connected with Production Order table. Here real time spent to produce something is recorded - usually the quantity and spent time (or start and end time of reported operation). When all ordered operations are made (or there is a decision to stop the production), the really produced quantity is stored into Production Orders table, and the status of production order is set to finished. The finished articles are sent to another workstation or into store, depending on Production Order.

[Accountant]

my friends
Let's start working step by step , We recorded a purchase invoice from the supplier Teresa quantity 50 in Form Purchases, We registered the production of a door in quantity 30 in Form Production, Here the balance of the raw material from the beech wood is correct and we bought 50 then go 30 for production , The problem here: Where are put the amount of production of the product (door) until we withdraw from this quantity when selling to a customer , The problem I have starts with the production Form

[Accountant]

dear My friend : ArviLaanemets
Can you edit the existing rule and then attach it to try what you say

[pbaldy]

This has been cross posted elsewhere:

cross posters