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]

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


Or do you really want the names repeated as shown?

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 misunderstandin g, 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.