Link to home
Start Free TrialLog in
Avatar of GlobexCorp
GlobexCorp

asked on

Microsoft Access Query Problem: Order by mutiple values on a particular column

Hi I'm trying to write a query that will be ordered in two different ways. My table has a field called Category and in this field I have 6 values which are Conservation, Culture, Environment, Voluntary, Community, and disadvantaged.

I want my query to be ordered so that it firstly shows the records for community voluntary then disadvantaged  - in that order

then after that to show the records for conservation culture snd environment

At the moment as a work around I have 2 seperate queries. So the first one orders my records by Community, Disadvantaged & Voluntary, the second query orders my records by Conservation, Culture & Environment. But I would really like to be able to have all this in one query and to have it in the particular order so it's not alphabetical like in the first query. I have attached the SQL I have used for each query:

Many thanks in advance for your help.
       
SELECT [tbl Organisation].[Organisation ID], [tbl Organisation].Organisation, [tbl subcategory].subcategory, [tbl Organisation].[Main Purpose], [tbl subcategory].subcategoryid, [tbl Organisation].[Registration Form Received]
 
FROM [tbl Organisation] INNER JOIN [tbl subcategory] ON [tbl Organisation].SubCategoryID = [tbl subcategory].subcategoryid
 
WHERE ((([tbl subcategory].subcategory)="Voluntary" Or ([tbl subcategory].subcategory)="Community" Or ([tbl subcategory].subcategory)="disadvantaged") AND (([tbl Organisation].[Registration Form Received])=Yes))
 
ORDER BY [tbl subcategory].subcategory; 
 
 
 
SELECT [tbl Organisation].[Organisation ID], [tbl Organisation].Organisation, [tbl subcategory].subcategory, [tbl Organisation].[Main Purpose], [tbl subcategory].subcategoryid, [tbl Organisation].[Registration Form Received]
 
FROM [tbl Organisation] INNER JOIN [tbl subcategory] ON [tbl Organisation].SubCategoryID = [tbl subcategory].subcategoryid
 
WHERE ((([tbl subcategory].subcategory)="Conservation" Or ([tbl subcategory].subcategory)="Culture" Or ([tbl subcategory].subcategory)="Environment") AND (([tbl Organisation].[Registration Form Received])=Yes))
 
ORDER BY [tbl subcategory].subcategory;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GlobexCorp
GlobexCorp

ASKER

Thanks everybody for your help was much appreciated