Seating Chart

[jschping]

Hi,

I want to make a seating chart report for my student's finals testing.

I have an Excel spreadsheet with each student's name, their room number, row
number, seat number.

I want to make a chart that is a visual aid to where they sit. So I don't
jsut want a table listing of names and rows, I want it to come out in the
rows and in the right order. It doesn't have to be spectacular, but it should
be a visual aid.

For example:

ROOM 201

ROW 1 ROW 2 ROW 3 ROW
4

Jack Frost Amelia Earheart George Washington Joe Shmoe

Marie Curie Abe Lincoln Hillary Clinton Marc Summers

And then do the next room.

How should I do this? How should I structure my table(s)?

The hard part (I think) is getting multiple columns that each have a
different record.

Thanks!

 

[jschping]

They messed up my formatting. This is how it should look:


------------------------------ROOM 201

ROW 1------------ROW 2------------- ROW 3--------------- ROW 4

Jack Frost----- Amelia Earheart--- George Washington -----Joe Shmoe

Marie Curie----- Abe Lincoln --------Hillary Clinton -------Marc Summers

(The lines are just to hold the place so you can see where everything should
be.)

Thanks!

 

[KARL DEWEY]

If your Excel spreadsheet for room number, row number, seat number is like
this --
Room Row Seat Name
201 1 1 Jack Frost
201 2 1 Amelia Earheart
201 3 1 George Washington
201 4 1 Joe Shmoe
201 1 2 Marie Curie
201 2 2 Abe Lincoln
201 3 2 Hillary Clinton
201 4 2 Marc Summers
then you can use a crosstab query like this --

Transform First([Name]) AS FirstOfName
SELECT Room
FROM YourLinkedExcel
GROUP BY Room, Seat
PIVOT Row;

 

[Brave Hearts]

Thankyou, KARL DEWEY, i have the same problem like this. now solved
for your great small example... ..

 

[jschping]

Thanks so much for replying.

I know I sound like a novice, but I don't know what a crosstab query is.
Where should I paste that code that you wrote? How do I use that in a report?

Thanks so much!

If your Excel spreadsheet for room number, row number, seat number is like
this --
Room Row Seat Name
201 1 1 Jack Frost
201 2 1 Amelia Earheart
201 3 1 George Washington
201 4 1 Joe Shmoe
201 1 2 Marie Curie
201 2 2 Abe Lincoln
201 3 2 Hillary Clinton
201 4 2 Marc Summers
then you can use a crosstab query like this --

Transform First([Name]) AS FirstOfName
SELECT Room
FROM YourLinkedExcel
GROUP BY Room, Seat
PIVOT Row;

--
Build a little, test a little.

They messed up my formatting. This is how it should look:


------------------------------ROOM 201

ROW 1------------ROW 2------------- ROW 3--------------- ROW 4

Jack Frost----- Amelia Earheart--- George Washington -----Joe Shmoe

Marie Curie----- Abe Lincoln --------Hillary Clinton -------Marc Summers

(The lines are just to hold the place so you can see where everything should
be.)

Thanks!

 

[KARL DEWEY]

Create a new query in design view but do not select a table, click on VIEW -
SQL View, and paste in the window over the 'SELECT;' you see there.
--
Build a little, test a little.

Thanks so much for replying.

I know I sound like a novice, but I don't know what a crosstab query is.
Where should I paste that code that you wrote? How do I use that in a report?

Thanks so much!

If your Excel spreadsheet for room number, row number, seat number is like
this --
Room Row Seat Name
201 1 1 Jack Frost
201 2 1 Amelia Earheart
201 3 1 George Washington
201 4 1 Joe Shmoe
201 1 2 Marie Curie
201 2 2 Abe Lincoln
201 3 2 Hillary Clinton
201 4 2 Marc Summers
then you can use a crosstab query like this --

Transform First([Name]) AS FirstOfName
SELECT Room
FROM YourLinkedExcel
GROUP BY Room, Seat
PIVOT Row;

--
Build a little, test a little.

They messed up my formatting. This is how it should look:


------------------------------ROOM 201

ROW 1------------ROW 2------------- ROW 3--------------- ROW 4

Jack Frost----- Amelia Earheart--- George Washington -----Joe Shmoe

Marie Curie----- Abe Lincoln --------Hillary Clinton -------Marc Summers

(The lines are just to hold the place so you can see where everything should
be.)

Thanks!

:

Hi,

I want to make a seating chart report for my student's finals testing.

I have an Excel spreadsheet with each student's name, their room number, row
number, seat number.

I want to make a chart that is a visual aid to where they sit. So I don't
jsut want a table listing of names and rows, I want it to come out in the
rows and in the right order. It doesn't have to be spectacular, but it should
be a visual aid.

For example:

ROOM 201

ROW 1 ROW 2 ROW 3 ROW
4

Jack Frost Amelia Earheart George Washington Joe Shmoe

Marie Curie Abe Lincoln Hillary Clinton Marc Summers

And then do the next room.

How should I do this? How should I structure my table(s)?

The hard part (I think) is getting multiple columns that each have a
different record.

Thanks!