Solved

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

Posted on 2006-07-10
16
338 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
  • 4
  • 3
  • 3
  • +5
16 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
> 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
Comment Utility

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
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
<OT>
matthewspatrick, puppydogbuddy,

Very informative, Thanks!
:)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:jmyrtice
Comment Utility
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
Comment Utility
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
Comment Utility
This =Sum([UnboundTxtmonthly1]) is not recognized as a valid fieldname or expression
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
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
Comment Utility
jmyrtice,
What Patrick is telling you is this:
            =Sum([YourUnboundTextBoxName])

which probably is:
             =Sum([Txtmonthly1])

           
0
 

Author Comment

by:jmyrtice
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed with points refunded (125)

CetusMOD
Community Support Moderator
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now