Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Thanked 0 Times in 0 Posts

    Need help with sumry query (Access 97)

    Can someone help with this one? Given the sample table of data below. Is there a way to create a query to answer the following question?
    For a projects started on 10-26-96, how long did it take for at least 80% of the money to be dispersed?

    The total of all payments is $1,075 dollars. 80% of this $860 dollars. In this case, the 1-28-97 payment date would be use to determine the number of days to reach at least 80% of the total money paid.
    <table border=1><td>Proj. Number</td><td>Proj Start Dt.</td><td>Payment $</td><td>Date Paid .</td><td>1</td><td>10-26-96</td><td>500.00</td><td>11-22-96</td><td>1</td><td>10-26-96</td><td>300.00</td><td>01-02-97</td><td>1</td><td>10-26-96</td><td>200.00</td><td>01-28-97</td><td>1</td><td>10-26-96</td><td>50.00</td><td>11-22-98</td><td>1</td><td>10-26-96</td><td>25.00</td><td>06-12-99</td></table>
    I have a flat table listing 100's of projects with payments similar to the sample table above and need a query to crunch this into a summary table like the one below.
    <table border=1><td>Proj. Num</td><td>Proj Start Dt.</td><td>Proj Total $</td><td>Days to reach 80% of payment</td><td>1</td><td>10-26-96</td><td>$ 1,075</td><td>63</td></tr><td>2</td><td>mm-dd-yy</td><td>$ $,$$$</td><td>##</td><td>3</td><td>mm-dd-yy</td><td>$ $,$$$</td><td>##</td></tr></table>

    Can it be done or do I need a VBA solution?


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Need help with sumry query (Access 97)

    Dont know if you're still interested.
    It can be done in queries, but you need to use more than one (I think).
    I created three queries. I give the SQL statements of each. Of course, you will have to change table and field names.

    First, a Group By query to compute the total paid per project: qryTotal

    SELECT [Project Number], [Project Start Date], Sum([Payment]) AS [Total Payment]
    FROM [tblPayments]
    GROUP BY [Project Number], [Project Start Date];

    Second, a query to compute running totals, qryRunSum

    SELECT [Project Number], [Payment], [Payment Date],
    Val(DSum("Payment","tblPayments","[Project Number] = " & [Project Number] & " AND " &
    BuildCriteria("[Payment Date]",8,"<=" & [Payment Date]))) AS RunSum
    FROM tblPayments
    ORDER BY [Project Number], [Payment Date];

    (The BuildCriteria is necessary if your computer doesn't use US date format; in the USA, the SQL statement could be simplified a bit)

    Third, a Group By query to get the number of days when at least 80% has been paid.

    SELECT qryRunSum.[Project Number], qryTotal.[Total Payment],
    Min([Payment Date]-[Project Start Date]) AS [Number Of Days]
    FROM qryRunSum INNER JOIN qryTotal
    ON qryRunSum.[Project Number] = qryTotal.[Project Number]
    WHERE (((qryRunSum.RunSum)>0.8*[Total Payment]))
    GROUP BY qryRunSum.[Project Number], qryTotal.[Total Payment];

    This may look daunting. When you adapt them and view them in Design and Datasheet view, it hopefully becomes clear how they work.

Posting Permissions

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