HOWTO: [wierd+wild] columnar reporting w/ varying rows???

[nsh]

running access 2k;

I have some stats (bowling) that I want to print in columns on a
report.

The top portion of the report will have a team-members name, and the
bottom of the report will have a

sub-report that presents the stats in UP-TO 4 columns.

Here's how it works (in concept):
a team may have 2 to 5 people; not everyone on the team bowls each
week.

So - I want to produce a report that shows up to 4 columns - ONLY the
most current 4 days (TOP 4).
It will look something like this:

NAME 3/1/05 3/8/05 3/14/05 3/20/05
__________________________________________________ ____________
Mary 465 397 525
Harry 592 604 575 602
Jerry 418 427
Gary 214 397 255 327
Larry 601 650 647 599

all of the names [which don't change] are actually on the master report
- only the dates are in the

sub-report.

Here's how the data is structured in tables:
BOWLER BOWL_DAYS BOWL_DATA
b_key (key) b_key (k) b_key (k)
name bdate (k) bdate (k)
score (k)

Now - the organization of the data is set in stone - I've VASTLY
simplified the concept. So the real question is how to do this report.
I've tried everything I can think of:
cross tab queries; queries FROM crosstab Q's; cross tab queries from a
"TOP 4" query;
the sub-report formatted thusly: AND Thusly:
(header) (header)
[mary] [name]
[harry]
[jerry]
[gary]
[larry]

With the "[name]" version, I tried doing some fancy vb record/column
counting, so as to know when to "go to the next column".

Nothing I've tried works right, and/or is error free!

What's the best way to accomplish such a report from the given data
organization?

TIA!!
Ned

[Chuck Grimsby]

I noticed you tried doing a crosstab query from a Top4 query, but have
you tried doing a Top4 query on the crosstab?

If you are looking at having multiple teams, you'll need to include
that as your a part of your crosstab, and using that field as a part of
the Top4's selection criteria.

ns*@starnetwx.net wrote:

running access 2k;
I have some stats (bowling) that I want to print in columns on a
report.
The top portion of the report will have a team-members name, and the
bottom of the report will have a
sub-report that presents the stats in UP-TO 4 columns.
Here's how it works (in concept):
a team may have 2 to 5 people; not everyone on the team bowls each
week.
So - I want to produce a report that shows up to 4 columns - ONLY the
most current 4 days (TOP 4).
It will look something like this:
NAME 3/1/05 3/8/05 3/14/05 3/20/05
__________________________________________________ ____________
Mary 465 397 525
Harry 592 604 575 602
Jerry 418 427
Gary 214 397 255 327
Larry 601 650 647 599
all of the names [which don't change] are actually on the master report
- only the dates are in the
sub-report.
Here's how the data is structured in tables:
BOWLER BOWL_DAYS BOWL_DATA
b_key (key) b_key (k) b_key (k)
name bdate (k) bdate (k)
score (k)
Now - the organization of the data is set in stone - I've VASTLY
simplified the concept. So the real question is how to do this report.
I've tried everything I can think of:
cross tab queries; queries FROM crosstab Q's; cross tab queries from a
"TOP 4" query;
the sub-report formatted thusly: AND Thusly:
(header) (header)
[mary] [name]
[harry]
[jerry]
[gary]
[larry]
With the "[name]" version, I tried doing some fancy vb record/column
counting, so as to know when to "go to the next column".
Nothing I've tried works right, and/or is error free!
What's the best way to accomplish such a report from the given data
organization?

[nsh]

hi Chuck,

Tx much for your reply;

I have not tried reversing the top4/Xtab relationship - would that
really make a difference?
I'll have to check into that....

Right now, I'm only reporting on 1 team - but in the future I expect to
expand that; as well as have the ability to add additional members to
the 1 team (right now it's fixed).

I'll check out reversing - TX....
Ned