Newbie heolp with group running sum or grand total

  • Thread starter Thread starter verci
  • Start date Start date
V

verci

Hi, sorry if this seems stupid,
I'm runnig Access XP, I've created a report with this underlaying query,
where Factura = Invoice, Fecha Inicial = Beginning Date, Fecha Final =
Ending Date, all this where Factura is the main form and FacturaDetalle is a
child subform for the details of the invoice.

SELECT FacturaDetalle.FacturaID, Factura.NumFactura, Factura.FechFactura,
Factura.Nombre, Factura.Retencion, FacturaDetalle.Importe, Factura.Cancelada
FROM Factura INNER JOIN FacturaDetalle ON Factura.FacturaID =
FacturaDetalle.FacturaID
WHERE (((Factura.FechFactura) Between [Fecha Inicial:] And [Fecha Final:]));

Now I've group order my report using the following values in the sorting and
grouping window, Field -> NumFactura, Sort Order->Ascending, Group
header->yes, GroupFooter->No, GroupOn-> Each value, Group Interval->1,
Keeptogether->No.

On the reports design view I've placed the fields on the groups header
section, now my problem is that I can't do a running sum of the filds, so I
can display the grand total of each field and the end of the report, I've
tried adding the groups footer section and putting all the fields grand
total, but so far no success, any help would be greatly appreciated.

Best Regards
 
verci said:
Hi, sorry if this seems stupid,
I'm runnig Access XP, I've created a report with this underlaying query,
where Factura = Invoice, Fecha Inicial = Beginning Date, Fecha Final =
Ending Date, all this where Factura is the main form and FacturaDetalle is a
child subform for the details of the invoice.

SELECT FacturaDetalle.FacturaID, Factura.NumFactura, Factura.FechFactura,
Factura.Nombre, Factura.Retencion, FacturaDetalle.Importe, Factura.Cancelada
FROM Factura INNER JOIN FacturaDetalle ON Factura.FacturaID =
FacturaDetalle.FacturaID
WHERE (((Factura.FechFactura) Between [Fecha Inicial:] And [Fecha Final:]));

Now I've group order my report using the following values in the sorting and
grouping window, Field -> NumFactura, Sort Order->Ascending, Group
header->yes, GroupFooter->No, GroupOn-> Each value, Group Interval->1,
Keeptogether->No.

On the reports design view I've placed the fields on the groups header
section, now my problem is that I can't do a running sum of the filds, so I
can display the grand total of each field and the end of the report, I've
tried adding the groups footer section and putting all the fields grand
total, but so far no success, any help would be greatly appreciated.

Best Regards
Click to expand...

I had only limited success (in Access 2000) displaying a running sum. I
got the right totals via a Running Sum = "Over Group" within
[GroupFooter1], the [FacturaDetalleID] footer, but that appeared after
every detail record.

Putting the same control into [GroupFooter0], the [NumFactura] footer, I
got the wrong totals.

So I defined a Query to calculate the totals within the group and used
its results in a text box in the [NumFactura] footer. (I deleted the
[FacturaDetalleID] footer.)

You didn't specify what field you used for subtotals, so I used
[Importe] and made it a Currency type. The report looks like this:

R_Factura


FacturaID -601653873 Retencion 5
NumFactura 10
FechFactura 11/1/2005
Nombre Vince
FacturaDetalleID Importe
-2119173547 $25.00
-491392482 ($3.00)
-------------
$22.00



FacturaID -1827184910 Retencion 666
NumFactura 42
FechFactura 10/25/2005
Nombre Verci
FacturaDetalleID Importe
1558781067 $3.00
1874272689 $2.00
1885267568 $5.00
-------------
$10.00

.... based on data in the following Tables:

[Factura]
FacturaID Num FechFactura Nombre Retencion Cancelada
Factura
----------- ------- ----------- ------ --------- ---------
-1827184910 42 10/25/2005 Verci 666 Yes
-601653873 10 11/1/2005 Vince 5 No

[FacturaDetalle]
FacturaDetalleID FacturaID Importe
---------------- ----------- --------
1885267568 -1827184910 $5.00
1874272689 -1827184910 $2.00
1558781067 -1827184910 $3.00
-491392482 -601653873 ($3.00)
-2119173547 -601653873 $25.00


The following Query gave me the record details:

[Q_Details] SQL:
SELECT Factura.FacturaID, Factura.NumFactura,
Factura.FechFactura, Factura.Nombre,
Factura.Retencion, FacturaDetalle.Importe,
FacturaDetalle.FacturaDetalleID
FROM Factura INNER JOIN FacturaDetalle
ON Factura.FacturaID = FacturaDetalle.FacturaID
WHERE (((Factura.FechFactura) Between
[Forms]![F_Factura]![Fecha Inicial]
And [Forms]![F_Factura]![Fecha Final]));

[Q_Details] Datasheet View:
FacturaID Num FechFactura Nombre Retencion Importe Factura
Factura DetalleID
----------- ---- ----------- ------ --------- ------- ------------
-601653873 10 11/1/2005 Vince 5 $25.00 -2119173547
-601653873 10 11/1/2005 Vince 5 ($3.00) -491392482
-1827184910 42 10/25/2005 Verci 666 $3.00 1558781067
-1827184910 42 10/25/2005 Verci 666 $5.00 1885267568
-1827184910 42 10/25/2005 Verci 666 $2.00 1874272689

.... and this one produced the sums:

[Q_Sums] SQL:
SELECT DISTINCTROW Q_Details.NumFactura,
Sum(Q_Details.Importe) AS SumOfImporte
FROM Q_Details
GROUP BY Q_Details.NumFactura
ORDER BY Q_Details.NumFactura;

[Q_Sums] Datasheet View:
NumFactura SumOfImporte
---------- ------------
10 $22.00
42 $10.00

.... and this one combined them into one record that could be used as the
basis for the Report:

[Q_DetailsWithSums] SQL:
SELECT Q_Details.*, Q_Sums.SumOfImporte
FROM Q_Details INNER JOIN Q_Sums
ON Q_Details.NumFactura = Q_Sums.NumFactura;


The Form and Subform are linked based on their shared [FacturaID]
fields. The record source for the Form is [Q_Details], and for the
Subform it is the following SQL:

SELECT [FacturaDetalle].[FacturaDetalleID],
[FacturaDetalle].[FacturaID],
[FacturaDetalle].[Importe] FROM FacturaDetalle;


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top