Crosstab Query on tables with Many to Many relationship

D

Doctor

How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
 
K

KARL DEWEY

By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.
 
D

Doctor

This SQL provides everything I need:
SELECT qryLLCSessionInfo.LLID, qryLLCSessionInfo.LLCWorkshopNumSequence,
qryLLCSessionInfo.LLCWorkshopNum, qryLLCSessionInfo.Room,
tblLLCSessionGenre.LLCSessionGenre
FROM qryLLCSessionInfo INNER JOIN (tblLLCSessionGenre INNER JOIN
tblLLCSessionGenreLink ON tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID) ON qryLLCSessionInfo.TrainingID =
tblLLCSessionGenreLink.TrainingID;

LLID and LLCWorkshopNumSequence are for sorting in the final query and don't
need to show. Room should be the column header. WorkshopNum should be the row
header. Then I want what could be any number of LLCSessionGenre to be in the
values. Instead of showing the training name in the value, I would like to
show however many genres represent that training topic. But up until now, I
could only get one to show. Thanks.

KARL DEWEY said:
By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.

--
Build a little, test a little.


Doctor said:
How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
Click to expand...
Click to expand...
 
D

Doctor

Sorry...didn't add what I want it to look like.

Rm1 Rm2 Rm3
Session 1 Gen 1, Gen 4 Gen 3 Gen 1, Gen 5
Session 2 Gen 2, Gen 4 Gen 1, Gen 6 Gen 4
Session 3 Gen 3 Gen 2 Gen 3

KARL DEWEY said:
By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.

--
Build a little, test a little.


Doctor said:
How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
Click to expand...
Click to expand...
 
K

KARL DEWEY

I forgot to ask for sample data from the tables.

--
Build a little, test a little.


Doctor said:
Sorry...didn't add what I want it to look like.

Rm1 Rm2 Rm3
Session 1 Gen 1, Gen 4 Gen 3 Gen 1, Gen 5
Session 2 Gen 2, Gen 4 Gen 1, Gen 6 Gen 4
Session 3 Gen 3 Gen 2 Gen 3

KARL DEWEY said:
By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.

--
Build a little, test a little.


Doctor said:
How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
Click to expand...
Click to expand...
Click to expand...
 
D

Doctor

Here are the tables. Most of these tables just have number fields, except for
the obvious names. A sample TrainingName might be: Helping Kids Through Tough
Times. Sample GenreName might be: Working with Kids, or Working with Teens.
Sample WorkshopName might be: Wk1 or WK2. Sample Room might be: BB212 or
FBC120.

Is this what you are looking for?

tblTrainings
TrainingID
TrainingName

tlkpGenre
GenreID
GenreName

tblGenreLink
GenreID
TrainingID

tblSessions
SessionID
TrainingID
WorkshopID
RoomID

tblWorkshop
WorkshopID
WorkshopName
WorkshopDate
WorkshopTime

tlkpFBCRooms
RoomID
RoomName

KARL DEWEY said:
I forgot to ask for sample data from the tables.

--
Build a little, test a little.


Doctor said:
Sorry...didn't add what I want it to look like.

Rm1 Rm2 Rm3
Session 1 Gen 1, Gen 4 Gen 3 Gen 1, Gen 5
Session 2 Gen 2, Gen 4 Gen 1, Gen 6 Gen 4
Session 3 Gen 3 Gen 2 Gen 3

KARL DEWEY said:
By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.

--
Build a little, test a little.


:

How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

To get any number of comma delimited (or other) values into the crosstab, you
can use the generic Concatenate() function found at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

--
Duane Hookom
Microsoft Access MVP


Doctor said:
Here are the tables. Most of these tables just have number fields, except for
the obvious names. A sample TrainingName might be: Helping Kids Through Tough
Times. Sample GenreName might be: Working with Kids, or Working with Teens.
Sample WorkshopName might be: Wk1 or WK2. Sample Room might be: BB212 or
FBC120.

Is this what you are looking for?

tblTrainings
TrainingID
TrainingName

tlkpGenre
GenreID
GenreName

tblGenreLink
GenreID
TrainingID

tblSessions
SessionID
TrainingID
WorkshopID
RoomID

tblWorkshop
WorkshopID
WorkshopName
WorkshopDate
WorkshopTime

tlkpFBCRooms
RoomID
RoomName

KARL DEWEY said:
I forgot to ask for sample data from the tables.

--
Build a little, test a little.


Doctor said:
Sorry...didn't add what I want it to look like.

