Retrieving old data

[Paul Fenton]

We have an application that tracks construction projects. Each
project will have multiple tasks, each with a due date. Each project
has progress payments due on certain task dates. Not every task has a
payment due. When a progress payment is made, the amount and paid
date are recorded.

We have a thing called the "WIP", which is a report of Work In
Progress. Bascially, we look for records in open projects where the
Paid Amount and the Paid Date are zero or null and those records are
gathered for the report. There is some more processing to consolidate
the individual retrieved records into a total dollar amount due for
each project.

If I run the report "today", I get an accurate picture of the WIP and
the total payments due the company, by project, at this moment. If I
want the report as of a date in the past, however, I don't get the
correct data because since that particular date, payments have been
made and the Payment Amount and the Due Date are no longer null or
zero and those records aren't retrieved in the queries. The database
has changed.

Can someone kickstart my thinking here as to how I can solve this
problem?


Paul Fenton

 

[Larry Daugherty]

If you want to capture date between dates, that's the thing to use:
"Between" in a criteria line. If you are using a launcher form then
put the Begin and End dates on that form. Now you can seek and
accumulate totals on only those "Paid" events that fall within your
time window.

If that doesn't address your specific issue it should still get you
going in the right direction.

HTH

 

[osmethod]

We have an application that tracks construction projects. Each
project will have multiple tasks, each with a due date. Each project
has progress payments due on certain task dates. Not every task has a
payment due. When a progress payment is made, the amount and paid
date are recorded.

We have a thing called the "WIP", which is a report of Work In
Progress. Bascially, we look for records in open projects where the
Paid Amount and the Paid Date are zero or null and those records are
gathered for the report. There is some more processing to consolidate
the individual retrieved records into a total dollar amount due for
each project.

If I run the report "today", I get an accurate picture of the WIP and
the total payments due the company, by project, at this moment. If I
want the report as of a date in the past, however, I don't get the
correct data because since that particular date, payments have been
made and the Payment Amount and the Due Date are no longer null or
zero and those records aren't retrieved in the queries. The database
has changed.

Can someone kickstart my thinking here as to how I can solve this
problem?

Paul Fenton

Can you be more specific with an example perhaps. Its hard to
interpret whaT "correct data" means to you?
If your Payment amount and due date are null this implies WIP? What
are you trying to query... Progress Payments outstanding, Progress
Payments Outstanding excluding/including Tasks not requiring
payment...?

osmethod

 

[Paul Fenton]

Let me clarify, if I can.

We're looking for projects that are In Progress. The definition of a
job that's still In Progress is one where there is at least one
payment yet to be made. All jobs have progress payments and when the
last payment is made, the job is Complete, no longer In Progress.

It's irrelevent how many tasks there are, although typical is from 20
to 30, of which 5 to 10 would require a Progress Payment at completion
of the task. So the "correct data" would be only those jobs that had
at least one unpaid Progress Payment as of that particular date. We're
not looking at a range of dates, just one point in time.

Once we retrieve the records, then we can calculate how much money is
in the "pipeline". Money that is due the company as of that day.

An example. We're going to build a patio cover. There are 15 tasks
from "Survey site", "deliver materials", to "final inspection". The
total job is $15,000.00 with 5 payments to be made at intervals. As
long as at least one of those payments has yet to be made (date paid
and amount paid are Null), then that customer record should be
retrieved. Once he makes his final pmt, we don't want it.

Hope that helps.


Paul Fenton