I have 4 tables with the following structure.

Colors:

color_code number(PK)
color_name Text

transfermain:

trindno Autonumber(PK)
job_code Text
factory_code Text
trdate Date/Time

Transfersub:

transid Autonumber(PK)
trindno Number(FK from transfermain)
color_code Number(FK)
qty Number

Dailyoutput:

indexno Autonumber(PK)
transid Number(FK from transfersub)
Qty Number
trdate Date/time

The data is like

colors:

color_code Color_name
---------------------------
1 Green
2 Combo
3 Chi chi combo
4 Navy

Transfermain:

trindno job_code fact_code
-----------------------------------------
1 04A001 ATH
2 04A001 DFB
3 04A002 PKS
4 04A001 MON

transfersub:

transid trindno color_code Qty
-----------------------------------
1 1 2 3000
2 1 3 2000
3 2 2 2500
4 2 3 3000
5 2 4 4500
6 3 2 1500
7 4 1 1000

dailyoutput:

indexno transid qty
----------------------------
1 1 3000
2 3 1000
3 4 2000
4 2 2000
5 5 3000


Means, the same job is assigned to different factories with same/diff colors and quantities. In the dailyoutput table, color for each factory has only one record. Its a cumulative quantity.

I want to display the resport to show all colors for the job(input) and quantity(sum of qty group by color_code from transfersub as assignedqty) and quantity(sum of quantity for each color from dailyoutput as finishedqty).

Ex: Report for job 04A001
--------------------------

color_name Assigned Qty Finished Qty
--------------------------------------------
Combo 7000 4000
Chi chi combo 6000 4000
Navy 1000 0


I have given the query as

SELECT colors.color_name, transfersub.qty, dailyoutput.qty
FROM colors RIGHT JOIN ((transfermain RIGHT JOIN transfersub ON transfermain.trindno = transfersub.trindno) LEFT JOIN dailyoutput ON transfersub.transid = dailyoutput.transid) ON colors.color_code = transfersub.color_code
WHERE (((transfermain.job_code)="04A001"));

It is displaying the same color more than once(coz, the same color can be assigned to different factories). I want to sum up all the quantities for each color.

How can i get the output as i needed.

SELECT colors.color_name, SUM(transfersub.qty) as SumofTrans, SUM(dailyoutput.qty) AS SumofDailyOut
FROM colors RIGHT JOIN ((transfermain RIGHT JOIN transfersub ON transfermain.trindno = transfersub.trindno) LEFT JOIN dailyoutput ON transfersub.transid = dailyoutput.transid) ON colors.color_code = transfersub.color_code
GROUP BY colors.color_name
WHERE (((transfermain.job_code)="04A001"));