Report problem

[lavanya]

I am using ACCESS 2000. Sorry this is a long post.
I have a table TAB1 with the following kind of info
which is displayed as a REPORT.

Fld0 Fld1 fld2 fld3 fld4 fld5
---------------------------------------------------
AA 1 31 12 24 1/1/2004
AA 1 32 21 88 2/1/2004
AA 3 33 15 76 1/1/2004
MM 6 35 12 34 1/1/2004
XX 5 36 16 54 1/1/2004
XX 5 37 20 94 4/1/2004

I need to create a summary line for each grouping of
fld0,
Containing values for, sum of all fld3 , sum of fld4 in
that group, and wherever there are 2 non unique fld1
within the group of fld0, I have to select the row which
has the later date.

For the above info the summary line will occur after each
group of fld0 in the same REPORT as abive should look
like this.

Fld 0 sum of fld3 sum of fld4
-------------------------------------
AA 36(21+15) 164(88+76)
MM 12 34
XX 20 94

Can I achieve this only with VBA or will queries alone do
this work ?
How will I display this in a report that has its record
source set to the table TAB1.

I am relatively new to report writing and would
appreciate any help.

 

[Terry Reardon]

To tackle a problem like this, I would declare integer
variables in the Report module. I would also place
UNBOUND controls on the report detail and calcuate the
date using a function. Then in the Detail format event I
would increment those integers based on the resultset you
have provided.

Example:
Option Compare Database
Option Explicit

Dim intFld0 as integer, intFld3 as integer
Private Sub Report_Open(Cancel As Integer)
Call Summarize()
End Sub

Private Function Summarize()
Dim rs as Recordset, db as Database
Set db = Currentdb
Set rs = db.OpenRecordset("MyReportRecordSource",
dbOpenDynaSet)

Do While Not rs.EOF
rs.MoveFirst
' INCREMENT YOUR VARIABLES BASED YOUR CONDITIONS
intFld0 = intFld0 CInt(rs.Fields("FLD0").value) + 1
Loop

end Function


Private Sub Detail_Format(Cancel As Integer, FormatCount
As Integer)
txtField0 = intFld0
'etc....
End Sub


I hope this helps and gives you ideas....

Terry Reardon
http://www.terryreardon.ca

 

[Terry Reardon]

Opps!
In my haste to respond to your post, I negelected to add
the rs.MoveNext in the Do While Loop...

Sorry if you didn't catch it and went into "Never Never
Land".....

Terry Reardon
mailto:[email protected]
http://www.terryreardon.ca

 

[Treebeard]

Create the following Query and call it qry1Tab1

SELECT DISTINCTROW [Fld0], [Fld1], Last([Fld3]) AS [Fld3_Last], Last([Fld4])
AS [Fld4_Last]
FROM TAB1
GROUP BY [Fld0], [Fld1];


This will give you these values which are the ones you want to add:


Fld0 Fld1 Fld3_Last Fld4_Last

AA 1 21 88

AA 3 15 76

MM 6 12 34

XX 5 20 94


Then create another query and call it qry2Tab1:

SELECT [Fld0], Sum([Fld3_Last]) AS [Fld3_Sum], Sum([Fld4_Last]) AS
[Fld4_Sum]
FROM qry1Tab1
GROUP BY [Fld0];


This will give you the values you want.

Finally create a report and call it rptTab1SubReport and set its data source
to be qry2Tab1. Make its layout Tabular and Orientation landscape. Remove
its report header and footer.

Finally open your current report which has Tab1 as its data source. In the
Report Footer put a subreport object. Click on "Use an existing report or
form" and select rptTab1SubReport , hit the next button, click on "define my
own" when it asks you to define a child field. Make sure the fields are
blank (you do not want any field links between the form an subform). Click
next and finish.

Good Luck,

Jack

 

[Guest]

thanku for your efforts in responding to this long
question. i will try what you have suggested.

-----Original Message-----
Create the following Query and call it qry1Tab1

SELECT DISTINCTROW [Fld0], [Fld1], Last([Fld3]) AS [Fld3_Last], Last([Fld4])
AS [Fld4_Last]
FROM TAB1
GROUP BY [Fld0], [Fld1];


This will give you these values which are the ones you want to add:


Fld0 Fld1 Fld3_Last Fld4_Last

AA 1 21 88

AA 3 15 76

MM 6 12 34

XX 5 20 94


Then create another query and call it qry2Tab1:

SELECT [Fld0], Sum([Fld3_Last]) AS [Fld3_Sum], Sum ([Fld4_Last]) AS
[Fld4_Sum]
FROM qry1Tab1
GROUP BY [Fld0];


This will give you the values you want.

Finally create a report and call it rptTab1SubReport and set its data source
to be qry2Tab1. Make its layout Tabular and Orientation landscape. Remove
its report header and footer.

Finally open your current report which has Tab1 as its data source. In the
Report Footer put a subreport object. Click on "Use an existing report or
form" and select rptTab1SubReport , hit the next button, click on "define my
own" when it asks you to define a child field. Make sure the fields are
blank (you do not want any field links between the form an subform). Click
next and finish.

Good Luck,

Jack

I am using ACCESS 2000. Sorry this is a long post.
I have a table TAB1 with the following kind of info
which is displayed as a REPORT.

Fld0 Fld1 fld2 fld3 fld4 fld5
---------------------------------------------------
AA 1 31 12 24 1/1/2004
AA 1 32 21 88 2/1/2004
AA 3 33 15 76 1/1/2004
MM 6 35 12 34 1/1/2004
XX 5 36 16 54 1/1/2004
XX 5 37 20 94 4/1/2004

I need to create a summary line for each grouping of
fld0,
Containing values for, sum of all fld3 , sum of fld4 in
that group, and wherever there are 2 non unique fld1
within the group of fld0, I have to select the row which
has the later date.

For the above info the summary line will occur after each
group of fld0 in the same REPORT as abive should look
like this.

Fld 0 sum of fld3 sum of fld4
-------------------------------------
AA 36(21+15) 164(88+76)
MM 12 34
XX 20 94

Can I achieve this only with VBA or will queries alone do
this work ?
How will I display this in a report that has its record
source set to the table TAB1.

I am relatively new to report writing and would
appreciate any help.

.

 

[Guest]

thanku both, for your efforts in responding to this long
question. i will try the mothods that you have suggested.