Add records for each month in QUERY

[jamaarneen]

Hi,

I have a table with Contracts (rent of apartment's), what was a field Date_Begin.
there is also a table PRICES, where i enter the price(s) for each contract (the price can change annually).

What I want to achieve is, a query that should give me -per contract- a list with a record for every month since the 'Date_Begin' until the current month. the query will have to take the right amount from the table 'PRICES'.

My question is: how do i make that the query should ADD a new line for each month (=each first day of a month).
[I don't think of adding to a table, it's only to represent in the query].

Or maybe isn't it possible to achieve this with only a query? do I have to store those lined in a table?

Thanks in advanced

 

[dhookom]

You need a table with some records in order to generate records. If I understand correctly, you can create a table [tblNumbers] with a single numeric field [num] and values for 1 to whatever large number you need to create new records. Then you can add this table to a query with your Contracts table and Prices and use an expression like:

Mnths: DateAdd("m",[num],Date_Begin)

Add the Num field to the query so you could set the criteria to the number of months you need to generate.

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

Hi Duane, thank you for your respond.

My issue is, the simplest way how to create a list, of how much rent somebody has to pay. therefor I would like to have one record for each month, beginning from the begin_date of the contract, until the current month.

I'm not sure I understood what you suggested, but what I do understand is that i probably need a table with one field with dates, to feed my query.

I thought of a table with a list of 'first-day-of-a-month':
01/01/2008
01/02/2008
01/03/2008

and so on...
Am I wrong? or - for a change - right?

Thanks, J

 

[dhookom]

You could use the table of numbers to generate the first day of the month table. Either solution should work but you might feel the most comfortable with the date values.

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

so it looks like I'll think do it with an extra table.

but I'm really wondering, if there isn't any other way, without a table - just to write an expression that will generate a record for every month.
maybe something like VB code. with a loop (from 'begin_date' until 'now')...

well, I'm not experienced, but maybe somebody of our experts out here will have an idea... P L E A S E

and, thanks... Ja

 

[dhookom]

I have a sample recurring that has the user interface and code for creating recurring events.

I don't think there is a method of generating records with generating records ;-)

Duane
Hook'D on Access
MS Access MVP

 

[jamaarneen]

HI

I'm not sure i understand what you mean with your recurring sample. it dosn't work properly with my access2003.

but thank you anyway for your help
Ja

 

[dhookom]

My recurring sample uses DAO code to append/generate records of a specific date interval. Your request is to append/generate records of a specific date interval. Your date interval is month. The sample can do months along with other intervals.

My code will work if you set a reference to the DAO object library.

Duane
Hook'D on Access
MS Access MVP

 

[tinmar]

Hi , I don't usually use MS Access, but just need some simple guidance.

I have 2 tables, 1 with a field of say total volume, and another table which has a field with the total cost, all I want to do is to find out the cost for a day.

they both have the date field in them, so I joined them at the query level, and then created another table named table 3

table 1
field - total call volumes

table 2
field - total money

table 3
field - cost

if i joined all of them with the date field, cant i in the criteria just say = [totalmoney.table2]/[totalcallvolume.table1] ,

thanks

 

[dhookom]

Create a new thread with your new question. When you do it, you need to provide all of the significant table and field names and data types.

Duane
Hook'D on Access
MS Access MVP