[AccessD] Several records into one record

Can you post the code you are currently trying to use?  A fresh set of eyes
might spot the exit loop problem you are having.

Doris Manning
mikedorism at verizon.net

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