MS ACCESS Group/Sort Consecutively
<p>I have a problem while Using MS ACCESS. I have a table with about 1.2 Mio entries. </p>

<pre><code>*Column1   Column2      Column3*    
**Name       Code         Datum**

aaa           111          01.01.01

aaa           111          02.01.01

aaa           222          03.01.01

aaa           222          04.01.01

aaa           222          05.01.01

aaa           111          06.01.01

aaa           111          07.01.01

aaa           111          08.01.01

bbb           333          01.01.01

bbb           333          02.01.01

bbb           111          03.01.01

bbb           111          04.01.01

bbb           333          05.01.01

bbb           333          06.01.01

ccc           222          01.01.01

ccc           222          02.01.01

ccc           222          03.01.01

ccc           222          04.01.01
</code></pre>

<p>This table should be summarized to:</p>

<pre><code>**NAme      Code         ValidFrom     ValidTo

aaa           111          01.01.01        02.01.01

aaa           222          03.01.01        05.01.01       

aaa           111          06.01.01        08.01.01

bbb           333          01.01.01        02.01.01

bbb           111          03.01.01        04.01.01    

bbb           333          05.01.01        06.01.01

ccc           222          01.01.01        04.01.01
</code></pre>

<p>The Problem is that I have a VBA code which works with multiple recordsets, one is looping through the 1.2 mio entries and compares whether the Name and COde is the same and updates therefore keeps updating the Valid To date, as soon as one of them change the Name, Code and Valid From Date is taken from the first entry. After that the secon recordset is set on the level of the first recordset.
This method however takes hours....</p>

<p>Is there a more elegant way? Maybe with SQL? using group by and then min max for the dates. This idea came to my mind, but unfortunately I have repeating Codes within the same name. :-(</p>

<p>PS: Idially I need it the following form:</p>

<pre><code>Name   Code            Valid From                     Valid to

aaa    111,222,111     01.01.01,03.01.01,06.01.01     02.01.01,05.01.01,08.01.01

bbb    333,111,111     01.01.01,03.01.01,05.01.01     02.01.01,04.01.01,06.01.01

ccc    222             01.01.01                       04.01.01
</code></pre>

<p>I am really grateful for your help and appreciate it.</p>

<p>regards</p>