multi-column report formatting question

[gingernob]

I have a report with 18 columns. The intention is to print out a seating plan of a class room. the max seats / row is 18 BUT sometimes it is less.
the query for data provides me with 3 columns of data:
Student #, seat # and row #.
obviously running the report i get exactly the max. format i want but only in 18 columns.
How do i use the row# to limit seats to <18 if there are only say 11 seats in that row?
I have attached a image of the test report format.
I thought i could use groupings on the row# but that does not seem to do the trick.
Any ideas appreciated.

[NeoPa]

What data are you working with?

[gingernob]

Hi
Umm confused; data as stated studentid,seatn um,rownum
eg;
studentid seatnum rownum
1 1 1
2 2 1
3 3 1
4 4 1
5 5 1
6 6 1
7 7 1
8 8 1
9 9 1
10 10 1
11 11 1
12 12 1
13 13 1
14 14 1
15 15 1
16 16 1
17 17 1
18 18 1
19 19 2
20 20 2
21 21 2
22 22 2
23 23 2
24 24 2
25 25 2
26 26 2
27 27 2
28 28 2
29 29 2
30 30 2
31 31 2
32 32 2
33 33 2
34 34 2
35 35 3
36 36 3
37 37 3
38 38 3
39 39 3
40 40 3

[jimatqsi]

Gingernob,
It's not really very clear what your data is. I see seat numbers greater than 18. I think that is just a mistake in your mockup of the data.

We can't tell what your table structure is, nor can we see what the query you are using looks like. But I think you are telling that you have a simplified table with 3 data elements in it, "student", "row" and "seat."

That being the case, you might want to consider a cross-tab query. Crosstabs will make a column for each value in a column that you select ('row', in this case). If there are 18 different values in column 'row', you will get 18 columns in your query results. If a value in a column gets returned as null for a particular row, then there was no matching student/seat combination for that row.

Here's a sample crosstab query:

TRANSFORM First(Seating.StudentID) AS FirstOfStudentID
SELECT Seating.RowNbr
FROM Seating
GROUP BY Seating.RowNbr
PIVOT Seating.SeatNbr;

Will that help?

Jim

[gingernob]

Jim
Thank you and I think we are on the right track but as I have never used crosstabs before, it looks like it is giving me the data I need;
Used the following query for source:

SELECT EXAMENTRYRECORDS.EXAMENTRYRECORDID, EXAMENTRYRECORDS.EID, EXAMENTRYRECORDS.CANDIDATENUMBER, E_VENUE_ALLOC.SEATNUMBER, E_VENUE_ALLOC.ROWNUM, E_VENUE.VENUE_NAME
FROM E_VENUE INNER JOIN (E_VENUE_ALLOC INNER JOIN EXAMENTRYRECORDS ON E_VENUE_ALLOC.E_VENUE_ALLOCID = EXAMENTRYRECORDS.E_VENUE_ALLOCID) ON E_VENUE.E_VENUEID = E_VENUE_ALLOC.E_VENUEID;

Then the following for the crosstab query:

TRANSFORM First(qrystudentseating.[CANDIDATENUMBER]) AS FirstOfCANDIDATENUMBER
SELECT qrystudentseating.[ROWNUM], First(qrystudentseating.[eid]) AS ExamID
FROM qrystudentseating
GROUP BY qrystudentseating.[ROWNUM]
PIVOT qrystudentseating.[SEATNUMBER];

which gave me the attached capture3.jpg image output.
Now my question is...what do i do with it. As i say, never using crosstabs before it looks right but then how do i design a report to look like capture.jpg attachment as the fields are not seatnum but the seatnumbers themselves. do i have to have a txt control for every instance on the report...would that not get huge for a 160 seat room?
If it is any consolation, you are definitely leading me into exciting new territory.
David

[NeoPa]

gingernob: Hi
Umm confused; data as stated studentid,seatn um,rownum

You're confused?

I was actually after the data that you start with. What is used to determine the answers to your question (as in what you're working with) - rather than the expected outcome. BTW Was it by design that your first attached thumbnail seems to show different figures from the data you posted? I was confused by that as I guessed it should have reflected the same situation (18 in both first two rows rather than an 18 followed by a 16). Unfortunately, thumbnails on here are almost illegible anyway (Our failure there - not yours) so almost useless for conveying information sadly.

Anyway, it sounds like Jim has you covered, so I'll leave you in his capable hands.

[jimatqsi]

Yes, I understand your excitement and confusion, I often start out thinking cross tabs are stupendously great; and then I find them difficult to work with. It could yet be the case for you, but I think it might actually work here.

If you know for sure that you will have 18 columns returned by the crosstab (even if some rows have null for the seat in, say, 16, 17, 18, or only 18, or whatever) then you can bind 18 text boxes to each of the 18 returned columns. The problem comes if you run against some data where none of the classes have more than 17 seats in a row, suddenly you have an 18th text box bound to data that is not supplied by the query. So dealing with crosstabs can be a sticky wicket.

So, if you KNOW you will always have 18 columns, just bind to the 18 text boxes, plus a text box for the row number. The labels for the 18 text box columns will be "Seat 01", "Seat 02", etc... What actually appears in each column will be the ID of the student to be in that seat.

If you are not sure you will get exactly 18 columns returned, you can edit the properties of your crosstab query to specify the column names you expect. I haven't done that a lot, but you type in each expected result followed by a semi-colon. Then even if you don't get any 18s, an 18th column will be returned and all the rows will have a null value for the student in that seat.

Have fun discovering how to use crosstabs.

Jim