newbie to crosstabs

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Suppose I have a recordset of authors and titles:

Author, Title
=====, ========
Dickens, Christmas Carol
Shakespeare, Hamlet
Shakespeare, MacBeth
Shakespeare, Romeo and Juliet

How do I transform this to

Author, Title1, Title2, Title3
=====, ===, ====, ====
Dickens, Christmas Carol, null, null
Shakespeare, Hamlet, Macbeth, Romeo and Juliet

I don't know ahead of time the maximum number of titles of any given author.
I think this involves crosstab queries but I don't know too much about them.
Thanks,
Gary
 
Dear Gary:

I don't think a crosstab is what you're looking for. Rather, this
would use a denormalizing query (a term I may have just invented).

A crosstab would create a column for each value:

Christmas Carol
Hamlet
MacBeth
Romeo and Juliet

But you want columns labelled by title.

Also, crosstabs are generally built around aggregate functions, which
you don't need.

Now, there are some problems with what you want to do.

What would you do if there were more than 3 Titles for an Author?
What if an author has 100 books, or 1000 books?

I don't know what you intend with the commas, but they wouldn't
typically be shown.

Now, if we were to first "Rank" the titles alphabetically, perhaps
using DISTINCT to prevents duplicates from entering the list, we would
be part way there:

Dickens Christmas Carol 1
Shakespeare Hamlet 1
Shakespeare Macbeth 2
Shakespeare Romeo and Juliet 3

For this, I'm thinking of a query step like this:

SELECT Author, Title,
(SELECT COUNT(*) + 1 FROM YourTable T1
WHERE T1.Author = T.Author AND T1.Title < T.Title) AS Rank
FROM YourTable T
ORDER BY Author, Title

From this, assuming Title is unique for each Author or that we make it
unique with DISTINCT, you can construct a crosstab if that's what you
want. You could use MIN() for the aggregate simply because there
would be only one value for each title.

However, when there are a large number of titles, this will fail. You
cannot have more than 256 columns in the query, meaning 255 titles.

Just how to work around this will depend on how you are using this.
If you're using it for a report, for example, you'll run out of paper
long before you put this many columns across a page.

I'll let you digest and experiment with this for now. Get back with
more information and questions as needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Sorry to switch gears on you, but I realized what would work better for me
instead of multiple title colums would be all of the titles concatenated
into a string in one title colum. We can assume there will be no more than
20 titles per author. Using the data from earlier this would look like:

Author Titles
===== ====
Dickens "Christmas Carol"
Shakespeare "Hamlet, MacBeth, Romeo and Juliet"

So how does this change things?
 
Dear Gary:

This trashes what I was describing completely.

You don't know how many titles an author has, so you don't know how
long the appended string may get. It will almost certainly get too
long for the width of your screen, or paper. It will probably get too
long for any table column you might specify.

I think it's time for you to describe why it is you feel you need to
do this. If the concept of why to do it isn't going to finally work
no matter how hard we try, then there's no sense spending a lot of
time on it.

What you're suggesting you want to do isn't "database." Putting an
unspecified number of items into a horizontal list isn't something
databases are built to be able to do, because such an operation is
eventually impossible as it get very long anyway. Databases store
this vertically, and its best left that way.

If you have a truly valid reason as to how and why you need to do
this, and can answer for the things that tend to make it infeasable,
then maybe we'll work hard to solve it. If not, I'll try to help you
see the problems and a way to steer around this.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
I think it's time for you to describe why it is you feel you need to
do this. If the concept of why to do it isn't going to finally work
no matter how hard we try, then there's no sense spending a lot of
time on it.
Click to expand...


Tom,
Thanks, here's what I'm trying to accomplish. My database describes water
treatment plants and the treatment operations that they employ. There is a
main table of water treatment plants (Plant table). The Plant table is
linked many-many to table "Pre" which contains pretreatment operations (for
example, Dechlorination, Softening). The Plant table is also linked
many-many to table "Post" which contains posttreatment operations (e.g.
Aeration, Fluoridation).

In other words, a given water treatment plant may have several pretreatment
operations and several posttreatment operations. I want the end-user to be
able to search on pre and post treatment criteria. An example of a search
that is giving me trouble is "Select plants that use Softening in
pretreatment, but for those plants display all the pre and post operations
they use".

Here's some sample data

Plant table:
=======
PlantA Oklahoma
PlantB Missouri
PlantC Texas

Pre table:
======
PlantA Dechlorination
PlantA Softening
PlantB Softening
PlantC Dechlorination

Post table:
=======
PlantA Aeration
PlantA Fluoridation
PlantB Aeration


So the result for my problem search above would have to include PlantA,
Dechlorination, Softening, Aeration, Flouridation, and Plant B, Softening,
Aeration. I would PREFER that the user sees just one line of results per
plant so they can quickly see "oh there are two such plants". If there is
some other logical way to present this I'm open to ideas.

Thanks,
Gary
 
Hi Gary,

Thanks for your post. According to your description, I understand that you
want to filter the records in the Plant table. The conditions are in the
Post and Pre tables.

For example:
You want to get the plant, which has the following conditions:
1. "Dechlorination" in Pre table
2. "Aeration" in Post table.

So PlantA is what you are looking for. If I have misunderstood, please feel
free to let me know.

To filter the records in the plant table, I created a test on my side and
you can check to see if it meets your requirements.

Table Schema

Plant table:
=======
Field Name Data Type
Plant Text PK
Desplant Text

Pre table:
=======
Field Name Data Type
Plant Text
Despost Text

Pre table:
=======
Field Name Data Type
Plant Text
Despre Text

Use the following SQL statements to create an underlying Query called
"Plant Query"

SELECT Plant.Plant, Post.Despost, Pre.Despre
FROM (Plant INNER JOIN Post ON Plant.Plant=Post.Plant) INNER JOIN Pre ON
Plant.Plant=Pre.Plant;

Use the following parameter query to filter the records.

PARAMETERS PostCondition Text ( 255 ), PreCondition Text ( 255 ) ;
SELECT DISTINCT ([Plant Query].[Plant]) AS Plant
FROM [Plant Query]
WHERE ((([Plant Query].Despost) = [PostCondition]) AND (([Plant
Query].Despre)= [PreCondition]));

Please feel free to post in the group if this solves your problem or if you
would like further assistance.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
 
Your assumptions are incorrect. In my description I posed the following
query: "Select plants that use Softening in
pretreatment, but for those plants display all the pre and post operations
they use".
Thanks,
Gary
 
Hi Gary,

Thanks for your update and I am fully understand your concerns now. It
seems that Duane's suggestion has addressed your problem. Unfortunately,
based on my research, it seems that there is not an easy way to meet your
requirments using pure SQL statements.

Here are the articles for your reference:
Concatenate Column Values from Multiple Rows into a Single Column with
Access
http://www.databasejournal.com/features/msaccess/article.php/2247531

Please feel free to let me know if you need other concerns on this issue.

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

This document contains references to a third party World Wide Web site.
Microsoft is providing this information as a convenience to you. Microsoft
does not control these sites and has not tested any software or information
found on these sites; therefore, Microsoft cannot make any representations
regarding the quality, safety, or suitability of any software or
information found there. There are inherent dangers in the use of any
software found on the Internet, and Microsoft cautions you to make sure
that you completely understand the risk before retrieving any software from
the Internet.
 
Back
Top