Order by 4 columns at once

  • Thread starter Thread starter Michael Hetrick
  • Start date Start date
M

Michael Hetrick

I need to generate a report from a table that may or may not have data in 4
columns and order by those 4 columns at once.

ID | Col1 | Col 2 | Col3 | Col4 | Title
1 | 4 | | | | some text
2 | | 2 | | | some text
3 | | | | | some text
4 | 9 | | | | some text
5 | | | 7 | | some text

The report should look like this:

Col | Title
2 | some text
4 | some text
7 | some text
9 | some text

I know that this would be easier in a relational table structure, but it
isn't an option to design the tables correctly at this time. The final
output will be displayed in a web page. Any assistance would be
appreciated!

Thanks -
Michael
 
Dear Michael:

First, I suggest you construct a query with a new column that gives
the value on which you would sort. Depending on your need, this might
be:

Nz(Nz(Nz(Nz(Col1, Col2), Col3), Col4), 0)

Meaning use Col1 unless it is null, in which case, use Col2 unless
that is null, etc. Use 0 if all are null.

By making this a column first, before trying to sort on it you can see
that it is doing what you really want. Then turn off displaying this
column and sort on it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Hi Michael,

How are things going?
You could try to write the query like this:
SELECT Nz(Nz(Nz(Nz([Col1],[Col2]),[Col3]),[Col4]),0) AS Expr1, Table1.title
FROM Table1
ORDER BY Nz(Nz(Nz(Nz([Col1],[Col2]),[Col3]),[Col4]),0);

and then use the query to support your report, or even web pages.

I would appreciate it if you could post here to let me know the status of
the issue. If you have any questions or concerns, please don't hesitate to
let me know. I look forward to hearing from you, and I am happy to be of
assistance. Please feel free to post in the group if this solves your
problem or if you would like further help. We are here to be of assistance!


Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
 
Back
Top