Results 1 to 11 of 11
  1. #1
    Sonya_S is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    10

    Adding a credit note to a statement report - Please help

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Sonya_S is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    10
    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

  4. #4
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  5. #5
    Sonya_S is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    10
    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

  6. #6
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.

  7. #7
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    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.


  8. #8
    Sonya_S is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    10
    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

  9. #9
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691

  10. #10
    Sonya_S is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jul 2012
    Posts
    10
    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

  11. #11
    Rainlover's Avatar
    Rainlover is offline Expert
    Windows 7 64bit Access 2003
    Join Date
    Nov 2009
    Location
    Queensland Australia
    Posts
    691
    Your Math teacher would not be happy with you. So I promise not to tell.

    Multiple by -1

    Provided he other figure is positive.

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

Similar Threads

  1. Replies: 9
    Last Post: 01-03-2012, 11:35 AM
  2. running sum for debit-credit in subform
    By roshanzarmal in forum Queries
    Replies: 1
    Last Post: 09-29-2011, 09:37 PM
  3. Replies: 2
    Last Post: 08-24-2011, 12:59 PM
  4. Can I add a note to a Query?
    By alexc333 in forum Access
    Replies: 2
    Last Post: 07-26-2011, 11:50 AM
  5. Credit Card Info, Where?
    By mastromb in forum Access
    Replies: 3
    Last Post: 05-26-2010, 12:37 AM

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