How to make one row be always on top in query 3

[marijonas]

Hi, I am have query that gives results for a comparison chart.
Results of the query are two rows with brand name and then some numbers that are displayed in chart.
One row is always the same brand, lets say Head Masters and the other row is the brand that user chooses.
I would like the row with Head Masters to be always on top which is needed for comparison. However Access aplies sorting rules according to the first letter of a brand and if the second brand is Daves, it becomes the first one (on the top).

If anyone has any suggestions it would be great. I don't think I can assign numbers to those brands and use it for sorting because they would be seen in chart.

Thanx,
Marijus

 

[BNPMike]

Access returns relations which are essentially unordered. If you need to order them you must do so based on data in the relation. As it happens Access sometimes sorts things without asking and maybe that's what you are seeing eg if you display a table then you will get items in primary key order, but you should never program based on this. Move it to another relational database and it might not behave in the same way.

If you can't find existing data to order the rows then add another field. You can order a query on a field without displaying the ordering field.

Your problem however looks a little bigger as you seem to have a table and a chart, and seem to be using the table as a key to the chart. Can you clarify?

 

[marijonas]

well, I simply use the query as source for a chart on a form. It seems that I need some kind of number to be able to sort according to it.. Access uses its default sorting by abc...

 

[BNPMike]

If it's a chart, why does the ordering of the query affect it?

As I said, if you want to order things use an ORDER BY clause. You seem to be just accessing a raw table. I recommend you always use a query. And always apply your ordering. Access is relational. It might give you accidental ordering but if you use SQL, it will always do what is it supposed to do. You're the one in control. It is a faithful servant. But you have to tell it what you want.

 

[cmmrfrds]

If you have a hierarchy of brands then you probably need to put a field in the brand table to indicate the hierarchy and then order by that in the queries. If it is just 1 or 2 values you could put them in the order by clause. Something like.

Order by iif(left(brand,5) = "head",1,2), brand, etc

 

[JonFer]

Or add a field in the query to prompt for the top brand so you can tag it with a sequence number:

Select IIf(Brand=[Enter top brand],1,2) as Sequence, Brand
From MyTable
Order By IIf(Brand=[Enter top brand],1,2), Brand

 

[marijonas]

Perfect.. I made order by to look on the form for the brand that is the main brand. It works perfect. Thanks to you all!