[AccessD] Several records into one record

On 20 Sep 2005 at 14:37, Reuben Cummings wrote:

> Sale#			ParcelNum
> 

As an aside, I'ts a good idea to avoid the "#" sign in fieldnames, VBA 
thinks that a trailing #  is a type declaration character for a double 
precision number.

> I need this in the new table
...
> Basically, I need to take multiple records and write them to one row of a
> new table - but only three of them max.  And there has to be three even if
> there are only 1 or 2 details of original data.
> 

Set rs = currentdb.openrecordset ("Select Distinct SaleNum, count(*) as 
NoOfSales from tblSales Inner Join tblDetails on ...." )

While not rs.eof
   Select case rs!NoOfSales
    Case 1
     			'write record using an SQL Insert
     			' write two blank records
    Case 2
      		'write records using an SQL Insert 
      		' write one blank record
    Case > 2
      		'write records using an SQL Insert
    End Select
rs.movenext
Wend

In the first two cases, use an
"Insert into tblNewTable (Select SaleNum,  ......."
in the third case use
"Insert into tblNewTable (Select TOP 3 Salenum, ...."


-- 
Stuart