Is this possible using columns???

[pbrown2]

Some background...
There are areas that are reported on, and in an attempt to format the report as everyone is use to in Excel I have set the report up to have columns.
The detail has 3 fields... [ACT] [BUD] [VAR]
Therefore the report currently looks like

[AREA1] [AREA2] [AREA3] etc...
ACT BUD VAR ACT BUD VAR ACT BUD VAR
5 5 0 4 4 0 10 11 1
4 3 -1 0 1 1 9 7 -2
The problem is that I need to explain the lines.... like line one is fore Direct and line 2 is for Indirect. Therefore I would like to have:

[AREA1] [AREA2] [AREA3]
ACT BUD VAR ACT BUD VAR ACT BUD VAR
Direct 5 5 0 4 4 0 10 11 1
IDirect 4 3 -1 0 1 1 9 7 -2


However, if I put Direct and IDirect in as labels the repeat for each detail...

Direct 5 5 0 Direct 4 4 0 and so on.

Is there away to have them show only at the begining???

Also is there away to have the last column be a "Total Sum" ?????
Direct 5 5 0 4 4 0 10 11 2 19 20 2

Any suggestions are greatly appreciated!!

Thank you for any and all help,

PBrown

 

[dhookom]

There are several different crosstab report samples at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

There is also an FAQ faq701-4524 here that shows how to create a crosstab with multiple values.
There is a KB article on how to keep labels only in the left most column

http://support.microsoft.com/default.aspx?scid=kb;en-us;210044&Product=acc

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pbrown2]

Thanks!
The report is now going as planned. However there is one item that has been "requested".
Since the report is now in the correct "Column & Row" format is there anyway to instruct Access to have the final Column in the row to be a "Totals" Column?

I.E.

[Area1] [Area2] Totals
Act Bud Var Act Bud Var Act Bud Var
Direct 1 2 1 3 2 -1 4 4 0
Semi 5 6 1 0 0 0 5 6 1
Indirect 7 2 -5 4 8 4 11 10 -1


I can say that currently (at least for the next year, that is) that there will not be anymore than 4 areas therefore, it would be safe to, if possible, to code the "Totals" to be in column 6. Also, with the current column width and spacing, I know that 6 columns can fit. (I added enough "dummy" areas to make sure).


Any suggestions??

I looked at the other sites that Duane suggested but we are currently unable to download off the net, therefore, I had to go with the "Non CrossTab" option. If CrossTab will do this, anyone have any suggestions?

Thank you again!!!

Thank you for any and all help,

PBrown

 

[dhookom]

How did you get this to work? Did you use the method suggested in the FAQ?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pbrown2]

I guess I do not fully understand the question?
I was able to get everything BUT the totals column to work by using the KB article.
Now, what I need is for the totals column...

Once again, exuse me if I have misunderstood the question.

Thank you for any and all help,

PBrown

 

[dhookom]

You stated [red]The report is now going as planned. However there is one item that has been "requested".
Since the report is now in the correct "Column & Row" format[/red]. I was just wondering which of the several resources that I pointed out helped you with your issue.

I believe that FAQ reference that I suggested should allow you to create a totals column. Did you use the FAQ and if so, what is the SQL of your crosstab?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pbrown2]

I will have to re-visit the FAQ. I would like to thank you for your understanding.


Thank you for any and all help,

PBrown

 

[pbrown2]

Tried going through the crosstab FAQ, but seem to be missing a step or two. I get many different columns but honestly can not make to much sense of it. Can a crosstab query be used when dealing with only 1 table, because that is all I have.

Guess I will continue to experiment if you say a crosstab can do what I have been requested to do.

Thank you for any and all help,

PBrown

 

[pbrown2]

I guess the crosstab coding is just too complex for me without any assistance.

I have gone through many different FAQ's and even different Access Books but am unable to understand how to get just the query working.

Any assistance would be greatly appreciated.....
Here is some more detail if it may help.

There are different [Areas]
Each [Area] has many different categories, such as Direct, Semi Direct, Indirect
Each category has an amount under [Actual] [Budget] & [Var]
Directs Act = [DACTUAL]
Semi Directs Act = [SDACTUAL]
Indirects Act = [IACTUAL]

Directs Bud = [DBUD]
Semi Directs Bud = [SDBUD]
Indirects Bud = [IBUD]

Directs Var = [DVAR]
Semi Directs Var = [SDVAR]
Indirects Var = [IVAR]

Also, in the form have coding that totals the ACT, BUD and VAR into its own fields
Total Act = [TACT]
Total Bud = [TBUD]
Total Var = [TVAR]


This information is updated via a simple form.

Currently by using the KB artical provided by dhookom I have been able to get the report, without a crosstab and by only using columns, to look like:

[Area1] [Area2]
ACT BUD VAR ACT BUD VAR
Direct 1 0 -1 3 2 -1

Semi Direct 3 4 1 5 7 2

Indirect 5 4 -1 6 0 -6

Total 9 8 -1 14 9 -5

Currently with this year I know that there will be no more than 5 areas being used, therefore I know that 6 columns will fit on a sheet of page.

The original question remains, Is there away to have column 6 be a totals column?
Totals
ACT BUD VAR
Direct 4 2 -2

Semi Direct 8 11 -3

Indirect 11 4 -7

Totals 23 17 -6

Perhaps with the more detail information on the set up on how everything is organized, perhaps it may be easier to show me where to go or provide an example that can be used by a person who has very rarely used a crosstab, and never created one.

Thank you one and all for all your help and understanding, crosstabs are a totally new area for me.

Thank you for any and all help,

PBrown

 

[dhookom]

I have some idea of where you want to go (final display) but no idea where you are starting (your current table/query structure and sample raw data).

My Mar. 9 response had a link to some sample crosstab reports. Did you look at any of these?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[pbrown2]

Here is the current table structure:
(this is serverly downsized since there are a total of 97 fields)

[Area]
[Facility]
[DirectACT]
[DirectBUD]
[DirectVAR]
[SemiDirectACT]
[SemiDirectBUD]
[SemiDirectVAR]
[IndirectACT]
[IndirectBUD]
[IndirectVAR]
[TotalACT]
[TotalBUD]
[TotalVAR]
[ManagerACT]
[ManagerBUD]
[ManagerVAR]

...etc...

The form:
[Area1]
Direct 9 10 1 (All var's are calculated in code)
SemiDirect 11 12 1
Indirect 10 13 3
Total 30 35 5 (All totals are calculated in code)

Manager 0 4 4


TotalSal

Then the user chooses the next area and completes that areas information.

I have visited the first sight you suggested but under your section I only see "download" and security has blocked our ablity to download.

Am I missing something?

Thank you for any and all help,

PBrown

 

[dhookom]

Holy un-normalized, Batman! I am going to need to "think spreadsheet" to get my head around this. I'll try to get back to you later today.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.