Solved

crosstab query report that needs to be grouped and totaled by row and column

Posted on 2006-07-10
16
376 Views
Last Modified: 2008-01-09
the textboxes are unbound and here is what it should look like:

Team                      January               february               March ...... .................                     Year to date
Co A
Co B
Co C
Total B & C
Co D
Co E
Co F
Total E & F
Total A..F      
0
Comment
Question by:jmyrtice
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +5
16 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 17074646
Access crosstab queries do not support row totals (which you can pull off in your report), and column sub-totals by a sub-category.  
Crystal Reports does.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17074796
> Access crosstab queries do not support row totals

Yes, it does.  Here is an example from a recent project:

TRANSFORM Sum(q.VolumeFactor) AS SumOfVolumeFactor
SELECT q.Dummy, q.SourceSAID, q.SourceProductCode, Sum(q.VolumeFactor) AS Total
FROM qryMSRtransadjust AS q
GROUP BY q.Dummy, q.SourceSAID, q.SourceProductCode
PIVOT q.ProductCode In ("SBA","CS","FA","*");


Regards,

Patrick
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17074885

With the report grouping/sorting tool in the Access report designer, You can group on field value [for example CompanyNo or CompanyName] or on a prefix based on a group interval [for example the first two digits of a CompanyCode or on letters of the Alphabet like a phone directory].  The cross tab columns and cross tab totals must come from a crosstab query that is used as the source for the report.  The crosstab row grouping totals and grand totals are generated by the Access Report Designer, based on the grouping levels chosen, including a report footer for the grand totals for each column.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 44

Expert Comment

by:GRayL
ID: 17075894
With mathewspatrick's solution you get a row total - then do a Union query of the Sum of the crosstabs to get the column total.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17112933
matthewspatrick, puppydogbuddy, GRayL,

If I am reading the original post correctly, they need "interim" row totals:
B & C but not including A
E & F but not including D

Then a Grand total (A through F)

Can "that" be done with a crosstab?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17113194
Hi Jeff,
I read the above as grouped totals, not as interim totals in the context that interim is usually used.

Most of the time you can handle these types of groupings if you get creative enough.  You can group on character blocks, prefixes or suffxes for examples...or you can concatenate 2 or mpre column values and group on the concatenated value. To illustrate,  If A thru F were company names and column 2 was their sales product , grouping on first 3 characters of column 2 will achieve requested grouping for A thru F above.  Very simplistic for purposes of illustration.
                1    2                                 Grouping = Left([Product],3)
                A  Toys                            Toy
                B   Ice Cream                    Ice
                C   Ice Milk                        Ice
                D   Grocery                        Gro
                E    Clothing Women's         Clo
                F    Clothing Men's              Clo
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17114161
Like puppydogbuddy, I am seeing the subtotals as best accomplished using grouping.

Using an assignment scheme like this:

Category1                    Cat2                            Team
1                                 1                                 A
1                                  2                                 B
1                                   2                                C
1                                  3                                D
1                                  4                                E
1                                  4                                F

Now, if the report is grouped first on Cat1 and then on Cat2, then the subtotals can be presented
as group footers.  One could even use the OnFormat event to hide the group footers for Teams
A & D, if desired.  (I would implement that by putting a calculated field in the group footer that
counts the number of related detail records, and makes Visible = False if the count = 1.)

Patrick
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 17115673
<OT>
matthewspatrick, puppydogbuddy,

Very informative, Thanks!
:)
0
 

Author Comment

by:jmyrtice
ID: 17132760
the grouping works fine but i need it to total by each grouping and since my textboxes are unbound it does not work
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17133472
jmyrtice,

> the grouping works fine but i need it to total by each grouping and since my textboxes are unbound it does not work

Why, exactly?  Why could you not add textboxes in the group footer that have a control source like:

=Sum([UnboundTextBox1])

?

Patrick
0
 

Author Comment

by:jmyrtice
ID: 17137783
This =Sum([UnboundTxtmonthly1]) is not recognized as a valid fieldname or expression
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 17137875
Are you sure that you are *exactly* matching the name of the unbound textbox in the detail section?
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 17137907
jmyrtice,
What Patrick is telling you is this:
            =Sum([YourUnboundTextBoxName])

which probably is:
             =Sum([Txtmonthly1])

           
0
 

Author Comment

by:jmyrtice
ID: 17139132
This exactly what i type in  =Sum([Txtmonthly1])
when i get my error.  I worked around it by doing dsum of the query but I actually want to sum the textbox
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 17468817
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

jjafferr
EE Cleanup Volunteer
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
ID: 17492731
PAQed with points refunded (125)

CetusMOD
Community Support Moderator
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question