Help with adding rows (Complex!)

[ohthesilhouettes]

Hi Everyone,

I have a table that lists four columns (Treaty, Country, YearIn, YearOut)
A country can join a treaty in one year and leave the treaty another year.

Here is what the table looks like:

Treaty Country YearIn YearOut
Kyoto FRANCE 1990 1993
Kyoto JAPAN 1990 1995
Ocean CHINA 1980 1983
Ocean FRANCE 1980 1983
Ocean CANADA 1985 1987



I need to combine the YearIn and the YearOut columns into rows of each year. This means adding new rows that repeat with the only difference is the year. The columns YearIn and YearOut serve as the boundary years but I need to automatically fill rows between the range

Here is what I want it to look like.

Treaty Country MemberYear
Kyoto FRANCE 1990
Kyoto FRANCE 1991
Kyoto FRANCE 1992
Kyoto FRANCE 1993
Kyoto JAPAN 1990
Kyoto JAPAN 1991
Kyoto JAPAN 1992
Kyoto JAPAN 1993
Kyoto JAPAN 1994
Kyoto JAPAN 1995
Ocean CHINA 1980
Ocean CHINA 1981
Ocean CHINA 1982
Ocean CHINA 1983
Ocean FRANCE 1980
Ocean FRANCE 1981
Ocean FRANCE 1982
Ocean FRANCE 1983
Ocean CANADA 1985
Ocean CANADA 1986
Ocean CANADA 1987

Is it possible to do this with some kind of count function and append query? Thanks for any help!

-Michelle

[pbaldy]

One way would be to open a recordset on the first table, plus a second recordset on the target table. Within a loop of the first recordset, use the year values in a For/Next loop. Within that loop, use the AddNew method of the second recordset to add records to the table, using values from the first recordset plus the For/Next loop counter.

[ohthesilhouettes]

Hi Paul,

THank you so much for replying!! I am not familiar with using recordsets in Access. How should I go about it? Are there good tutorial places to learn about recordsets? Also could you help me on getting started with the For/Next loop counter?

Thank you!

-Michelle

[stmoong]

Here is a link from MSDN for you to learn how to use DAO Recordset.

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[pbaldy]

How are you coming along Michelle?