Trying to create report listing records horizontally

[beastmp13]

Hi there,
I've done a bit of searching on this topic, and have tried some of the suggestions mentioned, but the layout I am looking for does not seem to make these solutions work for me.
I'm keeping track of a soccer team and have a game table listing the gameid, quarter, and each of the positions on the field. I use this to enter where I would like my players to play during each quarter, and would like to create a report based on this and some other information, however, I would like each of the quarters for the game to be displayed horizontally instead of vertically.

Quarter1

Quarter2

LF	CF	RF	LF	CF	RF
Player1	Player2	Player3	Player1	Player2	Player3
LD	CD	RD	LD	CD	RD
Player4	Player5	Player6	Player4	Player5	Player6
	Goal			Goal
	Player7			Player7
Side1	Side2	Side3	Side1	Side2	Side3
Player8	Player9	Player10	Player8	Player9	Player10

Underneath this will be more information about the specific game, and on top will be the gameid and other headers.

I'd appreciate any suggestions as to how to do this. It's not super important as I have a word document with tables and such that I fill out and print, but I'd like to have it all in one place.

Thanks in advance

[June7]

What have you tried? I presume you tried a CROSSTAB on the raw data and it didn't work.

Might get what you want by first doing a query that calcuates a field concatenating Quarter field with other fields and using that constructed field as the column field in CROSSTAB.

Have you considered IIf expressions in query, review http://datapigtechnologies.com/flashfiles/crosstab.html

Then there is VBA to manipulate data and save in 'temp' table, records are purged after the process completes.

Here is a thread on the topic https://www.accessforums.net/forms/d...lly-20989.html
Check out the additional links in that thread.

[beastmp13]

That second link was actually the one that I was looking at right before I decided to post, however it didn't sound like it was resolved, so here I am.
Crosstabs on the data didn't work because of the layout. I want the fields and their headers to be laid out like they are on the soccer field. I've used DLookup before in another database that I've put together, but I was hoping to avoid that on this one because all the data is right there, I just need it put together.

Here's the raw data:


And here's how it's showing up in the report(Here I'm using a subreport):


This is ultimately how I want to get it to look:


Maybe I'm just missing something easy, but I've been using Access for years, and this has me stumped. I've been avoiding vba but it looks like that might be the only way to get the layout like I want.

[June7]

That thread was not resolved but it is the other links referenced that are of true value:

https://www.accessforums.net/reports...html#post99062
which has link to:
http://forums.aspfree.com/microsoft-...ry-322123.html

However, consider:

qryQtr1
SELECT GameID, Quarter AS Qtr1, OpposingTeam AS Qtr1OP, LeftForward AS Qtr1LF, CenterForward AS Qtr1CF
FROM U10BSSGames
WHERE (((U10BSSGames.Quarter)=1));

Do similar query for each quarter.

Now join the 4 queries.
SELECT qryQtr1.GameID, qryQtr1.Qtr1, qryQtr1.Qtr1OP, qryQtr1.Qtr1LF, qryQtr1.Qtr1CF, qryQtr2.Qtr2, qryQtr2.Qtr2OP, qryQtr2.Qtr2LF, qryQtr2.Qtr2CF, qryQtr3.Qtr3, qryQtr3.Qtr3OP, qryQtr3.Qtr3LF, qryQtr3.Qtr3CF, qryQtr4.Qtr4, qryQtr4.Qtr4OP, qryQtr4.Qtr4LF, qryQtr4.Qtr4CF
FROM qryQtr4 INNER JOIN (qryQtr3 INNER JOIN (qryQtr2 INNER JOIN qryQtr1 ON qryQtr2.GameID = qryQtr1.GameID) ON qryQtr3.GameID = qryQtr1.GameID) ON qryQtr4.GameID = qryQtr1.GameID;

There is a limit of 255 fields for a query.

[beastmp13]

The sublinks helped a little. It looks like what I did with my other database. So just to clarify, as I don't have too much experience with crosstabs, the values seem to only be able to handle numerical data. Is that correct? Like I said, crosstabs don't look like they will work here as my data is laid out the way it is, but it would be good for reference.

I was looking at joining them together with the queries as in your SQL statements, but being my own personal database, I figured that if I were to go that route, it might just be easier to reconfigure the table to hold the entire game's data vs. a separate record for each quarter.

Looks like I might just go that route, as the queries would essentially do the same thing, and it would be simpler than doing DLookup for each field.

[June7]

Crosstabs can do Count of non-numerical data.

I agree that Crosstab not suited.

It is a balancing act between normalization and ease of data entry/output. The merged table you describe might be best for you and it probably meets some normalization standards - such as every field should have data.

[beastmp13]

Thank you for the help. I ended up using the 4 queries with the join, as it was easier to do for other data that I needed to obtain for the report.