hello i'm new here and this my first post please bear with me
I'm trying to build a all in one dynamic/dependable database and i have 4 tables for now
- Clients
- Orders/sales
- Invoicing
- Cash incoming
The main idea for each selling cycle is:
First record the client in Clients table (ClientID, first name, last name, phone, email, .......ect)
Second record the Orders/sales of the clients in Orders/sales table (OrID, ClientID , articleID , article name, quantity, unite price ,ex-VAT ,taxe-id ,vat-inc, invoice N°,invoice date)
the invoice N° and invoice date field will be empty until the order is done and then recorded manually
the part that i have problem with is the Invoicing table i wanted to get all the data from the Orders/sales table and fill the following :
- invoice N° : get all invoices number from Orders/sales table but without duplicating because multiple orders may have same invoice number
- Client id : based on the corresponded invoice
- Invoice date of the corresponded invoice
- taxeID (fields) :total of the taxes withe the same ID
- Rest amount (Rest amount to fulfill the invoice payment it get the total encasement in the Cash incoming table input are recorded manually incomeID, invoices n°, amount of payment, date of payment based on the corresponded invoice number )
the first field invoice N° the bother me the most , the rest if i can only have one example and i will apply each of them
thank you in advance