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 [tblItemsAndAuthors].[ItemID], LTrim([tblAuthorsEtc.LastNameNonSort] & " " & [tblAuthorsEtc.LastName]) AS FullLastName, [tblItemsAndAuthors].[Weighting]
FROM tblAuthorsEtc INNER JOIN tblItemsAndAuthors ON [tblAuthorsEtc].[AuthorID]=[tblItemsAndAuthors].[AuthorID]
WHERE ((([tblItemsAndAuthors].[AuthorRole]) In (3,4,6,7)))
ORDER BY [tblItemsAndAuthors].[ItemID], [tblItemsAndAuthors].[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. "FullLastName" 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 tblItemsAndAuthors
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 tblItemsAndAuthors 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,Blyton,Tolkien (each having an incremented id number automatically and into the tblItemsAndAuthors 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 qryAuthorsAndItems
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 qryAuthorsAndItemsExtended
11) Now run the query qryAuthorsAndItemsExtended and you should see four columns of data itemid,Author_1,Author_2,Author_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 qryAuthorsAndItemsExtended 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
3
