Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2006-07-10
16
Medium Priority
?
386 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 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 93

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 93

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 93

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 93

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

971 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