How to Combine and sort columns? MySQL, CF8, MS Access 2003

Question

I want to produce an alphabetized list of names produced by adding together two columns of names, columnA, and columnB.

I have tried this:

<cfquery name="listAuthors" datasource="hhLibrary">
SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
FROM books
WHERE ColumnB LIKE '#firstletter#%'
ORDER BY ColumnB
</cfquery>

this is the error code: Too few parameters. Expected 1.

any help greatly appreciated.

oregonHoney

Answer 1

Update:

select *
from (
    SELECT title, a1_Lname as Name
    FROM books  
    WHERE a1_Lname LIKE '#firstletter#%' 
    UNION ALL
    SELECT title, a2_Lname as Name
    FROM books  
    WHERE a2_Lname LIKE '#firstletter#%' 
) a
ORDER BY Name

Answer 2

In Jet/ACE SQL you can't place a WHERE clause or ORDER BY on a field-name alias -- you have to repeat the expression the alias is referring to. So, replace this:

  SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
  FROM books
  WHERE ColumnB LIKE '#firstletter#%'
  ORDER BY ColumnB

...with this:

  SELECT title, (a1_Fname + a2_Fname) AS ColumnA, (a1_Lname + a2_Lname) AS ColumnB
  FROM books
  WHERE a1_Lname + a2_Lname LIKE '#firstletter#%'
  ORDER BY a1_Lname + a2_Lname

If you have Access installed, I strongly encourage you to test your SQL in interactive Access, in the QBE. You would quickly have discovered that this is the case if you'd just tried it within Access.