UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordset For Each Invoicenumber, Access 2016    
 
   
bartjuh999
post Oct 12 2019, 07:23 AM
Post#1



Posts: 6
Joined: 16-February 19



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

Go to the top of the page
 
Jeff B.
post Oct 12 2019, 07:56 AM
Post#2


UtterAccess VIP
Posts: 10,300
Joined: 30-April 10
From: Pacific NorthWet


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

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
orange999
post Oct 12 2019, 08:47 AM
Post#3



Posts: 1,967
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


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)
Attached File  ModifiedCustomerInvoice.png ( 50.71K )Number of downloads: 6
 

--------------------
Good luck with your project!
Go to the top of the page
 
projecttoday
post Oct 12 2019, 08:50 AM
Post#4


UtterAccess VIP
Posts: 11,055
Joined: 10-February 04
From: South Charleston, WV


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

--------------------
Robert Crouser
Go to the top of the page
 
GroverParkGeorge
post Oct 12 2019, 09:02 AM
Post#5


UA Admin
Posts: 35,844
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
arnelgp
post Oct 12 2019, 09:51 AM
Post#6



Posts: 1,424
Joined: 2-April 09
From: somewhere out there...


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.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
bartjuh999
post Oct 12 2019, 11:49 AM
Post#7



Posts: 6
Joined: 16-February 19



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 !!

Go to the top of the page
 
arnelgp
post Oct 12 2019, 01:13 PM
Post#8



Posts: 1,424
Joined: 2-April 09
From: somewhere out there...


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

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
bartjuh999
post Oct 12 2019, 02:08 PM
Post#9



Posts: 6
Joined: 16-February 19



Wauw thanks!! Will check it out and post a reply
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th October 2019 - 03:41 AM