Solved

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

Posted on 2006-07-10
16
346 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
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
 
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

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…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

939 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

10 Experts available now in Live!

Get 1:1 Help Now