[AccessD] Several records into one record

You could do it via vba and a recordset,  Something like:

'warning - air code
'assuming that your logical key - name, ssn, etc is in LogKey
strKey=""
Do while not rs.eof
   'reset our count and test key
   if rs!LogKey<>strkey then
	i=1
      strkey=rs!LogKey
      else
        i=i+1
      end if
   'if have less than 3 of a given record, process it
   'if more than 3rd record, then it will be skipped
   if i<=3 then
	select case I
	   case 1:
           'write 1st part
	   case 2:
           'write 2nd part
	   case 3:
           'write 3rd part
         end select
      endif
   'get next record
   rs.movenext
   loop

Hope that I understood your issue correctly.

Bobby


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Reuben Cummings
Sent: Tuesday, September 20, 2005 3:37 PM
To: AccessD
Subject: [AccessD] Several records into one record


OK, I've worn myself out...

I have a table that may have any number of records related to one sale
(let's call them details).

I need to create another table that holds ONE record for each sale and
within that one new record there should be three of those details.  If there
is only one detail in the child table I need to write it to the new table
and then write zeros (0) to the fields as space holders for the other two
details on this record.  If there are two details, I will write two details
to the new table and zeros for the one missing record.

Original tables
tblSales(1)		tblDetails(many)     Several ParcelNum's for every
sale record
ID------------------	fkSales
Sale#			ParcelNum

I need this in the new table
tblSalesExport
ID
Sale#
Parcel1
Parcel2
Parcel3

However, my trouble comes from trying to stop at only three details.  I
can't get out of the damn loop at the right time.

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.

Can anyone help?

And please don't suggest table structure changes.  The State of Indiana has
come up with this brilliant reporting system without regard for the actual
data.  Even if there are 46 parcels in one sale the state only wants to see
three of them.

Reuben Cummings
GFC, LLC
812.523.1017




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