0

Problem: I have a piece of code that joins two tables on whether the amounts for the respective references from both add to zero. The issue is that I don't know how to include more columns in the resulting query.

SELECT *
FROM (SELECT PayRef, SUM(MerchAmount) AS FAmount
FROM qryPaymentReferenceConversion 
GROUP BY PayRef)  AS qryPaymentReferenceConversion 
INNER JOIN (SELECT Reference, SUM(TransAmount) AS BAmount
FROM tblBankStatementRegisterMaster GROUP BY Reference)  AS tblBankStatementRegisterMaster 
ON tblBankStatementRegisterMaster.Reference =qryPaymentReferenceConversion.PayRef
WHERE qryPaymentReferenceConversion.FAmount + tblBankStatementRegisterMaster.BAmount = 0;

In addition, when I try this alternative, I get a syntax error. Problem: Whenever I compile the code below, I receive a syntax error in the JOIN operation. Is this because I am selecting all fields rather than specifying the Reference field from the bank table? If so, how do I select all tables while maintaining the relationship?

SELECT tblBankStatementRegisterMaster.*, TransAmt 
FROM tblBankStatementRegisterMaster AS BAmount
INNER JOIN (SELECT PayRef, Sum(MerchAmt) AS FAmount 
FROM qryPaymentReferenceConversion GROUP BY PayRef)
ON tblBankStatementRegisterMaster.Reference = qryPaymentReferenceConversion.PayRef 
WHERE qryPaymentReferenceConversion.FAmount + tblBankStatementRegisterMaster.BAmount = 0;
Bob the Builder
  • 119
  • 11
  • 1
    Wasn't this already answered in your other question? Build aggregate query then join that query to table or query. You are doing that. Which table do you want more columns from? Edit question to show sample data and desired result. Alternatively, build report and do aggregate calcs in report design. Might have to do report/subreport. – June7 Jan 20 '21 at 09:30
  • @June7 I've tried looking up a way to do what I want but didn't have any luck. I need to select other fields from the table such as PayDate and PayStatus in order to provide more information for the two fields that the tables are joined on. I guess I can just join this query on the PayRefConv query. – Bob the Builder Jan 20 '21 at 09:38
  • I believe this is similar to what I'm trying to accomplish, but it didn't work for me. https://stackoverflow.com/questions/7489085/select-sum-and-multiple-columns-in-1-select-statement – Bob the Builder Jan 20 '21 at 09:39
  • 2
    Not surprising. Using example from your other question: if Amount data for Reference is aggregated, which PayDate and PayStatus would you want to return, assuming these are in Table1 and different in each record? If they are in Table2, then query join should work. The second SQL needs an alias name for the inner query. JOIN needs to be on the alias name and table. Also, the WHERE clause needs to use the alias name. – June7 Jan 20 '21 at 09:52
  • I wasn't even thinking about the fact that the amount data is aggregated. I think I will need to have a separate query or report that shows the details. Do you think a report would work better? I'm trying to think of a way of showing all of the individual amounts next to the amount from the second table, but the individual amounts also need to be summed. – Bob the Builder Jan 20 '21 at 10:47
  • @June7 Is it possible to show multiple pay dates in one field/record if there are multiple amounts in table 1 but only show one date if there's one amount? The aggregate amount data isn't always going to include multiple lines for the amounts, but of course I need to capture when that occurs. – Bob the Builder Jan 20 '21 at 11:05
  • Already suggested report as possible option. Also, review https://stackoverflow.com/questions/13822588/concatrelated-in-access-using-two-keys – June7 Jan 20 '21 at 20:08

0 Answers0