Solved

Crosstab effect without summary values

Posted on 2011-09-16
5
370 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now