Access Reports - Align Results Horizontally

[Rabbit]

@NeoPa, to number the rows, the OP would have to use one of those ranking queries. But sometimes they're more trouble than they're worth. It's probably easier to just use 3 different subreports.

[NeoPa]

As you say Rabbit, the subreport approach is probably the preferred one, but would I be right in thinking a ranking query would depend on a sorting order being defined/specified. It couldn't work with the random order of the records, or positionally?

[zmbd]

I Knew I had done this before!!!

YEA: I found version two of my database... it's not one that I use everyday... it one that I setup for my labs. My PC crashed and when they restored the drive I think they used an old backup image and I've been fighting this thing for about a month now to get all of my newest development db's back on the drive. Anyway... I had need to go back into the VPN to look at stuff.. long story... and decided I look at that DB on their PC.

And in that DB is the old url reference.... and I just found the new one.



[imgnothumb]http://bytes.com/attachments/attachment/6761d1355101195/bytesthread_945 069.jpg[/imgnothumb]

Examples of reports that start each group in a new row or column

TRICK:
You can NOT use the wizard to setup the report otherwise the columns start getting messed up.
Start with a blank report in design mode, then add the fields as listed.

Nothing fancy... no unions... no subreports... no vba... no pivot tables. Just the single select query that relates the names to the normalized table.
(edit: there are joins between the player and postion tables to the roster table and the query does use those relationships to pull the names instead of the primary keys)

[NeoPa]

That looks like it might be interesting Z. Why is there no explanation (No SQL). The link seems to give some report settings, but nothing about the data it's working from. Am I missing something obvious here?

[zmbd]

Exactly.


There's nothing special about the SQL nor the tables from a normalized view point anyway:

qry_roster

SELECT 
   tbl_positions.position_name,
   tbl_players.player_name
FROM tbl_positions 
   INNER JOIN 
    (tbl_players 
       INNER JOIN tbl_roster 
         ON tbl_players.player_pk = 
            tbl_roster.roster_fk_players) 
    ON tbl_positions.position_pk = 
      tbl_roster.roster_fk_positions;

Tbl_positions
[position_PK] autonumber
[position_name] text(25)

Tbl_players
[player_PK] autonumber
[player_name] text(25)

Tbl_roster
[roster_PK] autonumber
[roster_fk_posit ions] long 1:M tbl_positions
[roster_fk_playe rs] long 1:M tbl_players

Report1
bound to qry_roster
Group on positions
Labels removed
[tbl_positions.p osition_name] in the positions header
[tbl_players.pla yer_name] in the details section

settings as indicated in the link.

The design of this report is similar to the one in the previous example, but the NewRowOrCol property of the CategoryID header is set to Before Section, and Column Layout is set to Down, Then Across in the Page Setup dialog box.

[Rabbit]

@NeoPa, yes, you would need to specify something to sort by, you could use a PK or a randomly generated number. I don't think the order matters to the OP.

[zmbd]

opps, I did specify to sort on the player names in the report otherwise nothing special.
((Correction... in the posted screen shot I had not added any sorting; however, after haveing made the post I thought sort by name would be nice and added it to the grouping to sort on tbl_players.pla yer_name))


{edit - Screen shot of all data tables and the query}
[imgnothumb]http://bytes.com/attachments/attachment/6762d1355109687/bytesthread_945 069_data.jpg[/imgnothumb]

[NeoPa]

@Rabbit.
Cool. That makes sense.
@Z.
Ah. So the query results are grouped by the Position and essentially a break (In this case, because columns are used, the break goes to a new column rather than a new page.) occurs between each Position. I think I get it now.

May I say I like that solution. Far from an obvious one, so pretty clever, and elegant too :-)
@Lisa.
In my first post (#6) I suggested we may need to fall back on a code-based solution if no-one managed to come up with a more logical one. Well, it seems they have. One of the benefits of throwing something out there into the mix.