Force a query to populate consecutive dates?

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

I've been trying to figure out a way to do this for awhile, but I haven't
come up with anything that works. I'm querying a table in my company's
database that shows all of our loans and their payment changes. Some loans
have payment changes every month, some have payment changes every few
months/years, and others just have one payment amount and rate for their
whole term.

Loans that have a payment change every month have a record for every month
of the full term, like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
02/01/09 $200.00 6.5000%
03/01/09 $300.00 8.0000%
04/01/09 $400.00 7.5000%

That's how I want the entire table to look. But loans that have fewer
payment changes only show records for the due date that the payment change
happens, like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
03/01/09 $200.00 6.5000%
05/01/09 $300.00 8.0000%

And loans that don't have any payment changes only have one record. I'd like
for them to pull in a record for every due date, even if it's the same
payment amount and rate as the previous month. Like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
02/01/09 $100.00 6.0000%
03/01/09 $200.00 6.5000%
04/01/09 $200.00 6.5000%
05/01/09 $300.00 8.0000%
06/01/09 $300.00 8.0000%

And the table is protected, so I can't change the way it's set up. Sorry for
the long post. Does anyone have any suggestions? I understand if there's no
way to do this. I just thought it wouldn't hurt to ask for help.

Thanks,

Michelle
 
I keep thinking that I might be able to use the IIf function somehow. The
months of the loan term is in the database. If I could count the due dates,
and if the count of a loan's due dates is less than the total term months,
then I would know which loans don't have payment changes every month.

For those loans, maybe I could just recreate the table (sort of) in the
query, by identifying which months in the loan term count aren't listed and
use the DateAdd function on the first due date to bring in the months that
aren't listed.
 
I've been trying to figure out a way to do this for awhile, but I haven't
come up with anything that works. I'm querying a table in my company's
database that shows all of our loans and their payment changes. Some loans
have payment changes every month, some have payment changes every few
months/years, and others just have one payment amount and rate for their
whole term.

Loans that have a payment change every month have a record for every month
of the full term, like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
02/01/09 $200.00 6.5000%
03/01/09 $300.00 8.0000%
04/01/09 $400.00 7.5000%

That's how I want the entire table to look. But loans that have fewer
payment changes only show records for the due date that the payment change
happens, like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
03/01/09 $200.00 6.5000%
05/01/09 $300.00 8.0000%

And loans that don't have any payment changes only have one record. I'd like
for them to pull in a record for every due date, even if it's the same
payment amount and rate as the previous month. Like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
02/01/09 $100.00 6.0000%
03/01/09 $200.00 6.5000%
04/01/09 $200.00 6.5000%
05/01/09 $300.00 8.0000%
06/01/09 $300.00 8.0000%

And the table is protected, so I can't change the way it's set up. Sorry for
the long post. Does anyone have any suggestions? I understand if there's no
way to do this. I just thought it wouldn't hurt to ask for help.

Thanks,

Michelle
Click to expand...

One way you could do this is with the help of an auxiliary table, with just
one Date/Time field containing one record per month. You can build this table
very quickly in Excel with Fill... Series - just fill it through 2050 or 2100,
it'll still be tiny.

You could then use a Query joining your table to this table with a join clause
like:

SELECT monthtable.Monthdate, yourtable.Payment, yourtable.[INterest Rate]
FROM yourtable, monthtable
WHERE Monthtable.Monthdate = (SELECT Max(A.[Due Date]) FROM yourtable AS A
WHERE monthtable.Monthdate <= A.[Due Date])
 
John W. Vinson said:
I've been trying to figure out a way to do this for awhile, but I haven't
come up with anything that works. I'm querying a table in my company's
database that shows all of our loans and their payment changes. Some loans
have payment changes every month, some have payment changes every few
months/years, and others just have one payment amount and rate for their
whole term.

Loans that have a payment change every month have a record for every month
of the full term, like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
02/01/09 $200.00 6.5000%
03/01/09 $300.00 8.0000%
04/01/09 $400.00 7.5000%

That's how I want the entire table to look. But loans that have fewer
payment changes only show records for the due date that the payment change
happens, like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
03/01/09 $200.00 6.5000%
05/01/09 $300.00 8.0000%

And loans that don't have any payment changes only have one record. I'd like
for them to pull in a record for every due date, even if it's the same
payment amount and rate as the previous month. Like this:

Due Date Payment Interest Rate
01/01/09 $100.00 6.0000%
02/01/09 $100.00 6.0000%
03/01/09 $200.00 6.5000%
04/01/09 $200.00 6.5000%
05/01/09 $300.00 8.0000%
06/01/09 $300.00 8.0000%

And the table is protected, so I can't change the way it's set up. Sorry for
the long post. Does anyone have any suggestions? I understand if there's no
way to do this. I just thought it wouldn't hurt to ask for help.

Thanks,

Michelle
Click to expand...

One way you could do this is with the help of an auxiliary table, with just
one Date/Time field containing one record per month. You can build this table
very quickly in Excel with Fill... Series - just fill it through 2050 or 2100,
it'll still be tiny.

You could then use a Query joining your table to this table with a join clause
like:

SELECT monthtable.Monthdate, yourtable.Payment, yourtable.[INterest Rate]
FROM yourtable, monthtable
WHERE Monthtable.Monthdate = (SELECT Max(A.[Due Date]) FROM yourtable AS A
WHERE monthtable.Monthdate <= A.[Due Date])
Click to expand...

Thanks! I'm trying that right now.

Michelle
 
Back
Top