Sorting of numeric filed’s values is needed 2

[katrina11]

Hi experts!

I have an existing data entry application implemented in Access 2007. Whenever user selects a memberOrder , he can enter data.
The problem is that memberOrder is not sorted . Or rather sorting is not customized.
Whenever user click on memberOrder field heading, system provide a filters box where it is filtered in the following way:
1
10
11
12
13
14
15
16
17
18
19
2
20
And so on...
I found a query which is responsible for the Form but I do not know how to change it so that if give MemberOrder in the common way:
1
2
3
4
I assume "Order By" clause is needed in SQL view. However, when I added “order by memberOrder” it has no effect.

I wonder what syntax would implement what I am looking for. Could you please help me to fix that?

Thank you!

Katrin

 

[Andrzejek]

How is memberOrder field defined in your DB? I bet it is Char(string, text), not a Number. That's why you have Order like that - it IS ordered, it is just not the way you want it.

You may want to have a caluclated fiel in your SQL, something like:

Select ABC, XYZ, Format(memberOrder, "000") As MyField
From SomeTable
WHERE ...
ORDERED BY MyField

Have fun.

---- Andy

 

[katrina11]

Andrzejek, that was my first though as I imported input file from the application. At that time theer was no any sorting like:
17
13
19
12
That is why I changed character to number in the design view for the table which is a part of query. After that it started to look better (exactly as in my initial post example).

So far changing data type to numerick did not resolve the problem.
Is theer any way to implement it considering that it is numeric?

 

[katrina11]

Actually, memberOrder is not for all members. It is just for particular doctor (provider).
providerid memberOrder
FFFF1 1
FFFF1 2
FFFF1 3

HHHH1 1
HHHH1 2

and so on

I hava a feeling that it should be ordered not just by particular field memberOrder but by somewhat combined with ProviderID...

 

[katrina11]

However, providerID is a character string while memberOrder is supposed to be numeric,....

 

[Andrzejek]

Did you try my suggestion?
If you Format your field this way, you will end up with:[tt]
001
010
011
002
...[/tt]
so if you order it, even if it is a string, you will end up with:[tt]
001
002
003
009
010
011
012
...[/tt]

Have fun.

---- Andy

 

[katrina11]

1. I changed data type from character to numeric (I had to as it was required by application)
2. I made changes you suggested

There is no effect however...

 

[PHV]

SELECT ...
FROM ...
WHERE ...
ORDER BY providerid, memberOrder

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[katrina11]

Tried but no effect...

 

[PHV]

And this ?
ORDER BY providerid, Val(memberOrder)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

If you changed memberOrder field to Number, your Order By should work OK now.
If it does not work - there is something wrong with the memberOrder field, it is NOT numeric.

Have fun.

---- Andy

 

[katrina11]

Even with VAL() it doesn't work.

It is a number type. I double checked it through the design view

 

[katrina11]

Actually whenever I try text data type with Format() or without it doesn't sort properly.
Whenever I try number data type with val() or without it doesnt sort properly.

 

[Andrzejek]

That does not make any sense. If it IS a Number, Sort should work OK. But you say it does not. :-(

I would try this - add another field (column) to your table, set it as Number, and try to order by this column instead.

Have fun.

---- Andy

 

[katrina11]

The thing is that user should click member by MemberOrder for the certain provider. The same member could attend different providers…If he attend another provider his MemberOrder would be different while MemberID is the same (like for memberid=88888 who has memberOrder=2 for Provider1 while he has memberOrder=1 for Provider2)

ProviderID MemberOrder memberID
Provider1 1 77777
Provider1 2 88888
Provider1 3 99999

Provider2 1 88888
Provider2 2 66666
Provider2 3 55555


Sorting by a kind of Pseudokey field would not give me a chance to group them within each provider ID....

 

[Andrzejek]

Are you saying that the SQL:

SELECT ProviderID, MemberOrder, memberID
FROM SomeTable
ORDER BY ProviderID, MemberOrder

Does not work - orders records not the way it should?

If you have differenr SQL that the one here, please COPY and PASTE your SQL so we can see exactly what you have.

Have fun.

---- Andy

 

[katrina11]

Here it is:

SELECT
[1_provider].providerSiteID
, [0_provider site].providerSiteName
, [1_provider].providerId
, [1_provider].providerName
, [2_member].memberOrder
, [2_member].memberid
, [2_member].memberName

INTO [2a_Members for Chart Review]

FROM (2_member INNER JOIN 1_provider ON [2_member].providerId = [1_provider].providerId)
INNER JOIN [0_provider site] ON [1_provider].providerSiteID = [0_provider site].providerSiteID


ORDER BY [1_provider].providerId, VAL([2_member].memberOrder)
;

 

[Andrzejek]

Would this SQL order the records right? just one table.

SELECT memberOrder,
       memberid,
       memberName
FROM   2_member
ORDER BY memberOrder

Could you run it and let me know?

Have fun.

---- Andy

 

[PHV]

INTO [2a_Members for Chart Review]
You're using an append query and thus any order by clause is IRRELEVANT.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[katrina11]

PHV,

Does it mean that if I would create a table based on [2a_Members for Chart Review] query, then using Order by clause would work?