Solved

Crosstab effect without summary values

Posted on 2011-09-16
5
377 Views
Last Modified: 2012-05-12
How do I create a query which looks like this:

Month1   Month2   Month3
Red         Red         White
White      Blue        Blue

from a 2-field table which looks like this:

MonthName  Colour
Month1         Red
Month1         White
Month2         Red
Month2         Blue
Month3        White
Month3        Blue
0
Comment
Question by:Bellone
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36548816
1) Create a query like this, notionally named Step1:

SELECT t1.MonthName, t1.Colour, 
    (SELECT Count(t2.MonthName)
    FROM MonthColours t2
    WHERE t2.MonthName = t1.MonthName AND t2.Colour <= t1.Colour) AS RowNum
INTO CrosstabSource
FROM MonthColours t1
ORDER BY t1.MonthName, t1.Colour

Open in new window


That creates a new table.

2) Create a crosstab query to present the results:

TRANSFORM First(CrosstabSource.[Colour]) AS FirstOfColour
SELECT CrosstabSource.[RowNum]
FROM CrosstabSource
GROUP BY CrosstabSource.[RowNum]
PIVOT CrosstabSource.[MonthName];

Open in new window

0
 
LVL 5

Author Comment

by:Bellone
ID: 36549055
Hi MatthewsPatrick

Thanks very much for quick response.  Sorry to be dull but I'm not quite sure where t1 and t2 are coming from in the Step1 query, assuming my example table has been designated MonthColours

Bellone
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36549245
Since Patrick is showing you that you need a self join between MonthColours, you will need to remove ambiguity of where columns are coming from since you are guaranteed that every column has a duplicate name. Using aliases t1 and t2, SQL can understand the difference between each column in the query.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36549290
Bellone,

As mwvisa1 indicates, the t1 and t2 are simply table aliases.  Think of them as a "nickname" for the table, which allows the SQL statements to be more readable, and in some instances (as in this one) allows us to keep the logic straight.

To implement, wherever you see my table name "MonthColours", replace it with your actual table name.  Be sure to put in square brackets if your table name does not follow standard naming rules.

Patrick
0
 
LVL 5

Author Closing Comment

by:Bellone
ID: 36549441
OK, all clear now.  Many thanks to you both.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question