[AccessD] Several records into one record

Stuart,

Does 
Select Distinct SaleNum, count(*) as NoOfSales from tblSales Inner Join
tblDetails on ....

return a field NoOfSales that acts as a counter for each record returned?

As in:
NoOfSales	Key		Amt
 1 		AAAAAAA 	45.00
 2 		AAAAAAA 	67.87
 3 		AAAAAAA 	23.23
 4 		AAAAAAA 	128.93
 1		BBBBBBB	9.99	
 1		CCCCCCC	4.44
 2		CCCCCCC	0.00

Thanks,
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