Grouping a report

I am trying to group a report like so:
Company: ABC
Trip # Leg Amount
1 1 100
1 2 200
Sub Total 300
Trip # Leg Amount
2 1 100
2 2 200
Sub Total 300
Company: DEF
Trip # Leg Amount
3 1 100
3 2 200
Sub Total 300
Trip # Leg Amount
4 1 100
4 2 200
Sub Total 300
I get get the detail info in the report just fine with a grid. But
I'm running into problems when trying to add the company into the
grid. Currently I have a group created to get the sub total for each
trip and that works fine. But when I create the group to add the
company into the grid, it keeps putting the header columns (Trip #,
Leg, Amount, etc) above the company name. I have tried everything I
can think of to get the company name above it but can't get it to work.if you right click on the group row's headers(the grey part on the far
left when you have the table focused) you can insert a new group.
have a group with the company as the expression. then another with
the trip as the expression.
* company
* trip
** DETAILS|||I got it to work doing that and putting the column header row inside
my group row (instead of in a header row). But now I have another
question - not sure if this is possible, but since I am in a grid, my
Company name ends up in the same column as my trip number. The
company name is obviously going to be bigger than the trip number, but
if I stretch out the column to handle that, then the leg box looks
much larger than it needs to be (I'll try to show it here, but it'll
be tough):
Is there some way I can change the spacing on the company column
without affecting the leg column?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Company: xx ABCDEFGHIJKLMNOPQ xx x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x Trip # xx Leg xx Amount x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 1 xx
100 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x 1 xx 2 xx
200 x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
x xx Sub Total xx 300
x
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Group by query

I have this table ....

AID NAME DATE AMOUNT TYPE
1001 ABC 1/1/2006 120 AC
1001 ABC 1/1/2006 23 AC

1001 BC 1/1/2006 12 AC
1001 DC 1/1/2006 22 TR

1002 ZX 1/1/2006 21 DR

1003 ABC 1/1/2006 23 AC
1003 VF 1/1/2006 44 AC

Now I want a query which will give a result set of - between a specific date
i.e between 1/1/2006- 1/2/2006

AID NAME AC_AMOUNT TR_AMOUNT DR_AMOUNT
1001 ABC 143 0 0
1001 BC 12 0 0
1001 DC 0 22 0
1002 ZX 0 0 21

--
1003 ABC 23 0 0

One row for each name,that is group up by will be on AID and NAME.

Any help will be greatly appreciated...create columns in the query using the CASE operator on the "Type" column.
use group by to get the desired result..
Will not provide any ready made query since its for you to build 1.
Hope this helps.|||create columns in the query using the CASE operator on the "Type" column.
use group by to get the desired result..
Will not provide any ready made query since its for you to build 1.
Hope this helps.
Sorry ,I think that will not work at all.I mean if you use case in that case you have to use group by on AID,NAME,Type.
But That will give you multiple rows, which I don't want...
I think its not so easy man... ;)|||I think its not so easy man...
yes it is, it is very easy
select AID
, NAME
, sum(case when TYPE='AC'
then AMOUNT else 0 end) as AC_AMOUNT
, sum(case when TYPE='TR'
then AMOUNT else 0 end) as TR_AMOUNT
, sum(case when TYPE='DR'
then AMOUNT else 0 end) as DR_AMOUNT
from daTable
where DATE between '1/1/2006' and '1/2/2006'
group
by AID
, NAME|||Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
Thanks Rudy for correcting me.

Hope now this helps you!!!|||yes it is, it is very easy
select AID
, NAME
, sum(case when TYPE='AC'
then AMOUNT else 0 end) as AC_AMOUNT
, sum(case when TYPE='TR'
then AMOUNT else 0 end) as TR_AMOUNT
, sum(case when TYPE='DR'
then AMOUNT else 0 end) as DR_AMOUNT
from daTable
where DATE between '1/1/2006' and '1/2/2006'
group
by AID
, NAME
:rolleyes: Oh my God !! Yes thats it ...I haven't thought that ...:rolleyes:
If you put the sum within the case, you have to use Type in Group by...hmmm thats it
Superb !!
You have put the case within Sum(), WOW!!
Rudy you are great !!|||Look man our dear friend did the exact thing. I just did not think of SUM that you would need to use.
Thanks Rudy for correcting me.

Hope now this helps you!!!

Anyways, Thanks Wash for your help.:)