Trouble writting Sub Query

[PNGBill]

Much appreciated if the following two sql's can be combined into one, maybe by sub query.

StatementID is the common field.

SELECT tblMemberRepayments.StatementID, Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt
FROM tblMemberRepayments
GROUP BY tblMemberRepayments.StatementID;

SELECT tblBankStatements.StatementID, tblBankStatements.BankID, tblBankStatements.StatementDate, tblBankStatements.StatementAmt, tblBankStatements.ApprovalFlag
FROM tblBankStatements;

tblBankStatements is the Header and holds a value which should be the Total Deposits for the day.

tblMemberRepayments holds the detail and when summed, should equal the value in tblBankStatements.

(The two fields provide a check to ensure all repayment records are entered correctly)

I want one sql that can give me a record for each date (tblBankStatements) with the two values showing, One of them the Summed value (1st sql above) and the other the entered value in tblBankStatements.

 

[PNGBill]

Here is one of my attempts but it lists all records where I want it Grouped By StatementID

SELECT S.StatementID, S.BankID, S.StatementDate, S.StatementAmt, S.ApprovalFlag, R.PaymentAmt AS Expr1
FROM tblBankStatements AS S, tblMemberRepayments AS R
WHERE (((S.StatementID)=[R].[StatementID]) AND (((SELECT SUM(R.PaymentAmt) AS RepayTotal
FROM tblBankStatements S, tblMemberRepayments R
WHERE S.StatementID = R.StatementID))<>False));

 

[PNGBill]

My Latest attempt

SELECT tblBankStatements.StatementDate, tblBankStatements.BankID, Sum(tblMemberRepayments.PaymentAmt) as RepayTotal
FROM tblBankStatements
Left Outer JOIN tblBankStatements ON tblBankStatements.StatementID = tblMemberRepayments.StatementID
GROUP BY tblMemberRepayments.StatementID

Syntax error in Join Operation

 

[vbaInet]

Not quite sure what you're after but maybe all you need is an alias field with sum in the tblBankStaments query:

AliasName: (SELECT Sum(Q.PaymentAmt) FROM tblMemberRepayments AS Q WHERE Q.StatementID = tblBankStatements.StatementID)

 

[PNGBill]

Sorry vbaInet, I am lost on this.

 

[PNGBill]

This is the query I am trying to replace as it requires two queries.

this one:

SELECT tblBankStatements.StatementID, tblBankStatements.BankID, tblBankStatements.StatementDate, tblBankStatements.StatementAmt, QrySumofRepaymentsbyStatID.SumOfPaymentAmt, [StatementAmt]-[SumofPaymentAmt] AS Difference, tblBankStatements.ApprovalFlag, tblBankStatements.ApprovalDate
FROM tblBankStatements INNER JOIN QrySumofRepaymentsbyStatID ON tblBankStatements.StatementID = QrySumofRepaymentsbyStatID.StatementID
WHERE (((tblBankStatements.StatementDate) Between [Enter first date of period] And [Enetr last date of period]))
ORDER BY tblBankStatements.BankID, tblBankStatements.StatementDate;

and this one:

SELECT tblMemberRepayments.StatementID, Sum(tblMemberRepayments.PaymentAmt) AS SumOfPaymentAmt
FROM tblMemberRepayments
GROUP BY tblMemberRepayments.StatementID
ORDER BY tblMemberRepayments.StatementID;

 

[vbaInet]

Copy and paste what I wrote into a field in the query for tblBankStatements. Look at the values and see if that's what you're after.

 

[PNGBill]

When just about to give up this worked!!

Thanks again vbaInet

SELECT tblBankStatements.StatementID, tblBankStatements.BankID, tblBankStatements.StatementDate, tblBankStatements.StatementAmt, tblBankStatements.ApprovalFlag,  (SELECT Sum(Q.PaymentAmt) FROM tblMemberRepayments AS Q WHERE Q.StatementID = tblBankStatements.StatementID) AS SumOfRepayments
FROM tblBankStatements;

 

[PNGBill]

Now a side issue to this story...

The sql works and the report it is the source code also works but....

I am unable to have a =Sum([controlname]) in the report footer because it says I can not have multiple Group By in a sub query.

Is there a work around for this ?

Not critical as in this case the totals were mostly decoration but next time they may not be.

 

[vbaInet]

I don't see where that error is coming from. Could explain a bit more?

You could create yet another query off the one you just built selecting all the fields. Use this new query as the record source of your report. See if that works.

 

[PNGBill]

My goal is to have just one record source and that inserted into the report or form so nothing showing in the Queries Objects list.

Will scratch my head on this as an alternative would be to create a public function to sum the individual repayments by date by bank and then have this function replace the subquery, as we did yesterday.