Need a logic/script for my below problem

[#1]

Dear All,

Could any one please help me how to write a script for the below problem.

Please note that "Budget table" is uploaded from excel sheet and "Revenue table" is a selected list from 3 tabels.

Budget table:

BDEPARTNO	BCUSTNO	BAREACODE	BSERVCODE	BAMOUNT
70				25000
50				40000
70	100001			5000
50	100004			10000
70	100001	Fyn		2500
70	100001	Jylland		2500
70	100001	Jylland	1052	5000
70		Jylland		20000
70		Jylland	1052	10000

Revenue table:

DEPARTNO	CUSTNO	AREACODE	SERVCODE	AMOUNT
50	100004			500
50	100001	Fyn	1052	135
50	100004		7011	175
50	100001	Fyn	2531	25379
50	100001		2532	8459
50	100001		2532	10151
50	100001		2532	2537
70	100001		7011	100
70	100001	Fyn	7011	100
70	100001	Jylland	7011	559
70	100001		1052	190
70	100004			190
70	100004	Jylland	1052	175
70	100001	Jylland	1052	545
70	100840		7011	335
70	100840		7011	22680
70	100001		7011	485

Now I have to add another column of BRAMOUNT in "Budget table". Here BRAMOUNT is a calculated amount from "Revenue table" based on the below conditions.

If any of the field BDEPARTNO/BCUSTNO/BAREACODE/BSERVCODE has been filled with a value in "Budget table", then I have to collect the records from "Revenue Table" and sum up of their AMOUNT's and move it to the new field BRAMOUNT of "Budget table".

If non of the filed BDEPARTNO/BCUSTNO/BAREACODE/BSERVCODE has been selected then I have sum up all the record's AMOUNT and move it to the new field BRAMOUNT of "Budget table".

At last the resulted "Budget table" looks like below:

BDEPARTNO	BCUSTNO	BAREACODE	BSERVCODE	BAMOUNT	BRAMOUNT
70				25000	25359
50				40000	47336
70	100001			5000	949
50	100004			10000	675
70	100001	Fyn		2500	100
70	100001	Jylland		2500	1104
70	100001	Jylland	1052	5000	545
70		Jylland		20000	1279
70		Jylland	1052	10000	720

Thanks in advane.

[Anonymous]

Use a join between the tables and apply your logic.

I think you can easly solve the problem in the script.

regards Matteo

[#3]

Hi,

Here you are a possible solution.

I hope it helps you.

Regards.