Query beginning balance prior to start date and details between the start date and end date.

[bonskyn]

Hi. I want a query to give me the details of invoice and payments between a start and end date. Also I want a beginning balance prior to the start date. This what I've done so far. I'm no Access expert.



SELECT FacturasAlCobroT.ConsiderarParaReporte, FacturasAlCobroT.NumFactura, FacturasAlCobroT.Nombre, FacturasAlCobroT.Cuenta, FacturasAlCobroT.Fondo, FacturasAlCobroT.Organizacion, FacturasAlCobroT.Programa, FacturasAlCobroT.Asignacion, FacturasAlCobroT.AnoPresupuestario, FacturasAlCobroT.AportacionFederal, [BalanceInicial] AS Expr1, FacturasAlCobroT.CantidadFactura, PagosT.CantidadPago, [BalanceFinal] AS Expr2, [FechaDesde] AS Expr3, [FechaHasta] AS Expr4
FROM FacturasAlCobroT INNER JOIN PagosT ON FacturasAlCobroT.NumFactura = PagosT.NumFactura

BalanceInicial will be the beginning balance, which will be (sum [CantidadFactura] - sum [CantidadPago]), all the transactions that are prior to the start date. [FechaDesde] is the start date and [FechaHasta] is the end date, those will be input in a Form.
[BalanceFinal] will be the beginning balance [BalanceInicial] + [CantidadFactura] - Sum[CantidadPago], for the Sum of CantidadPago, will be all the transactions that took place between the start date and end date.
Help please.
Thanks.

[pbaldy]

I would probably get the beginning balance with a DSum() directly on the form/report rather than in the query.

[bonskyn]

Hi pbaldy. It ain't working. I tried it on a query, but it adds all the payments that I have recorded, giving me the same beginning amount for each invoice.
I'm only using two tables, the first one for the invoice and the second one for the payments. They are related by the Invoice Number, One-to-Many.
I need for each invoice to Sum all the payments received and subtract it from the invoice amount to get the beginning balance to a specified date.

[pbaldy]

What is "it" exactly? I can't fix what I can't see.

[bonskyn]

The DSum()

[pbaldy]

I don't see a DSum(). Maybe this will help since you don't seem to want to show what you're trying.

DLookup Usage Samples

[bonskyn]

I'm sorry if I haven't expressed myself correctly. I want a report, more like a statement, I want it to show all the invoices registered in the database, with a beginning balance, the invoice that have been created, payments received, and ending balance, depending on the start date and end date. I also don't want it to show the invoices that have been paid complete (balance zero). For example:
Table1 has InvoiceID, InvoiceDate, InvoiceAmount.
Invoice A, 8/01/2013, $2,000
Invoice B, 10/01/2013, $1,000
Invoice C, 7/15/2014, $3,000
Invoice D, 9/15/2014, $1,000

Table2 has InvoiceID(JOIN TO TABLE 1), PaymentDate, Payment Amount
Invoice A, 10/15/2013, $500
Invoice A, 5/01/2014, $500
Invoice B, 12/10/2013, $1,000
Invoice C, 9/30/2014, $2,000
Invoice A, 10/31/2014, $500

If my Start Date is 7/1/2014 and End Date 6/30/2015 the statement should look like this.
Invoice ID, Beginning Balance, Invoice Amount, Paid Amount, Ending Balance
Invoice A, .......$1,000, ....................................$500, ..........$500
Invoice C, ................................$3,000, ........ $2,000, ........ $1,000
Invoice D, ................................$1,000, ..............................$1,000

Invoice B will not show because by the start date is has been paid off.
I hope this help.
Thank you!