Is it possible to have a subtotal in a report every 10th line

[Axel]

Hi
I need to generate a report where I would need to have every 10th line a
subtotal, never the less the amount of lines if more than 10 lines.

Does anyone have a simple solution for this?
Thanks

 

[Al Campagna]

Axel,
It's do-able, but not easily... and I'm not sure why you need to
do that, but...

(I'll assume the running total will be in the Detail section)
Add an unbound control to the report named LineCounter, and give it
ControlSource of...
= 1.
Make that control Running Sum = Yes
That will cause each line of the Detail to be numbered 1, 2, 3, 4,
etc...

Add your data "totals" control (Ex. name [DataRunSum]) to the Detail
section, make it invisible, and make it a
running sum also.

Using the OnFormat event of your data's Detail section, start a counter
whenever the Detail section starts=, and whenever LineCounter = 10, make the
DataRunSum control visible.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Counter Mod 10 = 0 Then
Me.DataRunSum.Visible = True
Else
Me.DataRunSum.Visible = False
End If
End Sub
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Axel]

Thanks. I will try it out this evening.
By the way, the request is based on a procedure which defines the 10 lines
subtotal. And procedures... don't make all the time life easier...

Axel,
It's do-able, but not easily... and I'm not sure why you need to
do that, but...

(I'll assume the running total will be in the Detail section)
Add an unbound control to the report named LineCounter, and give it
ControlSource of...
= 1.
Make that control Running Sum = Yes
That will cause each line of the Detail to be numbered 1, 2, 3, 4,
etc...

Add your data "totals" control (Ex. name [DataRunSum]) to the Detail
section, make it invisible, and make it a
running sum also.

Using the OnFormat event of your data's Detail section, start a counter
whenever the Detail section starts=, and whenever LineCounter = 10, make the
DataRunSum control visible.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Counter Mod 10 = 0 Then
Me.DataRunSum.Visible = True
Else
Me.DataRunSum.Visible = False
End If
End Sub

Hi
I need to generate a report where I would need to have every 10th line a
subtotal, never the less the amount of lines if more than 10 lines.

Does anyone have a simple solution for this?
Thanks

 

[Axel via AccessMonster.com]

This works great.

Is there a possibility to remove the space, where the DataRunSum is not
visible? This would give it the look of 10 line "spreadsheet" with its
subtotal.

Thanks
Axel

Axel,
It's do-able, but not easily... and I'm not sure why you need to
do that, but...

(I'll assume the running total will be in the Detail section)
Add an unbound control to the report named LineCounter, and give it
ControlSource of...
= 1.
Make that control Running Sum = Yes
That will cause each line of the Detail to be numbered 1, 2, 3, 4,
etc...

Add your data "totals" control (Ex. name [DataRunSum]) to the Detail
section, make it invisible, and make it a
running sum also.

Using the OnFormat event of your data's Detail section, start a counter
whenever the Detail section starts=, and whenever LineCounter = 10, make the
DataRunSum control visible.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Counter Mod 10 = 0 Then
Me.DataRunSum.Visible = True
Else
Me.DataRunSum.Visible = False
End If
End Sub

Hi
I need to generate a report where I would need to have every 10th line a
subtotal, never the less the amount of lines if more than 10 lines.

Does anyone have a simple solution for this?
Thanks

 

[Al Campagna]

Axel,Not sure what your trying to do. Please try to give a simple example...
(Here's what I have... this is what I want)
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

This works great.

Is there a possibility to remove the space, where the DataRunSum is not
visible? This would give it the look of 10 line "spreadsheet" with its
subtotal.

Thanks
Axel

Axel,
It's do-able, but not easily... and I'm not sure why you need to
do that, but...

(I'll assume the running total will be in the Detail section)
Add an unbound control to the report named LineCounter, and give it
ControlSource of...
= 1.
Make that control Running Sum = Yes
That will cause each line of the Detail to be numbered 1, 2, 3, 4,
etc...

Add your data "totals" control (Ex. name [DataRunSum]) to the Detail
section, make it invisible, and make it a
running sum also.

Using the OnFormat event of your data's Detail section, start a
counter
whenever the Detail section starts=, and whenever LineCounter = 10, make
the
DataRunSum control visible.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Counter Mod 10 = 0 Then
Me.DataRunSum.Visible = True
Else
Me.DataRunSum.Visible = False
End If
End Sub

Hi
I need to generate a report where I would need to have every 10th line a
subtotal, never the less the amount of lines if more than 10 lines.

Does anyone have a simple solution for this?
Thanks

 

[Axel via AccessMonster.com]

I was to fast with my question and I could find a easy solution by activating
for this field the "can grow" and "can shrink" option.

But however a next question came up. Is it possible to sum only the 'grouped'
values?
Here's what I have (based on 2 lines subtotal)

Label Value
aa 10
bb 12
subtotal:22
cc 15
dd 20
subtotal:57
Total:57

and this is what I want

Label Value
aa 10
bb 12
subtotal:22
cc 15
dd 20
subtotal:35
Total:57

Axel,Not sure what your trying to do. Please try to give a simple example...
(Here's what I have... this is what I want)

This works great.

[quoted text clipped - 40 lines]

 

[Al Campagna]

Axel,
This summing every tenth record is not really acting upon a "group" ..
It just calculates an "artificial" grouping.
A real group arises from the values within the dataset...
(StoreNo, State, Dept, etc..)

Since you requested the artificial breaking, I must assume that there is
no "logical " way... with your data... to group by tens.

Doing what you requested would mean adding a field to your recordset
named ex. [GrpNo], and setting it's value to 1 for the first 10 records...
2
for the next 10, etc etc. Then use that value to group, in a "legitimate"
report Group.
Then you can add a running sum control and base it's running sum on
"Over Group."

But... you're really departing from the way reports are supposed to
work.
And they work the way they do for very logical reasons. It's how we
make
data into meaningful.I don't understand what you mean by that...

Reports should break, and calculate, on thier own legitimate data
values.
I still can't see any logical purpose for this artificial grouping.
But that's your call...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

I was to fast with my question and I could find a easy solution by
activating
for this field the "can grow" and "can shrink" option.

But however a next question came up. Is it possible to sum only the
'grouped'
values?
Here's what I have (based on 2 lines subtotal)

Label Value
aa 10
bb 12
subtotal:22
cc 15
dd 20
subtotal:57
Total:57

and this is what I want

Label Value
aa 10
bb 12
subtotal:22
cc 15
dd 20
subtotal:35
Total:57

Axel,

Is there a possibility to remove the space

Not sure what your trying to do. Please try to give a simple
example...
(Here's what I have... this is what I want)

This works great.

[quoted text clipped - 40 lines]

Does anyone have a simple solution for this?
Thanks

 

[Axel via AccessMonster.com]

Thanks for your help. I have now a idea how to proceed.

I know that request sounds strange but it is based on a official layout I
have to apply to and I have the same feeling about it as you have.

Thanks for your support.
Axel

Axel,
This summing every tenth record is not really acting upon a "group" ..
It just calculates an "artificial" grouping.
A real group arises from the values within the dataset...
(StoreNo, State, Dept, etc..)

Since you requested the artificial breaking, I must assume that there is
no "logical " way... with your data... to group by tens.

Doing what you requested would mean adding a field to your recordset
named ex. [GrpNo], and setting it's value to 1 for the first 10 records...
2
for the next 10, etc etc. Then use that value to group, in a "legitimate"
report Group.
Then you can add a running sum control and base it's running sum on
"Over Group."

But... you're really departing from the way reports are supposed to
work.
And they work the way they do for very logical reasons. It's how we
make
data into meaningful.I don't understand what you mean by that...

Reports should break, and calculate, on thier own legitimate data
values.
I still can't see any logical purpose for this artificial grouping.
But that's your call...

I was to fast with my question and I could find a easy solution by
activating

[quoted text clipped - 35 lines]

 

[Duane Hookom]

I generally create groups in the record source. For instance if you want to
group 10 orders together in the Northwind sample database, you could use SQL
like:

SELECT (Select Count(*) FROM Orders O WHERE O.OrderID<Orders.OrderID)\10 AS
The10Group, Orders.*, Orders.OrderID
FROM Orders
ORDER BY Orders.OrderID;

Then, in your report, you can group by the field [The10Group].

 

[Al Campagna]

Duane,
Very elegant solution!
Al Campagna

I generally create groups in the record source. For instance if you want to
group 10 orders together in the Northwind sample database, you could use
SQL
like:

SELECT (Select Count(*) FROM Orders O WHERE O.OrderID<Orders.OrderID)\10
AS
The10Group, Orders.*, Orders.OrderID
FROM Orders
ORDER BY Orders.OrderID;

Then, in your report, you can group by the field [The10Group].

--
Duane Hookom
Microsoft Access MVP

Hi
I need to generate a report where I would need to have every 10th line a
subtotal, never the less the amount of lines if more than 10 lines.

Does anyone have a simple solution for this?
Thanks

 

[Axel via AccessMonster.com]

Duane,

Where do I have to enter that SQL command?

Duane,
Very elegant solution!
Al Campagna

I generally create groups in the record source. For instance if you want to
group 10 orders together in the Northwind sample database, you could use

[quoted text clipped - 15 lines]

 

[Duane Hookom]

You would need to modify the SQL to fit your needs and use it as the Record
Source of your report.

If you can't figure this out, reply with the record source and primary
sorting and grouping expression for your report.

--
Duane Hookom
Microsoft Access MVP

Duane,

Where do I have to enter that SQL command?

Duane,
Very elegant solution!
Al Campagna

I generally create groups in the record source. For instance if you want to
group 10 orders together in the Northwind sample database, you could use

[quoted text clipped - 15 lines]

Does anyone have a simple solution for this?
Thanks

 

[Marshall Barton]

But however a next question came up. Is it possible to sum only the 'grouped'
values?
Here's what I have (based on 2 lines subtotal)

Label Value
aa 10
bb 12
subtotal:22
cc 15
dd 20
subtotal:57
Total:57

and this is what I want

Label Value
aa 10
bb 12
subtotal:22
cc 15
dd 20
subtotal:35
Total:57

This is essentially the same problem as calculating a
subtotal for each page and I think will yield to the same
approach,

Hide the running sum text box and add another text box
(named txtSubTotal) to display the pseudo group's subtotal.
Then add a little more code to the detail section's Format
event:

Static lngPrevTotal As Long
If Counter Mod 10 = 0 Then
Me.txtSubTotal.Visible = True
Me.txtSubTotal = Me.DataRunSum - lngPrevTotal
lngPrevTotal = Me.DataRunSum
Else
. . .