Grouping Data Problem

Hello all

I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.

e.g

debit �G100 - credit �G100 - debit of �G125 ( I only want to see the debit of �G125)

I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.

How can I achieve this in SQL?

All help appreciated.

hi how does your table look like?

can you please post the schema

|||

I am still a little new to SQL so am not entirely sure what the 'schema' is so here's the code from the create table command: -

Please note that I am importing data from an old DB3 file so don't have a lot of control over the data structure.

(
[KEY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[POL_IDX] [nvarchar] (12) COLLATE Latin1_General_CI_AS NULL ,
[ITEM] [nvarchar] (6) COLLATE Latin1_General_CI_AS NULL ,
[AGCY] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BRCH] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[DEPT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[MONTH] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[AMT] [float] NULL ,
[TBAL] [float] NULL ,
[DESC] [nvarchar] (30) COLLATE Latin1_General_CI_AS NULL ,
[EDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[INPUTDATE] [nvarchar] (8) COLLATE Latin1_General_CI_AS NULL ,
[FLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[TRANS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[BILL] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[ICO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[MCO] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[COM_P] [float] NULL ,
[PR] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P] [float] NULL ,
[PR2] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P2] [float] NULL ,
[BR_P] [float] NULL ,
[TYPE] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[POL] [nvarchar] (25) COLLATE Latin1_General_CI_AS NULL ,
[OINT] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[LOANNUM] [nvarchar] (11) COLLATE Latin1_General_CI_AS NULL ,
[PRT] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CLOSED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[OP_ID] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[CSR] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[JOURNAL] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[DOCTYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[REFER] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PRINTED] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CODE] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[PC] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[BIN] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[SPLIT_PLAN] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[PR3] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[PR_P3] [float] NULL ,
[CO_AMT] [float] NULL ,
[PR_AMT1] [float] NULL ,
[PR_AMT2] [float] NULL ,
[PR_AMT3] [float] NULL ,
[BR_AMT] [float] NULL ,
[PPAID] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PFLAG] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CO_TYPE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE1] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE2] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[PR_TYPE3] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CPAID_MO] [nvarchar] (4) COLLATE Latin1_General_CI_AS NULL ,
[ADJUST] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[STATUS] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
[TYPEGROUP] [nvarchar] (2) COLLATE Latin1_General_CI_AS NULL ,
[REFER1] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[NOTE] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[CONGLOM] [nvarchar] (7) COLLATE Latin1_General_CI_AS NULL ,
[SKIP] [nvarchar] (1) COLLATE Latin1_General_CI_AS NULL ,
[EXTRA] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL
)

|||I have now managed to achived this by using the same approach as I did with Access, however used the min,max options