Re: Order By question

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Dmitriy Antonov" <antonovdima@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:eKuoLLg$GHA.2128@xxxxxxxxxxxxxxxxxxxxxxx
another way of saying the same thing...via very "pseudo" code
If F1 = '1n' Or F1 = '4B' then
'Order By F2'
Else
...some sql to produce the posted results for '2L' and '3R'
For Each Distinct F2
'Order By F1
Next F2
End If


Try this query:

Select F1, F2
From (
Select F1, F2,
IIf(F1='1n', 1, IIf(F1='4b',3,2)) As Ord1,
IIf(F1='1n' or F1='4b', F1, 100-Val(F2)) As Ord2
From TableName
)
Order By Ord1, Ord2, F1, F2 Desc

Dmitriy.



Hi Dmitriy,
I'm trying to understand your idea....
can you help?
in this line
IIf(F1='1n', 1, IIf(F1='4b',3,2)) As Ord1,
it looks as if you're specifying a new field name for the first select
If F1 = '1n' then newfieldname = 1
ElseIf F1 = '4b' then new field name = 3
Else new field name = 2

is that correct?

I'm confused since i don't have fields by that name how that's supposed to
work

then in the second iif
IIf(F1='1n' or F1='4b', F1, 100-Val(F2)) As Ord2
we get either a field name 'F1' or a value of 100 minus the value in F2...

Sorry but I'm just not getting it.

can you elaborate on your process?

Thanks
Mark



.

Relevant Pages

  • Re: Query result wont display negative currency.
    ... with a minus sign in front of the number. ... Both the table and query are formatted for this field to be ... Can you post the SQL of your query? ... Carl Rapson ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Populating a list -- learning Access
    ... It is kinda funny -- I started using databases in the early 80's with dBase and, for years, never knew that I knew SQL! ... If you are on a form or report, the most important property is the NAME, because that is how you refer to it in code. ... I don't mean to skip your responce below, but thanks to your help with SQL, I was able to get the query to pull the info the way I needed. ... Queries (just shows the QBE grid for convenience -- ...
    (microsoft.public.access.forms)