So little code, such big results! Thank you all for your input, very valuable.

I will be keeping this little trick up my sleve for future use.

Peter

I will get this right in the end, I'm so sorry for making life more difficult.

The correct result set should be...

fh.Contact_number, fh.transaction_date, fhd.*

8,'2002-11-01',120752,1,1,10.00,'DON',0,'TDG3-03',1,'Z',0.00,NULL,NULL,N,5000015,10.00,0.00

Thanks.

I've just realised I copied and pasted some unnecessary data in one of the above posts which I don't want to confuse things.

Basically, with the sample data supplied, I would...

This code gives me too many rows...

SELECT fh.contact_number

,fhd.*

FROM financial_history_details fhd

left outer join financial_history fh

on fhd.batch_number = fh.batch_number

and fhd.transaction_number = fh.transaction_number

WHERE fhd.amount =(SELECT MAX(fhd2.amount)

FROM financial_history_details fhd2

left outer join...

Insert Data:

INSERT INTO [care_baseline].[dbo].[financial_history]

([batch_number]

,[transaction_number]

...

Thanks for offering to get back to me guys... Here's the first batch of information as requested.

Create financial history table:

CREATE TABLE [dbo].[financial_history](

[batch_number] [int] NOT NULL,

[transaction_number] [smallint] NOT NULL,

[contact_number] [int]...

Nearly there.

I now need to turn this select statement into a delete but am not having much luck.

select * from declaration_tax_claim_lines

inner join batch_transactions

on declaration_tax_claim_lines.batch_number = batch_transactions.batch_number

and declaration_tax_claim_lines.transaction_number = batch_transactions.transaction_number

where batch_transactions.transaction_date...

That seems to have worked on my test system, I will be trying to run it later on live when I have no users getting in my way.

Just out of...

Create table code as requested...

/****** Object: Table [dbo].[declaration_tax_claim_lines] Script Date: 03/12/2009 09:07:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[declaration_tax_claim_lines](

[claim_number] [int] NOT NULL,

[cd_number] [int] NOT...

Thanks for replying guys.

I can't get the update code to parse 'Incorrect syntax near 'TABLE2' (there's also not a transaction_date in table 1 but I presume that the join makes...

Thank you Karl and Mark for your input, that's brilliant.

I hope I may be of some help to some of you in the future but until then, I know where...