Need a summing up query between 2 dates

Question

TblInvoice has OwnerID, InvoiceDate,  InvoiceAmount, Status

tblAccountStatus has OwnerID, PaymentDate, PaymentAmount

I want to create a Subform that has to Date boxes. tbDateFrom, tbDateTo

Then I want the report/query to show all Totals between the 2 dates that the field Status is "Active"

Thanks for any help..............Bob

---

xxx

Answer 1

Also I should have said the totals being InvoiceAmount minus PaymentAmount

Thanks for any help..Bob

---

xxx

Answer 2

Hello TrunipOrange,

You could query like

SELECT TblInvoice.*, TblInvoice.InvoiceAmount-Nz(tblAccountStatus.PaymentAmount) AS Totals
FROM TblInvoice 
LEFT JOIN tblAccountStatus ON TblInvoice.OwnerID = tblAccountStatus.OwnerID
WHERE (((TblInvoice.InvoiceDate) Between [Forms]![MySubForm].[Controls]![tbDateFrom] And [Forms]![MySubForm].[Controls]![tbDateTo])) 
And TblInvoice.Status="Active"

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

As a query using a LEFT OUTER JOIN cannot be restricted on a column in the right side of the join, it will be necessary to join the tblInvoice table to a subquery which returns the sum of payments within the data scale, referencing the text box controls in the subform as parameters.  The query would thus be along the following lines, and used as the subform's RecordSource property:

PARAMETERS Forms!frmOwners!sfcAccountStatus.Form!tbDateFrom DATETIME, Forms!frmOwners!sfcAccountStatus.Form!tbDateTo DATETIME;
SELECT tblInvoice.OwnerID, SUM(InvoiceAmount) AS TotalInvoiceAmount, NZ(TotalPaymentAmount,0) AS TotalPayments,
SUM(InvoiceAmount) -NZ(TotalPaymentAmount,0) AS BalanceDue
FROM TblInvoice LEFT JOIN
    (SELECT OwnerID, SUM(PaymentAmount)
     AS TotalPaymentAmount
     FROM tblAccountStatus
     WHERE PaymentDate BETWEEN Forms!frmOwners!sfcAccountStatus.Form!tbDateFrom
     AND Forms!frmOwners!sfcAccountStatus.Form!tbDateTo GROUP BY OwnerID)  AS Payments ON tblInvoice.OwnerID = Payments.OwnerID
WHERE Status = "Active" AND InvoiceDate
BETWEEN Forms!frmOwners!sfcAccountStatus.Form!tbDateFrom  
AND Forms!frmOwners!sfcAccountStatus.Form!tbDateTo
GROUP BY tblInvoice.OwnerID, NZ(TotalPaymentAmount,0);

where:

1.  frmOwners is the name of the parent form
2.  sfcAccountStatus is the name of the subform control in the parent form's Controls collection, which houses the subform.

The subform should be requeried in the AfterUpdate event procedures of each of the text boxes in which the date range is defined with:

Me.Requery

---

Ken Sheridan, Stafford, England

Answer 4

Thanks Ken, I have just realized I don't have a Status "Active" field in either of those Tables. tblOwnerInfo.Status and tblOwnerInfo.OwnerID If that's a problem I will just leave Status out and create a record for every OwnerID

Thanks very much for your help.................Bob

---

xxx

Answer 5

Ken, I have given you a couple of wrong field names, Also can I just have the one form to open a report.

The report being frmFinanicalYearAll

If I cant have the tblOwnerInfo Status [Active] I will just create a total for every OwnerID in tblInvoice

I have changed names in your code, How does it look to you

Thanks for all your help....Bob

