• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

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

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
jmyrtice
Asked:
jmyrtice
  • 4
  • 3
  • 3
  • +5
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Patrick MatthewsCommented:
> 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
 
puppydogbuddyCommented:

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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
GRayLCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
puppydogbuddyCommented:
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
 
Patrick MatthewsCommented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
<OT>
matthewspatrick, puppydogbuddy,

Very informative, Thanks!
:)
0
 
jmyrticeAuthor Commented:
the grouping works fine but i need it to total by each grouping and since my textboxes are unbound it does not work
0
 
Patrick MatthewsCommented:
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
 
jmyrticeAuthor Commented:
This =Sum([UnboundTxtmonthly1]) is not recognized as a valid fieldname or expression
0
 
Patrick MatthewsCommented:
Are you sure that you are *exactly* matching the name of the unbound textbox in the detail section?
0
 
puppydogbuddyCommented:
jmyrtice,
What Patrick is telling you is this:
            =Sum([YourUnboundTextBoxName])

which probably is:
             =Sum([Txtmonthly1])

           
0
 
jmyrticeAuthor Commented:
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
 
jjafferrCommented:
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
 
CetusMODCommented:
PAQed with points refunded (125)

CetusMOD
Community Support Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 4
  • 3
  • 3
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now