Cross Tab Query

[Teryl]

I'd like to add a record count to the report of a cross tab query. I've add the calculated text box in the detail and group footer sections of the report, but I keep getting the number 510 or 1. I've set the properties to(RunningSum = Over Group) but I still can't get it to work....what am I doing wrong?

 

[Russell Hauf]

If you just want a record count, you can do something like this:

In the detail section, add a TextBox (txtRecCount, for instance). Set its visible property to False, and set its control source to =1. Also set the running sum to Over All.

In the report footer, put a text box where you want the record count. For its control source, put =[txtRecCount]

This should do it! Hope it helps,

Russell

 

[Teryl]

I've done what you suggested. But what I want are totals over a individual group within the report. I have motors, sent to vendors. I want to compare the cost of repairs to these motors using the cross tab query. I also want to count the individual motors by their particular group. example of query results below

Frame# Total Hannon Tri-State Warwood
0234 103,567 96,145 5,044 2,378

I want to now include the total number of motor repaired for each vendor. something like 18 total 9-hannon, 6-tri-state, 3-warwood.

I hope this makes sense. Maybe I should do the calculations in the query, if so how?

Thanks in advance...you guys are great!

 

[Russell Hauf]

If you have your report grouped by Vendor (group footer, at least), then just put the text box that I said to put in the Report Footer into the Group Footer.

Edit: I see that you want the totals at the bottom. I'll get back to you.

-rh
This message was edited by Russell Hauf on 2002-12-12 10:43

 

[Russell Hauf]

Ok, if you want the totals at the end of each group, then you can do what I said above (putting the 2nd text box in the group footer), but you want to change the "invisible" text box's Running Sum property to "Over Group" instead of "Over All".

If you want to have the totals for each group at the bottom of the report, create a new crosstab query where you group by the field that you want to group on, and do a count on the motors. Base a new report on that query, then put that new report in your original report's Report Footer section as a sub report.

Make sense? Sorry I can't explain more right now, but I'm busy at work! Let us know if you have more questions,

Russell