Insert years based on first and last year

[sanniep]

Hi guys,

Hope you can help out. I would like to create a query or piece of code to create records based on a start and end value. So I got this data:

Name Value Start End
DF334 ABC 2004 2007
DF335 DEF 1998 2001
DF336 DEF 2013 2016

And I want to make it like this:
DF334 ABC 2004
DF334 ABC 2005
DF334 ABC 2006
DF334 ABC 2007
DF335 DEF 1998
DF335 DEF 1999
DF335 DEF 2000
DF335 DEF 2001
etcetera

How could I resolve this?

Hope to hear from you! Thanks

[mbizup]

You'd need recordset code with a nested loop - the 'inner' loop would run from the start to end years; the 'Outer' loop would run through each record in your table...

Something like this:

Do Until rs.EOF
  for intYear = rs!Start to rs!End
    strSQL = "INSERT INTO yourTable (Name, Value, Year) Values ('" & rs!Name & "', '" & rs!Value & "', " & intYear & ")"
     currentdb.execute strSQL, dbfailonerror
  Next
rs.MoveNext
Loop

That code is neither perfect syntactically nor complete, but should give you a general idea to start coding a solution with. Give it your best try, and post back with any issues.

[sanniep]

Hi mbizup, and thanks for your response. Code was almost done, only had to add the recordset and change the fieldnames. Turned out to be simple thanks to your start. Thanks again!