[AccessD] Several records into one record

I thought that he was wanting to combine the 1st three related records into
one record as in:

Rec 1 AAAAAAA 45.00
Rec 2 AAAAAAA 67.87
Rec 3 AAAAAAA 23.23
Rec 4 AAAAAAA 128.93

Into a new table such as:

AAAAAAA 45.00 67.87 23.23

Bobby


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, September 20, 2005 6:21 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com