Grouping in cross tab queries 1

[sync123]

Hi,

I have a table that I have created from a query with the following
fields

sort group_name Area month_actual summary
1 M of H Seniors HC 5 funds
2 OHIP Branson 332 other suppl
3 Emp benefits Branson 66 salaries&Wages
4 M of H Seniors HC 33 funds
5 OHIP Seniors HC 98 other suppl
6 Emp benefits Branson 23 salaries&wages


The report that I want to create from this table has group_names as row headings and area as the column headings.The value shown in the report is month actual.

I have created a cross tab query and have sorted my information based on the sort field.

Now the issue that I am having is that the report must also be grouped by the summary field. I do a group by on summary in my cross tab query.

But when it comes to generating the report using a wizard on the cross tab query,the wizard does not show summary in the list box of fields I can group on. All I get in the list box is group_name,dif area names

Can someone please help me out?There must be a way to do this.This is what my query looks like rite now

TRANSFORM Sum(MONTH_ACTUAL) AS [The Value]
SELECT GROUP_NAME, Sum(MONTH_ACTUAL) AS [Total Of MONTH_ACTUAL]
FROM my_tbl
GROUP BY GROUP_NAME, SUMMARY, SORT
ORDER BY SUMMARY DESC , SORT
PIVOT AREA;

Thanks alot..
sync123

 

[dhookom]

You don't have Summary in your select statement:
TRANSFORM Sum(MONTH_ACTUAL) AS [The Value]
SELECT GROUP_NAME, Summary, Sum(MONTH_ACTUAL) AS [Total Of MONTH_ACTUAL]
FROM my_tbl
GROUP BY GROUP_NAME, SUMMARY, SORT
ORDER BY SUMMARY DESC , SORT
PIVOT AREA;


Duane
MS Access MVP

 

[sync123]

thanks dhookom!I have one more question to ask.

In my report I want a col that gives me the total of 2 group_names, shc and branson.

ie
area: SHC Branson Total Gen total
SHC and
branson

group_name
M of H 3 3 6 1 7
OHIP 7 8 15 4 19
...


I changed my query to look like this but its giving me all 0's in the firstTotal col.
TRANSFORM Sum(MONTH_ACTUAL) AS [The Value]
SELECT GROUP_NAME, Summary, Sum(MONTH_ACTUAL) AS [Total Of MONTH_ACTUAL],IIf(group_name='SHC' Or group_name='Branson', (MONTH_ACTUAL/1000),0) AS firstTotal
FROM my_tbl
GROUP BY GROUP_NAME, SUMMARY, SORT
ORDER BY SUMMARY DESC , SORT
PIVOT AREA;


Another way that I was previously doing was that in my report I was suming up the textboxes values of shc and branson but in some instances there was a group name that was in shc and not in branson so my total textbox was giving me a blank since i was trying to add a number to a null value.Is there a way around this?


Thanks alot.
sync123

 

[dhookom]

The easiest method in the report is to set the control source to:
=Nz([SHC],0) + Nz([Branson],0)
In some cases you may have to use:
=Val(Nz([SHC],0)) + Val(Nz([Branson],0))


Duane
MS Access MVP

 

[JonFer]

To get the extra column in your crosstab query you would need to have duplicate rows for those 2 Areas in your underlying table. Create a query with the underlying table/query and a table with 2 rows in it. Set the criteria to be:

Row=1 OR
Row=2 AND Area In ("SHC", "BRANSON&quot

Define Area as -> IIf(Row=1,[tablename].[Area],"SHC and BRANSON&quot

Use this query to feed your crosstab. This solution can be expanded for additional groupings (modify the row table to make it easier).

Duane's solution is much simpler if the report is all you need.

 

[sync123]

thanks jonfer! I just needed the report solution.But this is good to know as well.Its sure to come in handy.

Also, I had one more general question.

I am using the same report template for two reports. These reports are identical except that they just have a different record source.
In the on open event of my report depending on some criteria, I change the record source of my report.

This works fine.

The problem I am having is this. In my report footer I need to get a row containing info about overhead allocation. I get this info from a qry.

The control source of a textbox that appears in the report footer looks something like this

=DSum("[SHC]","qry_crosstab","[group_name]='Total Overhead Allocation' &quot

For report1 the qry used is "qry_crosstab"
For report2 the qry used is "qry_crosstab2"

I want to be able to change the control source of textboxes that appear in my report footer depending on which report I have opened. Is it possible to set the control source of a control in a report using vba??

I have been wondering this for a long time. Because in some other cases I have reports which are almost identical except for a few extra rows that appear in the "report footer".

Currently I make 2 different reports and the only difference is the extra footer textboxes. It would be good to know if there is a way to program this so depending on some criteria these rows could be hidden and their control source of these textboxes could be changed.

Thanks.
sync123

 

[dhookom]

I don't think you need DSum(). Try:
=Sum( Abs([GroupName]="Total Overhead Allocation&quot * [SHC])

This should run much faster and not require changing the control source.

Duane
MS Access MVP

 

[sync123]

Thanks Duane!! That works great!
I really appreciate all your help.
Here's a star for you.
Thanks again.
sync123