crosstab problems

[rwsolutiondesign]

Hello everyone!

Im stuck with a issue that appears to be difficult for me , and probably easy for you. I created a crosstab query that gives me the values i need by date
which is working very well. The only problem I'm having is that I am unable to do a summary based on the values presented. example



what I'm wanting is to have each day totaled at the bottom of the report. Here is my code

TRANSFORM Count(MAINtexarkana.MAINID) AS CountOfMAINID
SELECT MAINtexarkana.PASSENGER, MAINtexarkana.SERVICEID, MAINtexarkana.STATUSID


FROM MAINtexarkana
GROUP BY MAINtexarkana.PASSENGER, MAINtexarkana.SERVICEID, MAINtexarkana.STATUSID
PIVOT Format([dateoftrip],"Short Date") In (5/1/2017,5/2/2017,5/3/2017,5/4/2017,5/5/2017,5/8/2017,5/9/2017,5/10/2017,5/11/2017,5/12/2017, 5/15/2017,5/16/2017,5/17/2017,5/18/2017,5/19/2017,5/22/2017,5/23/2017,5/24/2017, 5/25/2017, 5/26/2017,5/29/2017,5/30/2017,5/31/2017);

[June7]

The image is not displaying for me.

This query is the RecordSource for a report object? What have you tried? Did you put textbox in report footer section with calculation to Sum() field?

Building a stable report to run perpetrually based on CROSSTAB is very difficult, especially when pivoting dates. With the query shown, you will have to rebuild the report every month. Review http://allenbrowne.com/ser-67.html

[rwsolutiondesign]

Yes I've tried to footer calculation to sum the field by creating a text box, which gives me the totals i am needing but is invisible in datasheet view. What im thinking is that
maybe i should do a subreport. And yes, you are correct i would have to rebuild my report every month. I can not find another way to filter by day using a crosstab query.

[June7]

Not sure what you mean by 'datasheet view' for a report. Report has ReportView and PrintPreview.

[aytee111]

This has an example of a calendar form using a crosstab query.

[HiTechCoach]

Here is an ecample of how I get totals for rows and columns of a crosstab query.

Dynamic Report based on a Crosstab query