Ordering by specific field values with MSAccess

Question

Dear All,

I would like to order one field by using a list of values.

If Use the below query

SELECT [History Date], [FieldName], [Message], [Performed by] FROM [HISTORY_TABLE] WHERE (((ID_Request)=571)) ORDER BY [ID_History] 

I get this result:

but I would like to have this:

So, I'm try to find a way to apply two filter:

1. ORDER BY [ID_History]
2. ORDER BY [FieldName] but sorted in this way ('Date Created', 'Part Number', User Modified', 'Solution', 'Status')

Thanks

Answer 1

SELECT * FROM product WHERE pro_id IN (5,3,6,1)

ORDER BY INSTR('5,3,6,1',pro_id)

ORDER BY instr("Date Created,Part Numb",fieldName)

use instr to order by specific words

Answer 2

With your table as it stands you would need to create a referenced table with columns FieldName and FieldOrder.  The FieldName column would be the primary key and the FieldOrder column would contain integer values indicating the ordinal position of each FieldName value.  You could then join the two tables on FieldName and sort the query with:

    ORDER BY  ID_History, FieldOrder

This would of course require the ID_History column to have the same value in each subset of rows within which you wish to return the rows by FieldOrder value.

However, the need to do this results from the incorrect table design.  The FieldName column contains the names of attribute types, whereas a fundamental principle of the database relational model is that each column position in a row in a table should contain an attribute value.  The table should therefore be radically amended so that each of the distinct values in the FieldName column is a column name, and each of the corresponding values in the Message column is a value in the relevant column.  You can then return the columns in whatever order you wish.