[X]My Assistant

[bartjuh999]

Hi,

I have 2 tables tblCustomers and tblInvoices, in the Invoices table i have datefields for the invoicelines (invoice has an invoicedetailtable and a producttable besides it)
I want to make a button which generates a follow up number like an invoicenumber.

A customer can have multiple invoices but i want the customer which has multiple invoices on 1 day to be combined into 1 invoicenumber.
I thought i could look up the fields which have the same [CustomerId] and the same [Invoicedate] and add the same number on that record in another field called [Invoicenumber].

So for example Customer John Doe has 3 invoices -- 2 on 2-9-2019 and one on 9-9-2019 then the field [Invoicenumber] should have the value 2019001 for the first 2 records and 2019004 for the third record.
Another Customer Jane Smith has 4 invoices, 3 on the same date and 1 on another, here [Invoicenumber] should be 2019002 for the first three invoices and 2019003 for the 4th invoice.

So like this

CustomerId Name Invoicedate Invoicenumber
079 John Doe 02-09-2019 2019-001
02-09-2019 2019-001
09-09-2019 2019-004

082 Jane Smith 04-09-2019 2019-002
04-09-2019 2019-002
04-09-2019 2019-002
06-09-2019 2019-003

I think i can do something with a recordset and then a for each statement, but don't know how to approach this.
Could you help ?
Thanks for reading this !

Bart Prins

[Jeff B.]

Are you saying that each invoice number (not the date part of it) should be unique?

[orange999]

I am also not sure of what exactly you are asking/expecting.

Here is a modified data model based on the Customer and Invoice data model from Barry Williams' site.
I removed some of the financial portion of his original hoping to show the tables involved in a typical Customer, Order, Product, Invoice set up.
(yours may differ).

Attached File(s)

 ModifiedCustomerInvoice.png ( 50.71K )Number of downloads: 8

 

[projecttoday]

When the user clicks "create invoice" search for an invoice for that user for that day and open it.

[GroverParkGeorge]

My first reaction to quests like this is to stop and wonder why we're going into a situation where we have to concatenate different data elements to get a "unique" identifier. There is, no doubt, a way to do this, but first I wonder if it's really the best approach.

You want to have ALL invoices submitted on one day classified as a single "Invoicing Event", correct?

So, I would start out saying each new invoice is actually not quite the same as the current individual invoice events being captured now. And that means the parent table here would be something like "DailyInvoice". When a customer submits an invoice for a given day, you start a new "Daily Invoice" for that customer for that day. That invoice carries the unique Invoice ID you need. It CAN be the currently proposed version based on a date, but then every customer would have the same invoice for that date and that only works if the primary key for "DailyInvoice" is a composite consisting of CustomerID and Daily Date Invoice.

Now, instead of having to concatenate your way to the individual Invoice events to be assigned to that master "Daily Invoice", you simply record the sequence for it in the Invoice table, and the foreign key back to the master, DailyInvoice table does the rest.

You will need to use DLookup when adding new records for Daily Invoices and individual Invoice events to ensure the sequences are appropriate within the invoice list for a given customer. That, however, is a much more accessible task, IMO.

[arnelgp]

the concept is the same as we have.

we have many test packages and on that test packages are many
materials to complete the package.

we request the materials to the main con, together with other
groups (who also have test packs).

the main con gather all request and create a single "Tracking Number"
for all materials requested on that day.

the following day the main con provide as of list of tracking number and
associated test packs/materials.

[bartjuh999]

Thanks for all the replies and the data model !!!
Yes the number should be unique and a follow up number as it is an invoicenumber. So the invoice should be +1 for each customer for that day. As in the example the first customer has 2019-001 for 2 invoices the second customer has 2019-002 and the first customer on another day gets 2019-003

I think i understand the DailyInvoice a bit, but how would i approach this ?

Make a table DailyInvoice with an DailyInvoiceId and a foreign key to CustomerId and InvoiceId?
And how do i automate the lookups for that specific day ? So how would i trigger this?

My tables now are:

tblCustomer with pk CustomerId
tblInvoice with fk CustomerId
tblInvoiceDetails with fkI InvoiceId and fk ProductId
tblProducts with pk ProductsId

Again for thinking along !!

[arnelgp]

DailyInvoice(table)
InvID (long, autoID of Invoice Master)
InvSeries (short text)

you can add the record to this table on the InvoiceMaster (form) AfterUpdate Event:

CODE

