Adding a credit note to a statement report - Please help

[Sonya_S]

Hi,

I posted a few days ago on how to create a statement for a customer by using forms and reports. Thanks to the help, I was able to do it. Unfortunately, I now have another problem.

I need to add a credit note to my statement report which minuses a wrong invoice. For example, if a wrong invoice is given to a customer, then the user uses a credit note form which updates in the credit note table and this shows in the statement report as a minus. I hope that's not too confusing. The problem comes when I need to add it to my report. It will have the same date as the original invoice and the same price, it will just be shown as a minus.

My statement starts as a form which the user has to enter a start date and an end date and select a customer from a combo box, they then click generate statement which opens the report. How would I add the credit note in? I want it to be in the same date selected but the query for the report only contains the invoice date and not the credit note date. If I add this into the query, it clashes and brings up a blank report. Is there anyway to do this? Will I need to create another query for the credit note and add it in as a subreport? Will I have to add another startdate and enddate for it too as a subform in the statement form?




I'm really confused with this. Please help,

Thank you,

Sonya

[June7]

Need to know more about data structure. What fields are in note table that can be used in query join?

Want to provide db for analysis? Follow instructions at bottom of my post.

[Sonya_S]

Hello,

I have created a separate query called [CreditNoteQuery] which is based on the [CreditNote] table. The fields I have in this query are: [CreditNoteNo], [CustomerID], [OrderDate] and [Total2]. I put this under the criteria of my [OrderDate] field: Between Nz([Forms]![StatementDate].[StartDate],#01/01/1900#) And Nz([Forms]![StatementDate].[EndDate],#31/12/2100#) and it links me to the form [StatementDate], this requires the user to enter a [StartDate] and an [EndDate] using a date picker and it is also used for another query called [Statement1]. The user clicks okay and it brings up the Report [Statement01]. The report is based on the query [Statement1] and therefore when I pick a StartDate and an EndDate from the form it brings up the invoices from between those two chosen dates.

I have tried adding the [CreditNoteQuery] as a subreport [CreditNoteQuerysubreport] in the report [Statement01] but it makes everything look scattered. It works in the report header section of the report but not anywhere else as it duplicates all other records. For example, if I have three invoices and two credit notes, it duplicates the invoices so I have six invoices and two credit notes for each of them. I know I'm doing this completely wrong. Can you possibly help me?

Thank you so much

[Rainlover]

In my experience a Statement shows all outstanding transactions (Both invoices and credit notes) as of the end of the month. The date created is not taken into account. If it has been finalised then is simply does not show.

With this in mind I would have both invoices and credit notes in the same table. There would be one field that shows either Invoice or Credit to distinguish between the two.

One thing to bear in mind is that a credit note can be applied to more than one invoice and an invoice could have more than one credit note plus one or more cash payments. You would therefore need a separate table to handle transactions.

[Sonya_S]

I'm extremely confused on how to go about this. I have a credit note form [CreditNote] which is exactly similar to the Invoice form [Invoice], but it is based on a different table. Should I get rid of it and add a credit note no field to the Invoice table instead? Would the user fill out the invoice form again and then click generate credit note? Wouldn't that create a duplicate invoice though? I'm so confused. Please help.

Really appreciate the help, thank you

[Rainlover]

Two separate forms is good.
The RecordSource for both should be the same Table via similar but different Queries. The Queries should be filtered to allow only Invoices or Credit Notes as the case may be.
The Default Value on the Form should be either Invoice or Credit Note.
A separate Table should be used for transactions. The Transaction Table would be the Record Source for a Sub Form of Payments which would be a Sub Form to the other two I spoke about. (Invoices and Credit Notes)

Does this get you started.

[Rainlover]

Having had a bit of time to think about this, I can say that this is far more complicated than what you or I first thought.
The Client may receive many invoices.
The Client may send many payments.
The Client decides which paymant goes against which Invoice/s. Not the oldest as some believe.
The Client decides which Invoice/s has the Credit applied against. Not you.
An Invoice may have many payments applied against it.
A Statement shows all outstanding Invoices and Credit Notes as of a certain date. You should be able to produce a Statement as of any date, not just the end of month.
I do not know of any sample Data Bases that I can refer you to. Perhaps someone else can.
One day I shall write my own sample, however it is a big job and will take some time.

[Sonya_S]

Thank you, Rain and June7. I've resorted to just putting the credit note as a subreport instead. However, the only thing I need to do now is add both the subreport's [Total2] field with the main report's [Total] field to receive a grand total in the main report. Any idea on how I could I do that? My main report is called [Statement01] and my subreport is called [creditnotequery subreport]. I've created an unbound text box in the main report. What calculation would I need in it to receive a total of both the report and the subreport?

Thank you

[Rainlover]

This link may help.

http://www.mvps.org/access/forms/frm0031.htm

[Sonya_S]

Thank you so so much, Rain! I've got the total working. If I could just have some help on one last thing (I promise, this is the last thing )

How would I convert a positive number into a negative number. I want my field 'Total2' to be negative, is there any way of making it negative? E.g. 32.00 would turn to -32.00? Is there something I could put into the query or into the field in the report?

Thank you again. Really appreciate the help

[Rainlover]

Your Math teacher would not be happy with you. So I promise not to tell.

Multiple by -1

Provided he other figure is positive.