organizing data in reports problem

[Guest]

I'm making a report based on an underlying crosstab query. The query orders
numerical values first by category #, then by a variable # -- these aren't
comparable to their text equivalents in order. In other words, if I run the
query by category name, it can be sorted alphabetically, but I don't want
that; I want it to be sorted by the category #, which might be equivalent to:
A, C, D, B, etc. Same with the variable # and variable name. The query
happily performs these operations and I get output with A, C, D, B (category
names), etc.

If I base a report on that query, however, no amount of my manipulation can
recreate that same order. If I want to use the category and variable names,
then I can't seem to get them to appear in the category # and variable #
orders ...

I must be doing something simple wrong? Help.

Thanks,

Meredith

 

[Duane Hookom]

It isn't clear (at least to me) if you want to sort your columns or
rows/records. Sorting of records in reports is only reliably done by using
the Sorting and Grouping dialog. Click the icon that looks kinda like:
+----+
|/{= |
|\{= |
+----+

 

[Guest]

I may not be explaining clearly enough. I'm reporting ratings data for
therapists; the data is represented by variables like "Group response
documented." There are approximately 15 variables and they're within 4
categories, like "Group Therapy," "Family/Individual Therapy," etc. I have
created number vars in Access for both the variable and category vars so that
I can order them, not alphabetically, but by their content.

I run a crosstab query which displays ratings by quarter year for each
variable and it ranks them in the order of the numerical vars I created. The
report, then, should display the same thing, basically, but instead if I try
to sort/group within the report wizard, I end up only being able to do so the
way I want to if I use the numerical vars -- and I need the actual variable
'names' to display instead.

Does that make more sense?>

Thanks,

Meredith

 

[Duane Hookom]

Again, are these numerical vars used as column headings/field names or are
they data values in fields? Since I am having trouble understanding, maybe
you could type a few sample records as they currently display in your report
and then how you would want them to display.

 

[Guest]

I'd like the data to look like this in the report:

Q1 Q2 Q3 Q4
Group Therapy
Group response documented 1.00 1.00 0.833 0.900
Group plan documented 1.00 1.00 1.000 1.000

Family/Ind Therapy
Type documented 1.00 0.875 0.900 1.000
Progress goals documented etc.

In order, though, to get these to display the way I want them to -- rather
than alphabetically -- I created numerical variables to correspond to the
order I want them to appear; so Group Therapy might be Category=1 and Group
response... might be ReportOrderVar = 1 etc. The crosstab query that
calculates the values by quarter for the variables displays correctly if I
include the Category and ROV vars. The report, however, won't do that any way
I can figure ...

Does this help?

Thanks,

Meredith

 

[Duane Hookom]

Did you try using the sorting and grouping dialog as suggested? Does your
crosstab include the ROV vars? The ROV vars are the values you would need to
use in the sorting and grouping.

 

[Guest]

I do use the ROV vars in the sorting & grouping but what I end up with is
something like (omitting the Q1 labels):

Category #: 1
ROV 1: 1.000
ROV 2: 1.000
ROV 3: 1.000
ROV 1: 0.900

Etc. It's VERY lengthy; what I think it's doing is trying to list all the
values for the quarters vertically -- the Cat labels and Variable name labels
are off to the right and also go on forever...

??

 

[Guest]

I forgot to say that I can tweak it so that the report will display the
variables in the order I want -- simply by deleting the ROV in the report and
moving the Variable Name over to its place. But, if I try to add another
grouping level to the mix, i.e., Category, it seems to fall apart ...

That can't be true, but I'm missing something. If I try the same 'trick' on
categories (i.e., deleting Cat # and moving the Category name variable to its
place, it doesn't work.

 

[Duane Hookom]

Using the ROV in the sorting and grouping doesn't suggest that you have to
display the ROV in your report section(s).
What do the records look like that come from your report's record source?

 

[Guest]

I just 'got' it (accidentally, believe me). If I put the Category in its own
header on the report, then it only displays once per category. The Variable
Names go in the detail section along with the per quarter data ...

I'm not entirely sure what I did, but thank you for helping. I'm pretty sure
something you sent over the ether made the difference

THANKS.