Combine rows into one row

[kbushnell]

I have a macro that I have created to format a worksheet for an import into another software. I have one more piece I would like to add but I am not sure how. I have four rows of data. A contains acct. numbers, B contains unique identifiers, C contains amounts, and D contains a date. What I need for a group of acct. numbers in column A combine any amounts in column C if column B begins with PN. For Example:
3050092010 831681101 15.223 11/14/2006
3050092010 939330106 3175.766 11/14/2006
3050092010 PN6009230 336.24 11/14/2006
3050092010 PN6009776 389.45 11/14/2006
3050092010 PN6009784 4257.9 11/14/2006
3050100047 831681101 1097.502 11/14/2006
3050100047 939330106 619.594 11/14/2006
3050100047 PN6010477 1956.29 11/14/2006
3050100047 PN6010691 7108.36 11/14/2006
The macro would return:
3050092010 831681101 15.223 11/14/2006
3050092010 939330106 3175.766 11/14/2006
3050092010 Loan 4983.59 11/14/2006
3050100047 831681101 1097.502 11/14/2006
3050100047 939330106 619.594 11/14/2006
3050100047 Loan 9064.65 11/14/2006

So it would take the total PN's from column B for each Acct. ID in column and make one row with a total that says Loan. Any help is greatly appreciated.
I posted this question last week but the post must have been deleted because it is not found now.

 

[traingamer]

Try (tested):

SELECT tbl1.acctnum, IIf(Left([uniqid],2)="PN","LOAN",[uniqid]) AS gb, Sum(tbl1.amt) AS SumOfamt, Max(tbl1.dte) AS MaxOfdte
FROM tbl1
GROUP BY tbl1.acctnum, IIf(Left([uniqid],2)="PN","LOAN",[uniqid]);

returns:
[tt]
acctnum gb SumOfamt MaxOfdte
102 108 $17.00 11/14/2006
102 109 $15.00 11/14/2006
102 LOAN $26.00 11/14/2006
103 145 $99.95 11/14/2006
103 LOAN $22.72 11/14/2006[/tt]
for raw data of:
[tt]
acctnum uniqid amt dte
102 108 $17.00 11/14/2006
102 109 $15.00 11/14/2006
102 PN22 $9.00 11/14/2006
102 PN13 $17.00 11/14/2006
103 145 $99.95 11/14/2006
103 pn11 $16.50 11/14/2006
103 pn5689 $6.22 11/14/2006
[/tt]

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[kbushnell]

Greg, I am not able to make this code work. I created a spreadsheet with the data you have above and cannot make the code work. I defined a range named tbl1 and used the exact code but cannot get it to function. Any thoughts as to why? Thanks

 

[traingamer]

Any thoughts as to why?

Well, as this is the Access VBA forum, I was giving an Access solution. I am somewhat less familiar with Excel macros. Sorry.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[kbushnell]

Sorry about that. I grabbed the wrong forum. I will post in the correct one.