'Pivoting' without aggregating - is this possible?

[FiveFootUnder]

Hi. Ow. I have a bruised forehead from banging my head against a brick wall and would really appreciate some help here. As long as it's not a suggestion that I shouldn't be trying this!!

I'm working in Access 2000. I have a recordset resulting from a query consisting of three columns:

ItemID (numeric)
Author (text)
Weighting (numeric: 1, 2, 3 etc - unique values)

Some Items have several authors and so several rows in the recordset. Weighting for each author is unique within these rows.

I want to produce a recordset where I have the Item on the same row as (up to three of) its authors, ie column heads like this:

ItemID
Author_1
Author_2
Author_3

The idea is that the author with weighting of 1 goes in Author_1, the author with weighting of 2 goes in Author-2, and so on.

Pivoting (crosstab query) requires me to do some aggregate sort of function, and I can't see how this will help. I have been tinkering with CASE WHEN in SQL view in a query, but don't get anywhere with this since Access responds with error messages (syntax error, operator missing) no matter what syntax I use based on examples taken from the web.

Any ideas? And also, what syntax should you use for CASE WHEN in SQL in Access queries?
Many thanks in advance!

[pbmods]

Heya, FiveFootUnder. Welcome to TSDN!

I'm going to go ahead and move this thread to the Access forum, where our resident Experts will be better able to help you out.

[Jim Doherty]

Hi. Ow. I have a bruised forehead from banging my head against a brick wall and would really appreciate some help here. As long as it's not a suggestion that I shouldn't be trying this!!

I'm working in Access 2000. I have a recordset resulting from a query consisting of three columns:

ItemID (numeric)
Author (text)
Weighting (numeric: 1, 2, 3 etc - unique values)

Some Items have several authors and so several rows in the recordset. Weighting for each author is unique within these rows.

I want to produce a recordset where I have the Item on the same row as (up to three of) its authors, ie column heads like this:

ItemID
Author_1
Author_2
Author_3

The idea is that the author with weighting of 1 goes in Author_1, the author with weighting of 2 goes in Author-2, and so on.

Pivoting (crosstab query) requires me to do some aggregate sort of function, and I can't see how this will help. I have been tinkering with CASE WHEN in SQL view in a query, but don't get anywhere with this since Access responds with error messages (syntax error, operator missing) no matter what syntax I use based on examples taken from the web.

Any ideas? And also, what syntax should you use for CASE WHEN in SQL in Access queries?
Many thanks in advance!

Here something to ease the bruising :)

This is based on a table called tblAuthor as I did not know your table name.

Paste the into the SQL window for a query and switch to design
you will see the the table in design is shown as Author thats because I've Aliased it (click the table right mouse click properties value is in there for you to edit if you wish or edit the SQL ..up to you)

The three columns you required are made up of SQL statements as you will see that call on tblAuthors (note not author!) retrieving the author name corresponding to each item and where the weighting is as you required (3 columns 1 value for each ie: 1,2,3) each of these SQL statements reference the itemid in the aliased author table per row. Given you said the weighting was unique I am assuming you meant as a 'composite' key

SELECT Authors.ItemID, (SELECT Author FROM tblAuthor WHERE ItemID=Authors.ItemID AND Weighting=1) AS Author_1, (SELECT Author FROM tblAuthor WHERE ItemID=Authors.ItemID AND Weighting=2) AS Author_2, (SELECT Author FROM tblAuthor WHERE ItemID=Authors.ItemID AND Weighting=3) AS Author_3
FROM tblAuthor AS Authors;

Hope this helps you

Regards

Jim

[FiveFootUnder]

Hi Jim, and thanks.

Have had a go at this, and it's not flying for me at all. Even following the alias you have put on the table with manic faithfulness, all I get is gibberish or error messages.

The three fields I'm dealing with - ItemID, Author and Weighting - all come from a query called qry. I've tried plugging my query name and its alias into your SQL, but as I said, without success. So, just to recap, this is a query based on a recordset resulting from a query called "qry".

Could I try your patience by asking you to edit the query you have here to reflect my situation?

Many thanks!

[Jim Doherty]

Hi Jim, and thanks.

Have had a go at this, and it's not flying for me at all. Even following the alias you have put on the table with manic faithfulness, all I get is gibberish or error messages.

The three fields I'm dealing with - ItemID, Author and Weighting - all come from a query called qry. I've tried plugging my query name and its alias into your SQL, but as I said, without success. So, just to recap, this is a query based on a recordset resulting from a query called "qry".

Could I try your patience by asking you to edit the query you have here to reflect my situation?

Many thanks!

OK post the field names and the actual table name not the query because I'll rebuild it and post it back.

