Results 1 to 7 of 7
  1. #1
    Aman is offline Novice
    Windows 10 Access 2019
    Join Date
    May 2023
    Posts
    1

    Question how to auto collect invoices numbers into another table as unique value

    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


    1. Clients
    2. Orders/sales
    3. Invoicing
    4. 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 :



    1. invoice N° : get all invoices number from Orders/sales table but without duplicating because multiple orders may have same invoice number
    2. Client id : based on the corresponded invoice
    3. Invoice date of the corresponded invoice
    4. taxeID (fields) :total of the taxes withe the same ID
    5. 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

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Seems like your Invoicing table should be an invoicing query...

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    correct (and simple) structure is

    tblClients - pk, name, address etc
    tblProducts (or services) - pk, name, details, etc
    tblinvoiceHeaders (with a fk back to clients) - pk, invoiceNo, invoiceDate, clientfk
    tblinvoiceLines (with a fk back tin invoice header and another fk back to products) - pk, Invoicefk, productfk, qty, price
    tblCash - pk, clientfk, invoiceFK, receivedDate, Amt

    tax treatment - perhaps include in invoice lines, or invoice header or both - depends on what the tax rules are

    However you will no doubt hit issues with this simple structure - what if you want to store the current price? (and prices change), what if you receive an over or under payment?, what if the client pays two or more invoices at the same time? what if the goods are returned? (in part or in full), what if you sell something and then discover you don't have the stock? You are charging tax - how are you going to account for that to the authorities? etc,

    why does the first invoice number bother you? -recommend google what is commonly called DMax+1, it might be what you are looking for

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Your DB logic isn't good! Consider:
    tblArticles: ArticleID, ArtName, ArtUnit, ...;
    tblClients: FirstName, LastName, ... ;
    tblOrders: OrderID, ClientID, OrderDate, ...; (This table contains general order info only. An order can be for any number of rows - an order row contains info about ordered articles. The OrderType field may be added in case you want to have both purchase and sales orders in same table.)
    tblOrderRows: OrderRowID, ArticleID, OrderQty; (This table contains info about all ordered articles or services or whatever. All pricing and taxing info doesn't belong here. In case you sell something, you register it in client invoice. In case you buy something, you register it in supplier invoice)
    tblInvoices: InvoiceID, InvoiceDate, ClientID, ...; (Again, the general info only!)
    tblInvoiceRows: InvoiceRowID, ArticleID/OrderRowID, ..., UnitPrice, VatPercent, TaxPercent, ...; (In case invoiced will be always exactly what was ordered, you can link invoice row with order row having OrderRowID here. As you have mentioned registering rest amount, this will not be the case, and you may need to have ArticleID, and a field for actual invoiced amoun here. And optionally ClientID too.).

    And anytime you have in any your table a ID-field from some other table as foreign key, don't have any other info from this table included. E.g. in your post you have in orders table fields for article ID and article name - which may result as problem. As simplest example - what will happen, when after a couple of years you have to rename some article. Do you change the name of this article manually in all previous orders, e.g. running an update query?

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    don't have any other info from this table included
    1 exception to this rule: table invoices and Invoicerows: these have always to remain exactly as they have been printed. So when afterwards an article name changes, the original name needs to remain as it was. So in these tables you have to register all data as they were on the moment the invoice was printed.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    1 exception to this rule:
    In addition to invoices, it really applies to any legal document - so for example would include anything to do with HR communications such as payroll, might also apply to sales and purchase orders. You can use a history table but that can be inefficient with large amounts of data.

  7. #7
    Join Date
    Apr 2017
    Posts
    1,673
    Or, like it is done in some ERP systems, there are orders and order rows tables, invoices and invoice row tables, etc. and additionally order log tables, invoice log tables, etc. which practically contain order/invoice/whatever row data with any essential information is entered in expanded form (no foreign keys) at moment when the entry is created (with datetime of creating the log entry saved). Usually any editing (except the status of entry) or deleting of log rows, after it was saved, is not allowed. Any later changes can be made by adding new log entries only. E.g. when there is a need to correct some info in order row, the log row matching this order row is marked as deleted/non-valid along with datetime of the change of status, and a new log entry with new info for this order row is added (keeping all info except the info about changed fields same as in original row).

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-23-2018, 01:13 PM
  2. Replies: 7
    Last Post: 03-06-2017, 08:43 AM
  3. Replies: 7
    Last Post: 12-10-2016, 12:02 PM
  4. Replies: 14
    Last Post: 05-06-2015, 11:19 PM
  5. Replies: 1
    Last Post: 03-31-2015, 02:24 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums