473,320 Members | 1,804 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

sum in access report with repeating invoice

Hi I need some helps in calculating the invoice amount in access 2003 report. Basically I have an access report that shows invoice#, invoice amount, payment amount, and payment date. The problem is some customers paid 2 - 3 times for 1 invoices (different amount and date) so when I total the invoice amount it duplicated since the report will show the same invoice amount for 2 payments
for example:

invoice number invoice amount payment amount payment date
100 3500 500 1/1/08
100 3500 3000 1/5/08
200 1000 1000 1/5/08
what I need is when I added the total on the report, it will show total invoice amount is 4500 (since it's only 2 invoices) instead of 8000. How do I do it on the access report? Please help.

Thank you.
Mar 6 '08 #1
6 5853
lee123
556 512MB
hi does this have a header and a footer because the way i do it is copy the total textbox and put it in the footer of the form and in the property box of the copied textbox find the control source and enter this

Expand|Select|Wrap|Line Numbers
  1. =sum(grandtotal)
lee123
Mar 6 '08 #2
hi does this have a header and a footer because the way i do it is copy the total textbox and put it in the footer of the form and in the property box of the copied textbox find the control source and enter this

Expand|Select|Wrap|Line Numbers
  1. =sum(grandtotal)
lee123
yes it's in group footer. But I if I use the sum, it still will give me the duplicate invoice total. what I need is, if invoice # is duplicate, take off the duplicate invoice amount when I want to do the sum.
Mar 6 '08 #3
yes it's in group footer. But I if I use the sum, it still will give me the duplicate invoice total. what I need is, if invoice # is duplicate, take off the duplicate invoice amount when I want to do the sum.
Can someone help me on this please....
Mar 7 '08 #4
Stewart Ross
2,545 Expert Mod 2GB
Can someone help me on this please....
Hi. There is no easy way to do this within your report, as Access is not to know that in fact the invoice amount may be a repeated value which should not be counted more than once. It is possible to use VB within the detail section to increment or decrement unbound values, but I wouldn't recommend this approach.

There is a work-around at the query level which will resolve this for you. Create a Count query counting the number of invoice rows for each invoice number based on your report's recordsource query:
Expand|Select|Wrap|Line Numbers
  1. SELECT [queryname].[invoice number], Count([invoice number] as N from [queryname] GROUP BY [invoice number];
Save this under a suitable name. Create another new query in which you join the new Count query to the original report query on the invoice number. Include all the fields from your original report query along with the count field, N, from the count query. Save this under a suitable name, then change the report's recordsource from your original query to this new one.

In your report add the new count field N to the detail line somewhere, but set its visible property to false (unless you want to show how many invoice payments there are for that invoice). Finally, to get the true invoice value shown on each unbound invoice total change the control source to
Expand|Select|Wrap|Line Numbers
  1. =SUM([Invoice Amount])/N
-Stewart
Mar 8 '08 #5
Stewart Ross
2,545 Expert Mod 2GB
Correction - control value should read
Expand|Select|Wrap|Line Numbers
  1. =SUM([Invoice Amount]/[N])
Mar 9 '08 #6
Correction - control value should read
Expand|Select|Wrap|Line Numbers
  1. =SUM([Invoice Amount]/[N])
thank you for the reply. I'll try it and will let you know....
Mar 11 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: JvdWal | last post by:
Hi, I've been trying the PDF & Mail Class Library from ACG Soft in combination with Northwind-2k3 style of db. Can someone help me with the error which is popping up: (which I cant get solved..)...
2
by: ABinBoston | last post by:
I have an Access Application that I would like to have a report print on pre-printed Invoices. The Totals are in the Footer, but the report prints a different size depending on the number of...
4
by: Greg Teets | last post by:
I have a table that provides all the details necessary for the line items of the report. I would like to use some other information in the header of the report. This information, like getting...
2
by: Dean Slindee | last post by:
Anybody written code in VB.NET to: 1) show a print preview window of reports already written and stored in an Access 2002 database; or 2) execute the print of a report stored in an Access 2002...
2
by: jburris | last post by:
In my current database, I have a single table which contains order information such as product, unit price and payment. The payment for each product is entered in and subtotals and totals are...
2
by: gleave | last post by:
Hi. im trying to setup a filter for a report. Let me explain what I would like to achieve. I have a form called Invoicing, here all the data is put in about the invoice, I then have a report also...
1
by: Hemant Trivedi | last post by:
Hi I am Hemant Hello to everone Now I want to discuss a problem I am facing in Ms-Access report and I want solution of it can U help me? My Problem is I want to print an invoice through...
1
by: mhafeez | last post by:
I have an invoice system developed in MS Access and invoice developed in Crystal Report 8.5. Now i want to print crystal report developed invoice from MS Access application Invoice Screen Directly by...
7
by: Evanescent | last post by:
Hi guys, as the title suggests, I'm facing some problems with the report. I have a form (createInvoiceForm) whereby the user can enter the invoice's details and then click on the Add New Record...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.