Access Reports - Align Results Horizontally

[lisamp]

new user and this seems like it would be easy to create, and i'm way over thinking. in short i have a table with a NAME field, and then a field for each position they may play. PITCHER, CATCHER, SS, ETC.

i want to create a report where each of my fields are labeled at top. Then if they play that position it will plug their name in the position colume. i ran a query which pulls the information, then when i put it in a report, the results for each colume do not start on line one of detail, it leaves big gaps. this is what i'm getting.

PITCHER        CATCHER          SS
Smith
Jones                           Jones
Wills
Cline                           Cline                  
King            King
Glass           Glass

I am looking to push all names to top, and not leave gaps by colume. should look like this:

PITCHER         CATCHER          SS
Smith           King             Jones
Jone            Glass            Cline
Wills
Cline
King
Glass

thanks for help!

[zmbd]

I'm sorry, do you mean this is the result?

PITCHER CATCHER SS
Smith King Jones
Jone Glass Cline

Or do you really want the names repeated as shown?

[zmbd]

Opps, I also meant to ask for your SQL.
Please remember to format it using the <CODE/> button.

By giving us your SQL for the underlying query, we can use the same field names and also check to make sure that there isn't something fundamentally wrong.

[lisamp]

@zmbd
the spacing is off with what I posted on my question, but the second one is the "idea" of the result im looking for (of course using all the records in my table). The first table is the result I am getting.

the names are repeated because they may play both positions, PITCHER and CATCHER, therefor the last name was filled in that colume based on the query i set up in office 2007, Access. Again the query is working, however when i put it in a report, as you can see, the columes don't all line up horizontally in the detail section. I want each colume to populate on line 1, not leave gaps.
I simply want it to have 3 columes labeled PITCHER, CATCHER, SS and then have the players name listed under the colume (or position) in which they play. they may play two or three positions, so there name would populate under each colume. does that make sense.

PITCHER CATCHER

[Rabbit]

Use code tags when you need to preserve white space.

You'll need 3 queries, one for each position, in each query, you will need to number the rows, and then join the queries together with an outer join.

Alternatively, you could use 3 subreports.

[NeoPa]

This is an extraordinarily difficult question as you seem to be asking for what is essentially three separate queries to appear synchronised without any logical point of synchronisation (other than position within the list which is anathema to RDBMS theory).

Nothing immediately springs to mind (in Access - Excel could handle it easily but then there are no such database constraints on Excel data). I'll keep my mind's eyes peeled for any ideas though. If anything occurs to me I'll be back. Obviously I'm excluding a code-based solution as being fundamentally too much of a kludge, but maybe we'll have to fall back on that if nothing logical occurs to anyone.

[NeoPa]

Rabbit:
You'll need 3 queries, one for each position, in each query, you will need to number the rows, and then join the queries together with an outer join.

1. How would you suggest numbering the records from the three queries?
2. How would an OUTER JOIN (For Access I assume you're referring to an unspecified linking, or Cartesian Product, of the three tables but filtering with a WHERE clause) manage to produce the results required? I assume you envision the linking factor to be the ordinal position value of each query record.

PS. Sorry, the answer to #2 is a little obvious (blush). I wasn't seeing it when I posted.

[zmbd]

OK, Now that I see the formatting (thnx Rabbit, he's my Hero :) )

From Rabbit, Neopa, and the reformat: What I understand you to want this to be as in a flat file - excel worksheet type arrangement.

Silly question I know; however, do I understand what you want correctly?

[NeoPa]

Z:
do I understand what you want correctly?

The request was for a report if that helps Z. Does that answer your question or do I misunderstand it?

[zmbd]

No, I understand it is for a report.

I have something similar at work that might work; however, before I go to the trouble of attempting the VPN connection I wanted to make sure I understood the OP correctly.

The VPN is very hostile to my router firewall for some reason. I've even let the Corporate I.T. gurus take a look (like Really wanted to let them have free reign on the personal pc) and they couldn't figure it out. Also because it is on the VPN I can't transfer the files between the VPN and Local directly. Instead I have to do some juggling with the encrypted drives. Real Pain.

IF I had my notes, then I'd just post that information.

Basically, it's a cross-tab query report; however, as I've mentioned in the past, cross-tabs are not my strong point; thus, I'd have a lot more confidence in offering the solution if I either had my notes or my work handy. :)

[NeoPa]

That's fine. I thought it might have been a simple misunderstanding, in which case I might have saved you the time of having to wait for the sun to come up in whatever part of the world Lisa is posting from. It's seems you'll have to wait for her however, I'm afraid.

Nor can I help with your Central IT and VPN issues :-( I was in IT for many years, but we preferred the moniker Group IT for the acronym. Of course, my main role was in third-line support, or Tertiary IT. Far more interesting ;-)

[lisamp]

Thanks. What i'm taking out of this, is that can't be done as easy as i thought. And maybe i was on right track. I did run seperate querys for each position and filtered the blanks. Then i created a reeport for each, and then i created a final report with all the other positions as sub-reports. I really thought there should be an easier way. But that's the way i'm going to go for now. Again i'm new and much of what was discussed is over my head. Thanks everyone

[Seth Schrock]

I feel funny mentioning this since several experts haven't, but wouldn't it be easier if the database was normalized? In the OP, it says that the table has a field for each position. My understanding is that it would be better to have a many-to-many relationship between the players table and the position table. It would then be very simple to pull a report that grouped by position with the groups vertically arranged. If you really need it horizontally arranged, I don't know if it would help having it normalized for this particular problem. If I'm wrong, could someone explain where I've gone off?

More to the point of the question, couldn't you use a separate query for each position and then put a subform on the report for each position? Normally I wouldn't do this, but I don't think that the number of positions in baseball will change anytime soon so we should be safe doing this.

[zmbd]

Seth,
You are right as far as the normalization.

As OP was talking about a report, I took this to mean that there was table on the report...

As for the suggested solution, Post 5 and post 12 both mention.

-
I braved my VPN last night... what I have is a form in pivot table view... it's actually quite ugly in that it has a total columns and the other "features" However, I played with it here at the house and I can get the thing to list as OP wants... it's just... really... well, ugly. And indeed this database has a table with employee info, and table with position (actually labs), and then a linking table (it's a roster for who is in what lab at the current movement as we rotate people thru positions/locations - useful when transferring workloads - I mean, telephone calls, yea, that's it, telephone calls! ;-)).

[Seth Schrock]

I did miss the references to the subforms in posts 5 & 12. As far as the table goes, I didn't think that there would be those blanks if the table referred to was just a table on a report. I guess I assumed that the table mentioned was a TableDef.

[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.

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_positions] long 1:M tbl_positions
[roster_fk_players] long 1:M tbl_players

Report1
bound to qry_roster
Group on positions
Labels removed
[tbl_positions.position_name] in the positions header
[tbl_players.player_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.player_name))


{edit - Screen shot of all data tables and the query}

[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.