Make-Table query: create records based on another table's criteria? 1

[gusbrunston]

[TT]
Hi:

I would like to run a make-table query to post rents to a table used to produce invoices.

Existing table includes the following fields:
TenantID
Rent
LeaseStartDate
LeaseEndDate

For each TenantID the new table should contain a record for the Rent due on the first of each month equal to or greater than the LeaseStartDate and less than or equal to the LeaseEndDate.

The new made table should look like this:

TenantID Date Rent
0006 04/01/06 $1195.00
0006 05/01/06 $1195.00
0006 06/01/06 $1195.00
0006 07/01/06 $1195.00
etc...
0015 05/01/06 $995.00
0015 06/01/06 $995.00
0015 07/01/06 $995.00
0015 08/01/06 $995.00
0015 09/01/06 $995.00
etc...

(After the original made-table is created, I know how to update it each month...that is add a record for the current month. My need is to create the "first edition" from past data.)

Not very experienced at SQL, I am working from the query design view of Access 2003. So far, I've been unable to crack this nut.

Any help would be very much appreciated.

Thanks,

Gus[/TT]

[tt]
Gus Brunston
www.thefirstnationalnetwork.com[/tt]

 

[dhookom]

I would create a table of numbers [tblNums] with a one numeric field [Num] and values 1 through the maximum number of months difference between Start and End dates. You can then add this table to a query with the [Existing] table. You can then create a new field in the query with and expression like:
RentDate: DateAdd("m", [Num], [LeaseStartDate])
Set the criteria under this column to
<=[LeaseEndDate]
Add the TenantID and Rent fields to this query and change it to a maketable or append query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gusbrunston]

[tt]Duane, thank you so much for the suggestion. I'll have to delay my work on this because of some other pressing issues, but I should be able to work on it this weekend. I'll let you know, and thanks again!
Gus[/tt]

[tt]
Gus Brunston
www.thefirstnationalnetwork.com[/tt]

 

[gusbrunston]

[TT]Duane:

Great!
That looks like such a simple solution, but I can't yet figure out why it works...it just does. Here's a star for you.

I have only one other thing to ask:

When a tenant's LeaseStartDate begins after the 1st day of the month, that number for that day is displayed for each record for that tenant. e.g., if the lease start date was 03/25/06 (I've already posted a pro-rata rent for March) then this is what I am now getting in the Date column:
[ul]
TenantID Date Rent | Date needs to be:[/u]
0006 04/25/06 $1195.00 | 04/01/06
0006 05/25/06 $1195.00 | 05/01/06
0006 06/25/06 $1195.00 | 06/01/06
0006 07/25/06 $1195.00 | 07/01/06
etc...
0015 05/01/06 $995.00 (Works great if this
0015 06/01/06 $995.00 tenant's LeaseStartDate
0015 07/01/06 $995.00 was 05/01/06.)
0015 08/01/06 $995.00
0015 09/01/06 $995.00
etc...

Thinking that Duane probably helps those who help themselves, I've tried to find out how to force the Date to the 1st day of each month, but so far no joy. I'll keep looking, but if you have it on the tip of your mind, well...

My sincere thanks for getting me started in the right direction on this query...I was at my wit's end.

Gus[/TT]

[tt]
Gus Brunston
www.thefirstnationalnetwork.com[/tt]

 

[dhookom]

To change the LeaseStartDate to the first of the month, subtract the day of the month from the LeaseStartDate and add 1. Play with something like:

RentDate: DateAdd("m", [Num], DateAdd("d",-Day([LeaseStartDate]) +1 ,[LeaseStartDate]))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[gusbrunston]

[TT]
Duane!
You've done it again...Thanks so much.
Cheers,
Gus[/TT]

[tt]
Gus Brunston
www.thefirstnationalnetwork.com[/tt]