Report Sub Totals

I've been banging my head for a view days on this and I'm ready for a little
humility. :)
Here is the simplified recordset:
Field1 Field2 Field3 Field4
AAAAA 11111 bat1 3
AAAAA 11111 bat2 4
AAAAA 22222 bat1 0
AAAAA 22222 bat2 2
BBBBB 33333 bat1 1
BBBBB 33333 bat2 3
BBBBB 44444 bat1 1
BBBBB 44444 bat2 3
And the report is supposed to look like this:
AAAAA 11111 bat1 3
bat2 4
22222 bat1 0
bat2 2
AAAAA Subtotal: bat1 3
bat2 6
BBBBB 33333 bat1 1
bat2 3
44444 bat1 1
bat2 3
BBBBB Subtotal: bat1 2
bat2 6
Totals: bat1 5
bat2 12
For the life of me I can't figure out how to make the subtotal include both
types of Field3. I'm sure it's possible...I just don't know where to look
for how. So I'm turning to the collective wisdom of the Internet.
Regards,
David GardnerWhat are you lookin for , is it the sum of al field 4 in relation to
the type in field 3.|||You are correct. The only thing I would add is that it needs to be
sub-totaled by Field1. So in summary:
I need the sum of Field4 in relation to Field3 sub-totaled/grouped by
Field1. Does that make sense?
Dave
"Ivan" wrote:
> What are you lookin for , is it the sum of al field 4 in relation to
> the type in field 3.
>|||Try a UNION query along the lines of:
SELECT Field1, Field2, Field3, Field4
FROM Table1
UNION
SELECT Field1, 'SubTotal' AS Field2, Field3, Sum(Field4) AS Field4
FROM Table1
GROUP BY Field1, 'SubTotal' AS Field2, Field3
ORDER BY 1, 2, 3
This should give you the dataset needed for the report.
GeoSynch
"David Gardner" <David.Gardner-Logan@.REMOVECAPSrrd.com> wrote in message
news:A3F5D991-6C7E-479A-A4D0-A3C0B605B9CF@.microsoft.com...
> I've been banging my head for a view days on this and I'm ready for a little
> humility. :)
> Here is the simplified recordset:
> Field1 Field2 Field3 Field4
> AAAAA 11111 bat1 3
> AAAAA 11111 bat2 4
> AAAAA 22222 bat1 0
> AAAAA 22222 bat2 2
> BBBBB 33333 bat1 1
> BBBBB 33333 bat2 3
> BBBBB 44444 bat1 1
> BBBBB 44444 bat2 3
> And the report is supposed to look like this:
> AAAAA 11111 bat1 3
> bat2 4
> 22222 bat1 0
> bat2 2
> AAAAA Subtotal: bat1 3
> bat2 6
> BBBBB 33333 bat1 1
> bat2 3
> 44444 bat1 1
> bat2 3
> BBBBB Subtotal: bat1 2
> bat2 6
> Totals: bat1 5
> bat2 12
>
> For the life of me I can't figure out how to make the subtotal include both
> types of Field3. I'm sure it's possible...I just don't know where to look
> for how. So I'm turning to the collective wisdom of the Internet.
> Regards,
> David Gardner
>|||"GeoSynch" wrote:
> Try a UNION query along the lines of:
> SELECT Field1, Field2, Field3, Field4
> FROM Table1
> UNION
> SELECT Field1, 'SubTotal' AS Field2, Field3, Sum(Field4) AS Field4
> FROM Table1
> GROUP BY Field1, 'SubTotal' AS Field2, Field3
> ORDER BY 1, 2, 3
> This should give you the dataset needed for the report.
>
> GeoSynch
>
So I can assume by your response that you don't believe I can build the
report based on what the dataset looks like now? That makes me feel a little
better in that I'm not that dumb. :) Still it would be nice if there were a
way to build a report like that.
Regards,
David Gardner|||David Gardner wrote:
> "GeoSynch" wrote:
>> Try a UNION query along the lines of:
>> SELECT Field1, Field2, Field3, Field4
>> FROM Table1
>> UNION
>> SELECT Field1, 'SubTotal' AS Field2, Field3, Sum(Field4) AS Field4
>> FROM Table1
>> GROUP BY Field1, 'SubTotal' AS Field2, Field3
>> ORDER BY 1, 2, 3
>> This should give you the dataset needed for the report.
> So I can assume by your response that you don't believe I can build the
> report based on what the dataset looks like now? That makes me feel a little
> better in that I'm not that dumb. :) Still it would be nice if there were a
> way to build a report like that.
An alternate approach "based on what the dataset looks like now" would be
to encapsulate a LIST table object and a LIST subtotal object within a
parent LIST object. This may be more elegant but UNION is easier and
results are all that matter, unless you've got lot's of time on your hands.
GeoSynch|||> An alternate approach "based on what the dataset looks like now" would be
> to encapsulate a LIST table object and a LIST subtotal object within a
> parent LIST object. This may be more elegant but UNION is easier and
> results are all that matter, unless you've got lot's of time on your hands.
>
> GeoSynch
Your parent LIST/subtotal LIST option sounded interesting and I do wish I
had a little more time to explore. However, I went with a modification of
what the recordset looked like. That did, in fact, turn out to be the more
efficient way to handle the formatting of the report. I appreciate
everyone's input!
Regards,
David Gardner