PARAMETERS Forms!FrmFinanicalYearAll!tbDateFrom DateTime, Forms!FrmFinanicalYearAll.Form!tbDateTo DateTime;
SELECT TblInvoice.OwnerID, Sum(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount, Nz(TotalPaidAmount,0) AS TotalPayments, Sum(OwnerPercentAmount)-Nz(TotalPaidAmount,0) AS BalanceDue, TblInvoice.OwnerName
FROM TblInvoice LEFT JOIN (SELECT OwnerID, SUM(PaidAmount)
      AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE InvoiceDate BETWEEN Forms!FrmFinanicalYearAll!tbDateFrom
      AND Forms!FrmFinanicalYearAll!tbDateTo GROUP BY OwnerID)  AS Payments ON TblInvoice.OwnerID = Payments.OwnerID
WHERE (((TblInvoice.InvoiceDate) Between [Forms]![FrmFinanicalYearAll]![tbDateFrom] And [Forms]![FrmFinanicalYearAll]![tbDateTo]))
GROUP BY TblInvoice.OwnerID, Nz(TotalPaidAmount,0), TblInvoice.OwnerName;

---

xxx

Answer 6

I don't see any obvious problems at first sight, but it's down to you to test it, and, if necessary, debug it.

---

Ken Sheridan, Stafford, England

Answer 7

Thanks Ken, When testing this code, Date selected on form then open report. I get a parameter value error on "BalanceDue" . When the report opens I am getting the names of the Names in multiple and no BalanceDue

Thanks for any help....Bob

---

xxx

Answer 8

---

xxx

Answer 9

I think I'll need to have the tables to debug this.  Please post the relevant tables to a publicly shared location in OneDrive or similar, and include the link in a reply here.

---

Ken Sheridan, Stafford, England

Answer 10

Thanks Ken

https://app.box.com/s/mpd141y7fcesqjvuescw

Password: bobby3652

File: StableSizeYourData.accdb

---

xxx

Answer 11

I can't log in without your user name which you obviously would not want to expose here.  Instead mail the file direct to me at the following munged address:

kenwsheridan<at>yahoo<dot>co<dot>uk

---

Ken Sheridan, Stafford, England

Answer 12

Ken, Did you get my database?........Thanks for your help....Bob

---

xxx

Answer 13

Ken, Did you get my database?

I've not received it.  Are you sure you demunged my address correctly?

---

Ken Sheridan, Stafford, England

Answer 14

Oops forgot the w in your name, thanks Ken I will send again

---

xxx

Answer 15

Got it.

Your query currently refers to an InvoiceDate column in tblAccountStatus, but there is no such column.  I'm guessing it should refer to the BillDate column.

You said in your original post that the text box controls were in a subform.  When I wrote the SQL for the query I assumed this to be the case.  Looking more closely at your SQL, however, the parameters are inconsistent in this respect, so, if the text boxes are in fact in a parent form named FrmFinancialYearAll, the query would be:

PARAMETERS Forms!FrmFinancialYearAll!tbDateFrom DATETIME,
Forms!FrmFinancialYearAll!tbDateTo DATETIME;
SELECT TblInvoice.OwnerID,
SUM(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount,
NZ(TotalPaidAmount,0) AS TotalPayments,
SUM(OwnerPercentAmount)-NZ(TotalPaidAmount,0) AS BalanceDue, TblInvoice.OwnerName
FROM TblInvoice LEFT JOIN
     (SELECT OwnerID,
      SUM(PaidAmount) AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE BillDate BETWEEN Forms!FrmFinancialYearAll!tbDateFrom
      AND Forms!FrmFinancialYearAll!tbDateTo
      GROUP BY OwnerID)  AS Payments
ON TblInvoice.OwnerID = Payments.OwnerID
WHERE TblInvoice.InvoiceDate BETWEEN
Forms!FrmFinancialYearAll!tbDateFrom
AND Forms!FrmFinancialYearAll!tbDateTo
GROUP BY TblInvoice.OwnerID, NZ(TotalPaidAmount,0), TblInvoice.OwnerName;

If, on the other hand, they are in a subform within FrmFinancialYearAll, and the subform is housed in a subform control named sfcDateParams for instance, then the query would be:

PARAMETERS Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom DATETIME,
Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo DATETIME;
SELECT TblInvoice.OwnerID,
SUM(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount,
NZ(TotalPaidAmount,0) AS TotalPayments,
SUM(OwnerPercentAmount)-NZ(TotalPaidAmount,0) AS BalanceDue, TblInvoice.OwnerName
FROM TblInvoice LEFT JOIN
     (SELECT OwnerID,
      SUM(PaidAmount) AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE BillDate BETWEEN Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom
      AND Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo
      GROUP BY OwnerID)  AS Payments
ON TblInvoice.OwnerID = Payments.OwnerID
WHERE TblInvoice.InvoiceDate BETWEEN
Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom
AND Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo
GROUP BY TblInvoice.OwnerID, NZ(TotalPaidAmount,0), TblInvoice.OwnerName;

---

Ken Sheridan, Stafford, England

Answer 16

Thanks Ken for your help,

Have a problem with OwnerName. If I slightly change the name in Owners Details when the Invoice is created for that month the changed name appears with the same OwnerID. So the report that is created from your code gives me multiple records if I have changed the same slightly. I think you may have to delete tblInvoice.OwnerName and  add tblOwnerInfo.OwnerID to the code so as it just gives the OwnerName in there and ignore OwnerName in tblInvoice..........Thanks ....Bob

Example below of horse name changed 3 times

---

xxx

Answer 17

Sorry Ken, tblOwnerInfo wont help as there is 3 fields for there Name. This query should be good

Thanks Bob

qryClientNameAllAll

SELECT tblOwnerInfo.OwnerID, ([OwnerLastName]+", ") & [OwnerFirstName] AS ClientName, tblOwnerInfo.Status
FROM tblOwnerInfo
WHERE (((tblOwnerInfo.Status)="Active" Or (tblOwnerInfo.Status)="Finished"))
ORDER BY tblOwnerInfo.OwnerLastName, tblOwnerInfo.OwnerFirstName;

---

xxx

Answer 18

Thanks Ken, It worked with the query, thanks for all your help fantastic and much appreciated Bob

PARAMETERS Forms!FrmFinYearAll!tbDateFrom DateTime, Forms!FrmFinYearAll!tbDateTo DateTime;
SELECT TblInvoice.OwnerID, Sum(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount, Nz(TotalPaidAmount,0) AS TotalPayments, Sum(OwnerPercentAmount)-Nz(TotalPaidAmount,0) AS BalanceDue, qryClientNameAllAll.ClientName
FROM qryClientNameAllAll INNER JOIN (TblInvoice LEFT JOIN (SELECT OwnerID, 
      SUM(PaidAmount) AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE BillDate BETWEEN Forms!FrmFinYearAll!tbDateFrom
      AND Forms!FrmFinYearAll!tbDateTo 
      GROUP BY OwnerID)  AS Payments ON TblInvoice.OwnerID = Payments.OwnerID) ON qryClientNameAllAll.OwnerID = TblInvoice.OwnerID
WHERE (((TblInvoice.InvoiceDateInv) Between [Forms]![FrmFinYearAll]![tbDateFrom] And [Forms]![FrmFinYearAll]![tbDateTo]))
GROUP BY TblInvoice.OwnerID, Nz(TotalPaidAmount,0), qryClientNameAllAll.ClientName;

---

xxx

Answer 19

I don't think you need the additional query.  Try this:

PARAMETERS Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom DATETIME,
Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo DATETIME;
SELECT TblInvoice.OwnerID,
SUM(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount,
NZ(TotalPaidAmount,0) AS TotalPayments,
SUM(OwnerPercentAmount)-Nz(TotalPaidAmount,0) AS BalanceDue,
(OwnerLastName+", ") & OwnerFirstName AS ClientName
FROM tblOwnerInfo INNER JOIN (TblInvoice LEFT JOIN
     (SELECT OwnerID,
      SUM(PaidAmount) AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE BillDate BETWEEN Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom
      AND Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo
      GROUP BY OwnerID)  AS Payments
ON TblInvoice.OwnerID = Payments.OwnerID)
ON tblOwnerInfo.OwnerID = TblInvoice.OwnerID
WHERE TblInvoice.InvoiceDate BETWEEN Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom
AND Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo
GROUP BY TblInvoice.OwnerID, Nz(TotalPaidAmount,0), (OwnerLastName+", ") & OwnerFirstName;

I always try and base queries on base tables wherever possible.  Having queries referencing queries can makes for maintenance problems.  I've seen some real monsters in my time, where people have queries referencing queries referencing queries and so on......  When I've dug down through them all I've found the same thing being done multiple times in the different queries.  Keeping queries as tight as possible usually makes for greater efficiency.

---

Ken Sheridan, Stafford, England

Answer 20

Thanks Ken tried your code and got this error......Regards Bob

---

xxx

Answer 21

It turns out that the reason is that the references to the NZ function are in some places in upper case and in others in proper case.  The usual convention in SQL is for all keywords to be in upper case, and table and column names to be in mixed or lower case.  Access unfortunately changes some keywords to mixed case, which is what's happened here.  I thought I'd  changed them all back to upper case in my reply, but clearly missed some.

The strange thing is that, by an large, Access is case insensitive, so I would not have expected the discrepancies in the references to the NZ function to make any difference.  They clearly do, however, which is a complete surprise to me.  I've never had this happen before, but I'll know to be more careful in future.  The following, in which all keywords are in upper case, definitely opens without error in the file you sent me:

PARAMETERS Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom DATETIME,
Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo DATETIME;
SELECT TblInvoice.OwnerID,
SUM(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount,
NZ(TotalPaidAmount,0) AS TotalPayments,
SUM(OwnerPercentAmount)-NZ(TotalPaidAmount,0) AS BalanceDue,
(OwnerLastName+", ") & OwnerFirstName AS ClientName
FROM tblOwnerInfo INNER JOIN (TblInvoice LEFT JOIN
     (SELECT OwnerID,
      SUM(PaidAmount) AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE BillDate BETWEEN Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom
      AND Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo
      GROUP BY OwnerID)  AS Payments
ON TblInvoice.OwnerID = Payments.OwnerID)
ON tblOwnerInfo.OwnerID = TblInvoice.OwnerID
WHERE TblInvoice.InvoiceDate BETWEEN Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateFrom
AND Forms!FrmFinancialYearAll!sfcDateParams.Form!tbDateTo
GROUP BY TblInvoice.OwnerID, NZ(TotalPaidAmount,0), (OwnerLastName+", ") & OwnerFirstName;

The only remaining question is does it return the correct results?  You are in a better position than I to answer that.

---

Ken Sheridan, Stafford, England

Answer 22

Thanks Ken, Is it possible to have OwnerName descending

Also my preview of my report shows the correct date ranges then when I print my Date boxes they are printed #Name

Thanks for your help...Bob

---

xxx

Answer 23

Thanks Ken, it worked, I just had to change the  Forms!FrmFinYearAll! as I had no subForm

Just need OwnerName to descend thanks....Bob

---

xxx

Answer 24

 Thanks Ken, Also using a different Invoice date changed from InvoiceDate to InvoiceDateInv

Regards Bob

PARAMETERS Forms!FrmFinYearAll!tbDateFrom DateTime, Forms!FrmFinYearAll!tbDateTo DateTime;
SELECT TblInvoice.OwnerID, SUM(TblInvoice.OwnerPercentAmount) AS TotalTotalAmount, NZ(TotalPaidAmount,0) AS TotalPayments, SUM(OwnerPercentAmount)-NZ(TotalPaidAmount,0) AS BalanceDue, (OwnerLastName+", ") & OwnerFirstName AS ClientName
FROM tblOwnerInfo INNER JOIN (TblInvoice LEFT JOIN (SELECT OwnerID,
      SUM(PaidAmount) AS TotalPaidAmount
      FROM tblAccountStatus
      WHERE BillDate BETWEEN  Forms!FrmFinYearAll!tbDateFrom
      AND  Forms!FrmFinYearAll!tbDateTo
      GROUP BY OwnerID)  AS Payments ON TblInvoice.OwnerID = Payments.OwnerID) ON tblOwnerInfo.OwnerID = TblInvoice.OwnerID
WHERE TblInvoice.InvoiceDateInv BETWEEN  Forms!FrmFinYearAll!tbDateFrom
AND  Forms!FrmFinYearAll!tbDateTo
GROUP BY TblInvoice.OwnerID, NZ(TotalPaidAmount,0), (OwnerLastName+", ") & OwnerFirstName;

---

xxx

Answer 25

A report must be ordered by means of its internal sorting and grouping mechanism.  A report will ignore a query's ORDER BY clause.

Without more detail about how the date range is represented in the report I can't comment conclusively on the second issue.  If you are trying to show the parameter date values in the report you can either reference the form's controls in the ControlSource property of an unbound text box control in the report, or pass the values to the report as a string expression via the OpenArgs property of the OpenReport method.  In the latter case you'd have to parse the string expression in the report's Open event procedure of course.  You'll find examples of the former method in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the section on 'retrieving data from the database' includes a form from which a Payments report can be opened in two different layouts.  In each case the selected date range is shown in an unbound control in the page header.  I'm able to send this report to a printer without problem.

---

Ken Sheridan, Stafford, England

Answer 26

Thanks Ken, Sorted in descending on report and it worked

Thanks for the help...Bob

---

xxx

Date field wont print , is it because I am closing my form then opening my report?

Private Sub cmdStatement_Click()
   On Error GoTo Err_cmdPrintClient_Click

    Dim stDocName As String


DoCmd.OpenReport "rptFinanicalYearAll", acViewPreview
 DoCmd.Close acForm, Me.Name

  DoCmd.Close acForm, "frmClientInfomation"
Exit_cmdPrintClient_Click:
    Exit Sub

Err_cmdPrintClient_Click:
    MsgBox Err.Description
    Resume Exit_cmdPrintClient_Click

End Sub

      

Answer 27

Date field wont print , is it because I am closing my form then opening my report?

Exactly.  Instead, close the form in the report's Close event procedure. Pass the form name to the report as the OpenArgs argument:

DoCmd.OpenReport "rptFinanicalYearAll", View:=acViewpreview, OpenArgs:=Me.Name

Then in the report's Close event procedure put:

If Not IsNull(Me.OpenArgs) Then
    DoCmd.Close, acForm, Me.OpenArgs
End If

---

Ken Sheridan, Stafford, England

Answer 28

Thanks Ken, all done!

---

xxx