How to sort a query in access?

Question

0.00/5 (No votes)

See more:

Hi.
I have an access database with two tables, a Client table and then a table for reports.
What I am trying to do is to generate a report using Crystal report in vb to list all the clients. But i want the user to specify if the clients sould be sorted by surname, name, age, gender or address. Once the selection is made it writes in the report table by what field to sort. This is then send to the query where it specifies witch field to sort by.

Here is the example of the sql query:

SQL

SELECT Clients.[Client Name], Clients.Surname, Clients.Address, Clients.[Tel Nr], Reports.SortBy
FROM Clients, Reports
ORDER BY iif Reports.SortBy = Name then 1,
Else Reports.SortBy = Surname then 2,
Else 3
End;

How do you do this or is there a better way of doing it?

What I have tried:

I have tried the case statement also.

Posted 24-Feb-22 2:18am

Member 14131979

Updated 24-Feb-22 9:49am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 24-Feb-22 12:11pm

Are you sure you want to join data from Reports table using cross join? Note: cross join returns the Cartesian product of rows from tables in the join. This will produce rows which combine each row from the first table with each row from the second table.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2022-02-24T06:39:00

Solution 1

Regarding my comment on your question, imagine this:

Table Name | Content
--------------------
Table A    | 1, 2
Table B    | A, B

Result of cross join is:

A | B
-----
1 | A
1 | B
2 | A
2 | B

You should use table aliases. See:

SQL

SELECT C.[Client Name], C.Surname, C.Address, C.[Tel Nr]
FROM Clients AS C;

When you explain the relationship between Clients and Reports table, i'll improve my answer.

Posted 24-Feb-22 6:39am

Maciej Los

Comments

Member 14131979 25-Feb-22 8:12am

There is not a relation between the two tables. I am only using the reports table to tell the query what field to sort by. In the VB app when the user selects the report and sort by field it gets written to the reports table and then the when the crystal report open it calls the querry in access.

Maciej Los 25-Feb-22 16:08pm

In this case, i'd suggest to read this: Controlling the sort using a Parameter Prompt in Crystal Reports[^] and this: https://thorkolner.files.wordpress.com/2017/05/dynamically-changing-the-sort-order-in-crystal-reports1.pdf[^]

Andre Oosthuizen · Answer 2 · 2022-02-24T09:49:00

Solution 2

You are only showing the SQL here. How do users select the column to sort by, from access or an application?

Irrespective, each selection will give you a value to sort by. Only select the selection and then run your query accordingly.

Posted 24-Feb-22 9:49am

Andre Oosthuizen