# Thread: Need help with sumry query (Access 97)

1. ## 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?

Thanks!
Bruce

2. ## 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
•