I was working on the assumption of a table called tblAuthors and made correlated queries based on that by referencing itemid from the same table and aliasing it. It works here but from what you say I suspect your qry might read completely different I don't know, so post back like I say your actual table name and column names and SQL syntax of your current query and I'll deal with it

Jim :)

[FiveFootUnder]

Thanks, Jim. Appreciate your continued patience.

Here is the SQL view of the query that produces the result set I'm trying to reorder with the query we're working on here:

SELECT [tblItemsAndAuth ors].[ItemID], LTrim([tblAuthorsEtc.L astNameNonSort] & " " & [tblAuthorsEtc.L astName]) AS FullLastName, [tblItemsAndAuth ors].[Weighting]
FROM tblAuthorsEtc INNER JOIN tblItemsAndAuth ors ON [tblAuthorsEtc].[AuthorID]=[tblItemsAndAuth ors].[AuthorID]
WHERE ((([tblItemsAndAuth ors].[AuthorRole]) In (3,4,6,7)))
ORDER BY [tblItemsAndAuth ors].[ItemID], [tblItemsAndAuth ors].[Weighting];

-- it's straight out of the Access SQL view window for the query, hence all the square and round brackets that Access fills things with. "FullLastNa me" is what I refer to as "Author" in my earlier posts.

I'd be really grateful for any help you can give here.

[Jim Doherty]

Thanks, Jim. Appreciate your continued patience.

Here is the SQL view of the query that produces the result set I'm trying to reorder with the query we're working on here:

SELECT [tblItemsAndAuth ors].[ItemID], LTrim([tblAuthorsEtc.L astNameNonSort] & " " & [tblAuthorsEtc.L astName]) AS FullLastName, [tblItemsAndAuth ors].[Weighting]
FROM tblAuthorsEtc INNER JOIN tblItemsAndAuth ors ON [tblAuthorsEtc].[AuthorID]=[tblItemsAndAuth ors].[AuthorID]
WHERE ((([tblItemsAndAuth ors].[AuthorRole]) In (3,4,6,7)))
ORDER BY [tblItemsAndAuth ors].[ItemID], [tblItemsAndAuth ors].[Weighting];

-- it's straight out of the Access SQL view window for the query, hence all the square and round brackets that Access fills things with. "FullLastNa me" is what I refer to as "Author" in my earlier posts.

I'd be really grateful for any help you can give here.

OK well the SQL syntax you have posted there doesnt work even when I replicate the table structure exactly from the SQL as defined. The reason for that is the incorrect useage of the square bracketing as you posted.

I am suspecting that you were editing the SQL immediately before posting it, because Access would not have been able to generate that as is, instead it would have thrown you an error 'before' ever showing it to you in the SQL window and refused to go into the SQL window...BUT no matter here goes...

On your first post WEIGHTING was the prioritising factor in that you mentioned the figures 1,2,3 as setting the agenda for each author by item. If the item was shown as the numeric figure 1 then that should take up the name of a column called Author_1. and sit immediately to the right of a very first column called Item ID in any display..... thereby visually becoming column 2 in any display)

Similarly if the 'Weighting' shows a figure of 2 then that should take a name of Author_2. and sit immediately to the right of Author_1 (column 2) taking thus the position of column 3 in the display.

Similarly if the 'Weighting' shows a figure of 3 then that should take a name of Author_3. and sit immediately to the right of Author_2 (column 3) taking thus the position of column 4 in the display.


I know the above sounds confusing, but I,m sure as you break it down you will understand me. particularly as you build what it is I am going to ask you to build
the purpose being, to assist you to fully understand how we arrive at what it is we will arrive at. In so doing we shall do this step by step so follow it as you say 'manically' hahaha ok?

Now I am assuming that you are learning SQL and maybe Access and that you have a database there you are learning from. Either way the example I will illustrate which by the way, is in accordance with your field names above relies on specifics. In other words what I dont' want is to conflict with any current objects in your current database which might throw out the demo ok?

So..... maybe you should create a new database for the purposes of this and follow what it is I am about to relate to you (you can always then apply that logic to your current database:

From your posted SQL I have made certain assumptions causing me to arrive at the following

1) In your new database create a table called tblAuthorsEtc

(I have to say the 'etc' bit on the end I think must have been you practicing seems rather unusual name to me but anyway)

2) Create the following fields in that table

AuthorID with a datatype of (autonumber) and set as the Primary key
LastName with a datatype of Text (fieldsize as you wish)
LastNameNonSort with a datatype of Text (Fieldsize as you wish)

