Skip to Content
0
Former Member
May 15, 2008 at 03:30 PM

Summarizing data

21 Views

Post Author: ishaame

CA Forum: Data Connectivity and SQL

I have am trying to create a report taking data from two linked tables. The link gives one-to-many. The query creates for each matching value in table 2 ( the right part of the sample beginning from column "SYMBOL") a copy of values of table one. Using summaries end up giving un realistic values as it simply sums everything. Here is a sample:

ACCOUNT

ACTIVITY

DONOR

COSTCENTER

Budget

Actual

SYMBOL

DTE

RE Split in CHF

RE Foreign Amnt

RE-1 Year

ACT

C1

3410

YYC1

1178

C1

0.00

201,750.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

201,750.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

201,750.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

201,750.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

204,750.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

204,750.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

204,750.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

204,750.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

206,750.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

206,750.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

206,750.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

206,750.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

208,175.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

208,175.00

EUR

09/10/2007

208,175.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

208,175.00

EUR

11/01/2007

201,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

0.00

208,175.00

EUR

11/07/2007

206,750.00

125,000.00

2007

YYC1

3410

YYC1

1178

C1

64,333.33

0.00

EUR

10/04/2007

204,750.00

125,000.00

2007

YYC1

Can any one help and tell how to get only one line from table one then all corresponding values in table two without repeating values of in table one.

Thanks in advance

I. Shaame