Results 1 to 14 of 14

Thread: Report problem

  1. #1
    Join Date
    Dec 2002
    Posts
    117

    Unanswered: Report problem

    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.

    Any help is appreciated. Thanks in advanced.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sounds like a fairly simple report. What have you come up with so far? Hopefully it's a quick fix.

  3. #3
    Join Date
    Dec 2002
    Posts
    117

    Report So Far

    So far I can get the paymentamount to total and display but everything else errors out.

    It seems that I am having a problem with the report needing to pull info from two tables but its only binding to one.

    I can either get the servicecharge and revenue to total and the paymentamount to #error or vice-versa.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What are you using as a record source for the report? You should be using a join query?

  5. #5
    Join Date
    Dec 2002
    Posts
    117

    Record Source

    My record source has been qryPayments......which is a qry that sums the amounts......it has been tblPayments where it pulls the service charge, revenue, and payment amount out.....

    I just need to figure out how can I combine the payment data and list the totals on the report. But, unfortunately, its clashing with control sources and I cant figure out how to do it.

    Thanks.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In your footer, the Revenue textbox Control Source should be something like:

    =CCur([ServiceChgTxt])-CCur([PaymentAmountTxt])


    Where in my example ServiceChgTxt and PaymentAmountTxt are the names of the other textbox controls in the footer ...

  7. #7
    Join Date
    Dec 2002
    Posts
    117

    Yet again

    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.

    Thanks.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That's what I was eluding to. You should be binding the report to a join query as opposed to a single table. Your life will be much easier.


  9. #9
    Join Date
    Dec 2002
    Posts
    117

    Join Query

    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.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.

  11. #11
    Join Date
    Dec 2002
    Posts
    117

    Getting there

    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.

    =SUM(PaymentAmount)

    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

    What do I join?

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Post the current sql you're using and a sample record

  13. #13
    Join Date
    Dec 2002
    Posts
    117

    Sample

    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]
    FROM tblPaymentsMyLineToo
    GROUP BY tblPaymentsMyLineToo.PaymentID, tblPaymentsMyLineToo.MyLineTooSupplierName;


    This is the query I have created.

    Payment ID 1
    Date Today
    Supplier Microsoft
    PaymentAmount 100.00
    ServiceCharge 10.00
    Revenue 25.00

    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)

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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
    FROM tblPaymentsMyLineToo

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •