[AccessD] Splitting data from one row into two

Thanks! I'll give it a try and let you know if I hit any snags. 

Nanette

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bobby Heid
Sent: Monday, April 24, 2006 11:24 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Splitting data from one row into two

Nanette,

I first thought about using a union query, but that would duplicate the
EmpID.  So I'd probably use a temporary table two queries.

Temptable layout:

Fld1 Fld2 Fld3 Fld4 Fld5

Then populate the table with something like (air code):

strSQL="SELECT EmpID,PayGroup,FirstName,LastName,AwardAmt " & _
	"FROM SomeTable " & _
	"ORDER BY EmpID ASC;

strSQL2="SELECT RRC_RRT,PayrollProcAcct,EarningsCode " & _
	"FROM SomeTable " & _
	"ORDER BY EmpID ASC;

set rs=db.openrecordset(strsql,dbopensnapshot)
set rs2=db.openrecordset(strsql2,dbopensnapshot)
with rs
   do while not.eof
	'insert 1st line of data
	strSQL3="INSERT INTO TempTable (Fld1,Fld2,Fld3,Fld4,Fld5) " & _
		"VALUES(" & _
			cstr(!EmpID) & "," & _
			cstr(nz(!PayGroup),'') & "," & _
			cstr(nz(!FirstName),'') & "," & _
			cstr(nz(!LastName),'') & "," & _

			cstr(nz(!AwardAmt),'0') & ";"
	db.execute strsql3,dbfailonerror	'insert the 1st line

	'insert 2nd line of data
	strSQL3="INSERT INTO TempTable (Fld1,Fld2,Fld3) " & _
		"VALUES(" & _
			cstr(nz(!RRC_RRT),'') & "," & _
			cstr(nz(!PayrollProcAcct),'') & "," & _
			cstr(nz(!EarningsCode),'') & ";"
	db.execute strsql3,dbfailonerror	'insert the 2nd line

	.movenext
      loop


Now you can dump export the table to Excel.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gould,
Nanette
Sent: Monday, April 24, 2006 11:53 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Splitting data from one row into two


Hello, all. I have a problem I hope someone can help me solve. I've got
data in a table that I need to export to Excel and each row needs to be
split into two rows for each EmpID. It's the AwardAmt and the RRC_RRT
data that need to appear on separate rows. Does anyone have any ideas of
how I can accomplish this in either Access or Excel?

Here's how the data is in my table:

EmpID	 PayGroup	 FirstName	 LastName	 AwardAmt
RRC_RRT	 PayrollProcAcct	 EarningsCode	
0007871	 MBB	 	 Luis M	 Alcazar	 50		 24.24
56704	 	C	
0063749	 MBB	 	Kay S	 	Alexander	 25
12.12	 	56703	 	T	

Here's how it needs to be in Excel:

EmpID	 PayGroup	 FirstName	 LastName	 AwardAmt
RRC_RRT	 PayrollProcAcct	 EarningsCode	
0007871	 MBB	 	 Luis M	 Alcazar	 50
56704	 	RRA	
0007871	 MBB	 	 Luis M	 Alcazar	 		24.24
56704		RRC	
0063749	 MBB	 	 Kay S	 	Alexander	 25
56703	 	RRA	
0063749	 MBB	 	 Kay S	 	Alexander
12.12	 	56703	 	RRT	


Thanks!
Nanette

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