Private Sub Form_AfterUpdate()
    Dim lngID As Long
    Dim strSeries As String
    Dim var As Variant    
    lngID = Nz(DLookup("InvoiceID", "tblInvoice", "InvoiceDate=" & Format("\#mm\/dd\/yyyy\#") & "And CustomerID=" & Me.CustomerID, 0)
    If lngID <> 0 Then
        strSeries = Nz(DLookup("InvSeries", "DailyInvoice", "InvID=" & lngID), "")
        If strSeries ="" Then
            strSeries = Nz(DMax("InvSeries", "DailyInvoice"), Year(Date()) & "-" & "000")
            var = Split(strSeries, "-")
            var(1) = Format(Val(var(1)) + 1, "000")
            strSeries = Join(var, "-")
        End If
    Else
        strSeries = Nz(DMax("InvSeries", "DailyInvoice"), Year(Date()) & "-" & "000")
        var = Split(strSeries, "-")
        var(1) = Format(Val(var(1)) + 1, "000")
        strSeries = Join(var, "-")
    End If
    CurrentDb.Execute "Insert Into DailyInvoice (InvID, InvSeries) " & _
                "Select " & Me.InvoiceID & ", '" & strSeries & "';"
End Sub

in case you decided to delete the InvoiceMaster (header) then
you must delete also the Record from DailyInvoice.
you do it on the Form's Close Event:

CODE

Private Sub Form_Close()
Currentdb.Execute "Delete * From DailyInvoice Where InvID Not In (Select InvoiceID From tblInvoice);"
End Sub

[bartjuh999]

Wauw thanks!! Will check it out and post a reply

[bartjuh999]

Hi Arnelgp,

Thanks again for the code!
I edited it to my tables and fields as followed:

CODE

Private Sub Form_AfterUpdate()
'Code voor het maken van een dagfactuur

Dim lngID As Long
    Dim strSeries As String
    Dim var As Variant
    lngID = Nz(DLookup("FactuurId", "tblFactuur", "Factuurdatum=" & Format("\#mm\/dd\/yyyy\#") & "And KlantId=" & Me.KlantId), 0)
    
  
    If lngID <> 0 Then
        strSeries = Nz(DLookup("FactuurSeries", "tblDagFactuur", "FactuurId=" & lngID), "")
            If strSeries = "" Then
            strSeries = Nz(DMax("FactuurSeries", "tblDagFactuur"), Year(Date) & "-" & "000")
            var = Split(strSeries, "-")
            var(1) = Format(Val(var(1)) + 1, "000")
            strSeries = Join(var, "-")
        End If
    Else
        strSeries = Nz(DMax("FactuurSeries", "tblDagFactuur"), Year(Date) & "-" & "000")
        var = Split(strSeries, "-")
        var(1) = Format(Val(var(1)) + 1, "000")
        strSeries = Join(var, "-")
    End If
    CurrentDb.Execute "Insert Into tblDagFactuur (FactuurId, FactuurSeries) " & _
                "Select " & Me.FactuurId & ", '" & strSeries & "';"
End Sub

However i'm getting an error on the line

CODE

lngID = Nz(DLookup("FactuurId", "tblFactuur", "Factuurdatum=" & Format("\#mm\/dd\/yyyy\#") & "And KlantId=" & Me.KlantId), 0)

The error is 3075 Syntaxiserror Operator is missing in query-expression:
Factuurdatum=\#mm\/dd\/yyyy\#And KlantId=2.

I use a short date notation which in my country (The Netherlands) is dd-mm-yyyy
For the InvoiceDate (Factuurdatum) i used a Date() as default value.

Could you check what i'm doiing wrong ?

Thanks so much again

Gr.

Bart

[dmhzx]

Jumping back to the start if you don't mind:
You seem to be wanting to collect all invoices for a given customer on a given date to be lumped together.
But didn't actually say what this was for.

Accounting packages and such things as invoices tend to be more or less immediate. If the customer gets three invoices on a single day, then there are normally three sets of activities in the accounting area.

So the reason for this appears to be for some sort of reporting rather than interacting with the customer.

So what you have is an invoice number produced by the accounting package, and you want to aggregate all invoices for a given day for some purpose.

How about a table to hold
CustomerID, Invoice Number, Invoice date, Invoice Daily Sequence (Sequence can be increased by one each time) From this you can concatenate Customer ID, Invoice date, and Sequence number

I think you need to be clear on what an "Invoice Number" is, and what I would call a "Invoice Daily Sequence"

Have I understood what you want?

[Jeff B.]

… and if <dmhzx> has correctly identified your eventual purpose as reporting, consider NOT aggregating the data in your tables, but using queries to do so, and feeding your reports from the queries...

[bartjuh999]

Thank you both for the replies, thats correct the purpose of this is not to send for example three invoices on a day to a customer but one. However i need an (say) invoicenumber which is the reference for the payment and the tax authority demands i have a following number for each invoice i send out. Would like to make a query for this but how.do.i aggregate this in a query by customer and date? Do.you have an example.for.this.

Again thanks.for thinking along !

Gr

Bart

[bartjuh999]

And how do i add that follow up number to this after making the query

Thanks

[bartjuh999]

Anyone please ?

[bartjuh999]

Just trying to.bump this for a reply

Thanks!

Bart

[dmhzx]

You seem to be raising three 'invoices' a day, but wanting to send one consolidated invoke a day.

You need to be clear on what an invoice is. It cannot be two different things.

Are you saying that you want to process multiple orders throughout the day, and then wrap them all up in a single invoice to send to the customer.
Or ar you raising several invoices during the day, and want to send a batch for the day all out at once?

If the former, you can do this by NOT allocating invoice numbers when you raise them, but then have an invoice numbering run, which give them all a single number. It doesn't need to be sequential, just the next overall invoice number. Your basic query here would be to find all 'invoices' for a customer that don't yet have a number, and add one.


If the latter, then you can't call both what you raise during the day AND what you send out "Invoice".

Sorry if this is labouring the point, but I'm having trouble understanding WHAT you want, and so can't advise on HOW to do it.