February 10, 2011 at 7:54 am
Hello,
I've written this SQL , running it in Access 2003, which produces the information I need, including a footer showing the Total.
However, I would like to Sort Col1 alphabetically (From A-Z), but so far, has been unsuccessful. Below is the main pseudcode for this:
SELECT “Total“ AS Col1, “ “ AS Col2, “ “ AS Col3, Count([TableA.Column1]) AS [Count_TableA_Column1]
FROM…TableA INNER JOIN TableB INNER JOIN TableC
UNION
SELECT TableB.Col1, TableB.Col2, TableB.Col3, Count([TCPIP Table].[Term #]) AS [CountOfTerm #]
GROUP BY TableB.Col1, TableB.Col2, TableB.Col3
ORDER BY Col1, [Count_TableA_Column1]
Col1 Col2Col3 CountOfTerm#
A--5
D--4
Total11
Z--2
When I remove the "Col1" from the "Order by" in the second SELECT, I get the "Total" row pushed to the bottom, but then the Col1 is all unordered like this, but the Total appears at the bottom, as I want it.
Z 2
D 4
A 5
Total 11
Is there a way to order by Column 1 while still getting the Total Row at the bottom? Thanks for any help
February 10, 2011 at 8:04 am
I've reached a temporary solution. I added a space ( " "+ [TableB.Col1] ) to the second Select statement in the Union query, inserting a space before each cell of data. So, when it sorts, it sorts everything with the Space first, and then the "Total" which has no space before it in the first Select.
February 10, 2011 at 8:08 am
SELECT “Total“ AS Col1, “ “ AS Col2, “ “ AS Col3, Count([TableA.Column1]) AS [Count_TableA_Column1], 2 as roworder
FROM…TableA INNER JOIN TableB INNER JOIN TableC
UNION
SELECT TableB.Col1, TableB.Col2, TableB.Col3, Count([TCPIP Table].[Term #]) AS [CountOfTerm #],1 as roworder
GROUP BY TableB.Col1, TableB.Col2, TableB.Col3
ORDER BY roworder,Col1, [Count_TableA_Column1]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply