Query is too complex

[markwilliams2004]

So I'm not sure if a query is the best way to do what I'm trying to accomplish but essentially:

1. I am trying to create a table that hold's the life of a agricultural field. The table I set up has the following data:

FieldNum PlantingDate
6133 07/10/2008

2. Based on that planting date, I wanted to create 36 timeframes which would inidcate the field's life until it needs to be replanted. So:

Month 1 = 07/10/2008
Month 2 = 08/10/2008
Month 3 = 09/10/2008
....
Month 36 = 06/10/2011

I thought about a query that would calculate this and ultimately store it in a table (maybe) but I quickly get a query is too complex.

Any thoughts on how to get to my end result more efficiently?

Thanks

 

[PWise]

create a digits table

fields
digitid

values

1
2
....
36

run this query

insert into Fieldtimeframes (FieldNum,timeframes  )
Select FieldNum , PlantingDate ,dateadd("m",1,PlantingDate )timeframes 
from fields,digits

 

[JBinQLD]

Check out datepart, dateadd functions and ensure they can't help you out. Rather than create dates in advance like a spread sheet it's far better to generate a date and store data alongside it as and when required. That's the way we roll.

Laterz

 

[markwilliams2004]

It inrementally increases it to 8/1/2008 but repeats that number 36 times in the RS. I think I'm missing a key component of this

Thanks

 

[PWise]

sorry

insert into Fieldtimeframes (FieldNum,timeframes  )
Select FieldNum , PlantingDate ,dateadd("m",[COLOR=red]digitid-1[/color],PlantingDate )timeframes 
from fields,digits

 

[markwilliams2004]

Pwise...I see that it's using plantdate which is why it only increases it by on for the 36 times to 8/1/2008.

I may be doing it wrong but it seemed the way you mentioned. I renamed a couple fields but here's the SQL copied from access:

SELECT tblFldLifeTest.Field, tbldigits.DigitID, DateAdd("m",1,[PlantDate]) AS timeframes
FROM tblFldLifeTest, tbldigits;

 

[markwilliams2004]

Awesome!!!!! Thank you so much!