(I have no idea what you intention was with the Fieldname of LastNameNonSort I am assuming it was some sort of independant idea you had for sorting the surname or something. and as you will see the only use it has... in reality.. is that it is part of the trimmed concatenation which you seemed to be specific about in arriving at your column.)

3) Create a table called tblItemsAndAuth ors

4) Create the following fields in that table

ItemID with a datatype of (number - fieldsize long integer)
AuthorID with a datatype of (number - fieldsize long integer)
Weighting with a datatype of (number - fieldsize as you wish integer?)
AuthorRole with a datatype of (number - fieldsize as you wish integer?)

Once again here the field 'AuthorRole' is pretty much redundant for the purposes of this because we are using WEIGHTING to set the Agenda NOT AuthorRole. There is a contradiction it seems to me in your intitial post and your posted SQL) but no matter we will get there in the end.

5) In table design view for tblItemsAndAuth ors select itemid,authorid ,and Weighting and set all three together as a COMPOSITE primary key in other words once you have all three selected hit the KEY button on the toolbar.
What this goes to serve is that a data value 'must' exist in all three columns in order to enter a record and in addition to that 'no record' of all three values when taken together as a unit can be duplicated.

6) Now populate those tables with data of your choosing

(At my end I have inserted into the tblAuthorsEtc table three records as Shakespeare,Bly ton,Tolkien (each having an incremented id number automatically and into the tblItemsAndAuth ors table relvant data to suit NOTE place any weighting data ie numeric values of 1 or 2 or 3 or 4 or 5 etc into the weighting field not the AuthorRole field remember Weighting is setting the agenda on returning records ultimately)

7) Create a query and in the SQL Window paste the following SQL

SELECT tblItemsAndAuthors.ItemID, LTrim([tblAuthorsEtc].[LastNameNonSort] & " " & [tblAuthorsEtc].[LastName]) AS FullLastName, tblItemsAndAuthors.Weighting
FROM tblAuthorsEtc INNER JOIN tblItemsAndAuthors ON tblAuthorsEtc.AuthorID = tblItemsAndAuthors.AuthorID
WHERE (((tblItemsAndAuthors.Weighting) In (1,2,3,4)))
ORDER BY tblItemsAndAuthors.ItemID, tblItemsAndAuthors.Weighting;

8) Save that query as qryAuthorsAndIt ems

9) Create a query and in the SQL Window paste the following SQL

SELECT DISTINCT Authors.ItemID, (SELECT FullLastName FROM qryAuthorsAndItems WHERE ItemID=Authors.ItemID AND Weighting=1) AS Author_1, (SELECT FullLastName FROM qryAuthorsAndItems WHERE ItemID=Authors.ItemID AND Weighting=2) AS Author_2, (SELECT FullLastName FROM qryAuthorsAndItems WHERE ItemID=Authors.ItemID AND Weighting=3) AS Author_3
FROM qryAuthorsAndItems AS Authors;

10) Save that query as qryAuthorsAndIt emsExtended


11) Now run the query qryAuthorsAndIt emsExtended and you should see four columns of data itemid,Author_1 ,Author_2,Autho r_3 as you originally required
Each of those (author) columns containing the name of the author who has a weighting value of 1 or 2 or 3. To see how this is arrived at look at the qryAuthorsAndIt emsExtended in design and you will see those columns as essentially subselects where the comparison is made between the qryAuthorsEtc query and ITSELF (qryAuthorsEtc) BUT ALIASED to AUTHORS and where the weighting value determines the column placement

I hope this makes overall sense to you thus far

Regards

Jim

[Jim Doherty]

OK well the SQL syntax you have posted there doesnt work even when I replicate the table structure exactly from the SQL as defined. The reason for that is the incorrect useage of the square bracketing as you posted.

I am suspecting that you were editing the SQL immediately before posting it, because Access would not have been able to generate that as is, instead it would have thrown you an error 'before' ever showing it to you in the SQL window and refused to go into the SQL window...BUT no matter here goes...

On your first post WEIGHTING was the prioritising factor in that you mentioned the figures 1,2,3 as setting the agenda for each author by item. If the item was shown as the numeric figure 1 then that should take up the name of a column called Author_1. and sit immediately to the right of a very first column called Item ID in any display..... thereby visually becoming column 2 in any display)

Similarly if the 'Weighting' shows a figure of 2 then that should take a name of Author_2. and sit immediately to the right of Author_1 (column 2) taking thus the position of column 3 in the display.

Similarly if the 'Weighting' shows a figure of 3 then that should take a name of Author_3. and sit immediately to the right of Author_2 (column 3) taking thus the position of column 4 in the display.


I know the above sounds confusing, but I,m sure as you break it down you will understand me. particularly as you build what it is I am going to ask you to build
the purpose being, to assist you to fully understand how we arrive at what it is we will arrive at. In so doing we shall do this step by step so follow it as you say 'manically' hahaha ok?

