C# How do I Order By my Transform/Pivot in Ms Acce

Question

Using this code:

TRANSFORM SUM(Amount) SELECT DISTINCT IS_Accounts FROM [TABLE] GROUP BY IS_Accounts PIVOT Status

I am capable of creating a mini-report summary for my purposes, generated from a much larger database with only the important stuff.

This is how it looks like:

However the Accounts column is all out of order, some of these need to be above the others in priority. To do this, I've written a query and tested it on the main database.

" ORDER BY IIf([IS_Accounts] = 'Revenues' , 1 , IIf([IS_Accounts] = 'Cost of Services', 2 , IIf([IS_Accounts] = 'Operating Expenses', 3, IIf([IS_Accounts] = 'Other income/expense', 4, IIf([IS_Accounts] = 'Income Taxes', 5, 6))))) ASC ");

When I run this on my main database, it properly gets sorted with revenues at the top and the rest as the pattern suggests, however plugging this to my Transform/Pivot query, I encounter a "Group by clause conflicts with Order by clause" error. If this operation is not allowed, how can I order my pivot data?

Note that I used IIF instead of a case, this is because MS Access cannot use when unlike other databases.

All I intend to do is make it so that the final rows of my pivot follow the correct pattern.

Answer 1

Construct a field in query called [Sort] (or whatever you want) with expression:

Switch([IS_Accounts] = 'Revenues', 1, [IS_Accounts] = 'Cost of Services', 2, [IS_Accounts] = 'Operating Expenses', 3, [IS_Accounts] = 'Other income/expense', 4, [IS_Accounts] = 'Income Taxes', 5, True, 6)

Now assign that field as another RowHeading in the CROSSTAB.

I presume you already have a lookup table of these budget items used as source for entry into data records. This sequence number could be another field in that lookup table. Join the table into the CROSSTAB to pull in the sequence number. Are you saving the actual descriptive text into your data records instead of a PK from the lookup table? Repeating this text will use up Access 2GB limit faster but that could still take a very long time depending on your data volume. Also, joins are supposed to perform more efficiently when done on numeric data.