Consolidating records where primary key not equal

[AJo]

Hi,

I hope someone can help me achieve a report that i'm currently struggling with...

I am trying to consolidate data currently in the format:

Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500

to be output as:

Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £75000

Is this possible? If yes, please can you tell me how can achieve it? I have a number of records that need to be consolidated in this way.

Many thanks,
Amanda

 

[hneal98]

Yes.

create the following query:

select Ref = "456/100/001, [Company], [Date], Sum([Amount]) from [TableName] group by [Company], [Date];

Then base your report on this query.

 

[AJo]

Thanks for that, but I have a number of records (with different reference numbers) that need to be consolidated on the same report. How can this be achieved?

For example:
Ref Company Date Amount
456/100/001C Bloggs & Co 12/12/03 £2000
456/100/001LC Bloggs & Co 12/12/03 £4000
456/100/001LT Bloggs & Co 12/12/03 £1500
123/101/002LT Smith & Co 15/11/03 £1000
123/101/002LC Smith & Co 15/11/03 £3000

would become:
Ref Company Date Amount
456/100/001 Bloggs & Co 12/12/03 £7500
123/101/002 Smith & Co 15/11/03 £4000

I thought about using the 'LEFT' function, but I'm not sure how to use it in this context.

Thanks in advance

 

[CosmoKramer]

Group your report by Ref, then add an unbound text box to the group footer that will sum your amount field:

=Sum([amount])

Hoc nomen meum verum non est.