Rm1 Rm2 Rm3
Session 1 Gen 1, Gen 4 Gen 3 Gen 1, Gen 5
Session 2 Gen 2, Gen 4 Gen 1, Gen 6 Gen 4
Session 3 Gen 3 Gen 2 Gen 3

:

By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.

--
Build a little, test a little.


:

How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
Click to expand...
Click to expand...
Click to expand...
 
D

Doctor

Yes, that will work. NICE.

Duane Hookom said:
To get any number of comma delimited (or other) values into the crosstab, you
can use the generic Concatenate() function found at
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

--
Duane Hookom
Microsoft Access MVP


Doctor said:
Here are the tables. Most of these tables just have number fields, except for
the obvious names. A sample TrainingName might be: Helping Kids Through Tough
Times. Sample GenreName might be: Working with Kids, or Working with Teens.
Sample WorkshopName might be: Wk1 or WK2. Sample Room might be: BB212 or
FBC120.

Is this what you are looking for?

tblTrainings
TrainingID
TrainingName

tlkpGenre
GenreID
GenreName

tblGenreLink
GenreID
TrainingID

tblSessions
SessionID
TrainingID
WorkshopID
RoomID

tblWorkshop
WorkshopID
WorkshopName
WorkshopDate
WorkshopTime

tlkpFBCRooms
RoomID
RoomName

KARL DEWEY said:
I forgot to ask for sample data from the tables.

--
Build a little, test a little.


:

Sorry...didn't add what I want it to look like.

Rm1 Rm2 Rm3
Session 1 Gen 1, Gen 4 Gen 3 Gen 1, Gen 5
Session 2 Gen 2, Gen 4 Gen 1, Gen 6 Gen 4
Session 3 Gen 3 Gen 2 Gen 3

:

By the titles you posted -- qryLLCScheduleMatrix, qryLLCScheduleMatrix, and
qryLLCSessionInfo you are using other queries between the tables and these
queries. Why is that?

Try building a select query directly from the tables that will pull the data
to be outputted in a cross tab query but not necessarily in final format.
Post the SQL of that query and example of what the crosstab output should
look like.

--
Build a little, test a little.


:

How do I put multiple values into the value portion of a crosstab query? Or
perhaps a more appropriate question would be how do I represent the data in a
crosstab query from tables based on a many to many relationship?

My tables of importance to this question are:
tblTraining: TrainingID, TrainingName, etc.
tblGenre: GenreID, Genre
tblGenreLink: TrainingID, GenreID

I am trying to put together a crosstab query that shows me a session time
slot in the row header, a room number in the column header and a list of
genres represented by the underlying trainings being taught in those time
slots. I have already built a query that shows me the training name in this
way (SQL below). But now I want to see the many genres for each Training in
the Values portion of each row.

Crosstab for Training name:
TRANSFORM First(qryLLCScheduleMatrix.[InfoShort]) AS FirstOfInfoShort
SELECT qryLLCScheduleMatrix.LLID, qryLLCScheduleMatrix.[LLCWorkshopNum],
Count(qryLLCScheduleMatrix.Speaker) AS Sessions
FROM qryLLCScheduleMatrix
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
qryLLCScheduleMatrix.[LLCWorkshopNum]
ORDER BY qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.[Room];

Best Attempt for what I want:
TRANSFORM First([qryLLCSessionInfo].[LLCWorkshopNum] & " (" & [InfoShort] &
")") AS Expr1
SELECT tblLLCSessionGenre.LLCSessionGenre
FROM tblLLCSessionGenre INNER JOIN ((qryLLCSessionInfo INNER JOIN
qryLLCScheduleMatrix ON qryLLCSessionInfo.LLCSessionID =
qryLLCScheduleMatrix.LLCSessionID) INNER JOIN tblLLCSessionGenreLink ON
qryLLCSessionInfo.TrainingID = tblLLCSessionGenreLink.TrainingID) ON
tblLLCSessionGenre.LLCSessionGenreID =
tblLLCSessionGenreLink.LLCSessionGenreID
GROUP BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence,
tblLLCSessionGenre.LLCSessionGenre
ORDER BY qryLLCScheduleMatrix.LLID,
qryLLCScheduleMatrix.LLCWorkshopNumSequence
PIVOT qryLLCScheduleMatrix.Room;

This doesn't work becuase it only shows the first training name in each
category.

Thanks in advance for any help on such a loaded question.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL query with Many-To-Many relationship??? 2
Query with 3 tables with 1 to many relationship 7
HELP!! Jet Database Engine Error on Crosstab Query 4
Jet Database Engine Error on Crosstab Query 1
List Box to create a Report based on crosstab query 6
Dynamic crosstab query with multiple values 3

Top