Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Why would the total fee for code = 100 be 35.00? I would say it's 15.00 + 25.00 = 40.00

  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Sorry it was a typo

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Why 45.00? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have a query that isolates all detail records by an operator entered Received or Issued Date Range, example 5/1/5 - 5/31/5

    <table border=1><td>Code</td> <td>Receive</td><td>Issue</td><td>Cost</td><td>Fee</td> <td>100</td> <td>05/01/05</td><td>05/11/05</td><td>1000</td><td>15.00</td> <td>100</td> <td>05/31/05</td><td>05/31/05</td><td>5000</td><td>25.00</td> <td>100</td> <td>04/30/05</td><td>05/01/05</td><td>2000</td><td>45.00</td> <td>200</td> <td>04/30/05</td><td>05/01/05</td><td>3000</td><td>50.00</td> <td>200</td> <td>05/01/05</td><td>05/15/05</td><td>1000</td><td>10.00</td> <td>200</td> <td>05/15/05</td><td>05/16/05</td><td>5000</td><td>20.00</td> <td>200</td> <td>05/31/05</td><td> </td><td>2000</td><td>40.00</td> </table>For a Report, I need code to summarize by code and accumulate Received Total Count, Cost and Fees for records that have a Receive Date in the Date Range entered in the query.

    I need code to summarize by and accumulate Issued Total Count and Value for records that have a Issue Date in the Date Range entered in the query.

    Report should look like this:

    <table border=1><td>Code</td> <td> #R </td> <td> Cost </td> <td> #I </td> <td> Fee </td> <td>100 </td> <td> 2 </td> <td> 6000 </td> <td> 3 </td> <td> 40.00 </td> <td>200 </td> <td> 3 </td> <td> 8000 </td> <td> 2 </td> <td> 70.00 </td> </table>

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Wooops 40.00 is OK for the 2 100 transactions that were received in May

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I would create
    - A Totals query with Group By for Code, Count for Receive and Sum for Cost and Fee, and criteria to limit Receive to the specified period.
    - A Totals query with Group By for Code and Count for Issue, and criteria to limit Issue to the specified period.
    - A query that combines the two above queries, joined on Code.
    The latter query can act as record source for the report.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Help With Query Totals for Report? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    Perfect!

    Thanks, John

Posting Permissions

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