I have a table with the following fields: paymentid, paymentdate, suppliername, paymentamount, servicecharge, revenue.
I want to sum the paymentamount column and display the paymenttable data on a report with the paymentamount total in the footer, the servicecharge total in the footer, and the revenue total in the footer.
The revenue will be the servicecharge minus the payment amount.
I can see how to get that. The problem starts at the beginning. I have to know what control source to bind the report to first. I have it bound to payments. Secondly, what is the coding to pull the sum of the payment amounts into the report as well. I tried making a query which works that totals the payments for me, however, I bound a textbox to the qrypayments query and it does not work. How do I go about doing this report. Maybe I am just ignorant, but I have tried several ways and cannot get it to work.
Thats what I am looking for. I apologize for the run around. As you can tell, I titled myself Rookie. That I am. So query wizard should be able to get me through it, right? Only, how do I get the join query to pull in the data and do the sum calculation as well?
Going to play with it real quick and then will base a report off of that query.
You can do all the calculations within the report itself, it's not necessary to do it with the datasource. If you pull the entire dataset from a single join query, you can then set grouping levels and have the report do any aggregates for you by setting a txtbox to =SUM([yourfield]) in a section footer.
Okay I used the qry wizard. I got the qry build but I dont think its a join query. I have also used a textbox as once before on the report.
I selected the fields I wanted then put them in the order I wanted and got to the end and did summary and sum for payment, service charge and revenue.
Gives me #error for the amount
I know for a join query you have to open it in design view and select the table you want to join it to....but I dont have a table that stores the sum of the payments in it....only the table with payment info.
I have payment table, customer table, companyinfo table
SELECT DISTINCTROW tblPaymentsMyLineToo.PaymentID, tblPaymentsMyLineToo.MyLineTooSupplierName, Sum(tblPaymentsMyLineToo.PaymentAmount) AS [Sum Of PaymentAmount], Sum(tblPaymentsMyLineToo.ServiceCharge) AS [Sum Of ServiceCharge], Sum(tblPaymentsMyLineToo.Revenue) AS [Sum Of Revenue]
GROUP BY tblPaymentsMyLineToo.PaymentID, tblPaymentsMyLineToo.MyLineTooSupplierName;
This is the query I have created.
Payment ID 1
Now i want all of that to show up on a report and in the footer section have the textbox reading Sum of Payments =SUM(PaymentAmount) Sum of Service Charges =SUM(ServiceCharge) Sum of Revenue = SUM(Revenue)
Ah, so all the information you're actually using comes from one table, tblPaymentsMyLineToo. First all, it's too much work to be doing all of your aggregates with the sql. Take the sum() functions and imput them to the report itself. Your basic dataset could like like this:
SELECT DISTINCTROW tblPaymentsMyLineToo.PaymentID, tblPaymentsMyLineToo.MyLineTooSupplierName, tblPaymentsMyLineToo.PaymentAmount, tblPaymentsMyLineToo.ServiceCharge, tblPaymentsMyLineToo.Revenue
It looks like you only want a sum for the entire report. If so, that's a pretty easy fix. Put all of your aggregates in the report footer. Anything that is sum().
Basically you're doing all the math in the query, when it's simpler and cleaner to do it in the report itself.