Problem with SQL query

[arunakumari02]

I have a table tblMain with fields PID, PName, Check (Here the Check value is True/False)

tblMain

PID PName Check
1 X True
2 Y True
3 Z False

I have another table tblChange with fields PID,RID,Month,Value

tblChange

PID RID Month Value
1 1 1/1/2008 3
1 2 2/1/2008 4
2 1 1/1/2008 5
2 2 3/1/2008 2
3 1 4/1/2008 6


The Question:

First I have to select only the records from the tblMain which have Check as True

Here say I get 2 records with PID 1 and 2

Now I have to write a SQL query to select the records from tblChange, which group by's RID and Month, Sum(value) for both the PID 1 and 2 (which are different)

From the above tables the output should be:

RID Month Sum(Value)
1 1/1/2008 8 -- For PID 1 and 2, GROUP BY (RID, Month), Sum(Value)
2 2/1/2008 4
2 1/1/2008 2

Code I have written sofar:

strSQL = "SELECT * FROM tblMain WHERE CHECK = True"
    Set rstMain = CurrentDb.OpenRecordset(strSQL)
 
    If rstMain.RecordCount > 0 Then
 
        Do Until rstMain.EOF
 
          [B]  strSQL = "SELECT SUM(Value) AS DemandValue, RID, Month FROM tblChange GROUP BY RID, Month "[/B]

My problem here is how to Sum the Value for different project ID and also Group By for RID and Month
Any Help

 

[WayneRyan]

a,

I'm a little confused about your sums, but this should get you closer:

Select A.PID, B.RID, B.Month, Sum(B.Value)
From   tblMain As A Inner Join tblChange As B On
         A.PID = B.PID
Group By A.PID, B.RID, B.Month
Having A.Check
Order By A.PID, B.RID, B.Month

hth,
Wayne