[AccessD] Several records into one record

I am, Bobby.

I'm using Bobby's code as a base to start and I'm getting pretty close now.

However, I have to go help 1st graders (my son's class) in the computer lab
today so this project gets to wait.

Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Bobby Heid
> Sent: Wednesday, September 21, 2005 9:28 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>