Now I am assuming that you are learning SQL and maybe Access and that you have a database there you are learning from. Either way the example I will illustrate which by the way, is in accordance with your field names above relies on specifics. In other words what I dont' want is to conflict with any current objects in your current database which might throw out the demo ok?

So..... maybe you should create a new database for the purposes of this and follow what it is I am about to relate to you (you can always then apply that logic to your current database:

From your posted SQL I have made certain assumptions causing me to arrive at the following

1) In your new database create a table called tblAuthorsEtc

(I have to say the 'etc' bit on the end I think must have been you practicing seems rather unusual name to me but anyway)

2) Create the following fields in that table

AuthorID with a datatype of (autonumber) and set as the Primary key
LastName with a datatype of Text (fieldsize as you wish)
LastNameNonSort with a datatype of Text (Fieldsize as you wish)

(I have no idea what you intention was with the Fieldname of LastNameNonSort I am assuming it was some sort of independant idea you had for sorting the surname or something. and as you will see the only use it has... in reality.. is that it is part of the trimmed concatenation which you seemed to be specific about in arriving at your column.)

3) Create a table called tblItemsAndAuth ors

4) Create the following fields in that table

ItemID with a datatype of (number - fieldsize long integer)
AuthorID with a datatype of (number - fieldsize long integer)
Weighting with a datatype of (number - fieldsize as you wish integer?)
AuthorRole with a datatype of (number - fieldsize as you wish integer?)

Once again here the field 'AuthorRole' is pretty much redundant for the purposes of this because we are using WEIGHTING to set the Agenda NOT AuthorRole. There is a contradiction it seems to me in your intitial post and your posted SQL) but no matter we will get there in the end.

5) In table design view for tblItemsAndAuth ors select itemid,authorid ,and Weighting and set all three together as a COMPOSITE primary key in other words once you have all three selected hit the KEY button on the toolbar.
What this goes to serve is that a data value 'must' exist in all three columns in order to enter a record and in addition to that 'no record' of all three values when taken together as a unit can be duplicated.

6) Now populate those tables with data of your choosing

(At my end I have inserted into the tblAuthorsEtc table three records as Shakespeare,Bly ton,Tolkien (each having an incremented id number automatically and into the tblItemsAndAuth ors table relvant data to suit NOTE place any weighting data ie numeric values of 1 or 2 or 3 or 4 or 5 etc into the weighting field not the AuthorRole field remember Weighting is setting the agenda on returning records ultimately)

7) Create a query and in the SQL Window paste the following SQL

SELECT tblItemsAndAuthors.ItemID, LTrim([tblAuthorsEtc].[LastNameNonSort] & " " & [tblAuthorsEtc].[LastName]) AS FullLastName, tblItemsAndAuthors.Weighting
FROM tblAuthorsEtc INNER JOIN tblItemsAndAuthors ON tblAuthorsEtc.AuthorID = tblItemsAndAuthors.AuthorID
WHERE (((tblItemsAndAuthors.Weighting) In (1,2,3,4)))
ORDER BY tblItemsAndAuthors.ItemID, tblItemsAndAuthors.Weighting;

8) Save that query as qryAuthorsAndIt ems

9) Create a query and in the SQL Window paste the following SQL

SELECT DISTINCT Authors.ItemID, (SELECT FullLastName FROM qryAuthorsAndItems WHERE ItemID=Authors.ItemID AND Weighting=1) AS Author_1, (SELECT FullLastName FROM qryAuthorsAndItems WHERE ItemID=Authors.ItemID AND Weighting=2) AS Author_2, (SELECT FullLastName FROM qryAuthorsAndItems WHERE ItemID=Authors.ItemID AND Weighting=3) AS Author_3
FROM qryAuthorsAndItems AS Authors;

10) Save that query as qryAuthorsAndIt emsExtended


11) Now run the query qryAuthorsAndIt emsExtended and you should see four columns of data itemid,Author_1 ,Author_2,Autho r_3 as you originally required
Each of those (author) columns containing the name of the author who has a weighting value of 1 or 2 or 3. To see how this is arrived at look at the qryAuthorsAndIt emsExtended in design and you will see those columns as essentially subselects where the comparison is made between the qryAuthorsEtc query and ITSELF (qryAuthorsEtc) BUT ALIASED to AUTHORS and where the weighting value determines the column placement

I hope this makes overall sense to you thus far

Regards

Jim

As a PS I am on vacation as of today (off to sunny cyprus) so if you don't get a resolution you can PM me and I'll continue on return next week?

Jim