sorting order of columns in access

[David]

Hi All

I have a database that call on specific records to display based on their
assigned values.
eg. if I enter 1, i get records that are listed as fields under 1.
if I enter 2, I get records that are listed as fields under 2.
and so on....

My issue is that when I enter 1, 23, 18, 67, 43.........I dont get the
records in this order.

Instead access sorts them and give records in either ascending or descending
order..eg...1, 18,23,43,67 or 67,43, 23, 18, 1

How can have the records displayed under the order i type 1, 23, 18, 67, 43 ??

If anyone could please address this issue....I would greatly appreciate it.

Thanks

 

[Duane Hookom]

I think you have some confusion over Access terms and functionality. Records
are like marbles in a bag. There is no reliable order unless you specify a
sort order. If you want records to display in a specific order, you must
have a value or values stored in the record that can be used to sort the
records.

Your use of "listed as fields" makes no sense.

 

[David]

Yes I do agree that I am confused with Access terms. I am relatively new to
Access and am in the process of learning. However, I want to solve this
problem.

Let me describe how everything is setup in my database.

Under Tables.....I have the below fields and info under fields.

Plan Number Company Name Insurance Rate Deductions
1 ABC 0.9% 00
2 guy1 1.2% 3%
3 Lunch 3.4 % 6%
4 tecknic 2.9% 9%

Under queries
I have the information that brings up the fields and records when I call on
them

Under report
I have the report that gives the format the information is to appear on the
page.
-----------------------------------------------------------------------

Now when I run the query, it asks me to enter upto 6 Plan Numbers........
I enter 1,4,5,7,3,2,.........this then opens a page that displays the
information for these Plan Numbers....eg

Plan Number 1 2 3
4
Company Name ABC UUAY ASD FAS
Deductions % % %
%
Insurance Rate % % %
%

Now the problem is that when I enter Plan Numbers in the sequence 1,4,5,7,
3, 2
access automatically sorts them either in ascending or descending order
1,2,3,4,5,7 or 7,5,3,2,1.....even when I remove the Filter/sort.

I hope I am clear on this and have explained it properly. If anyone could
again help me out.....I would really appreciate it.

THANKS

DAVID

 

[Rick Brandt]

Yes I do agree that I am confused with Access terms. I am relatively
new to Access and am in the process of learning. However, I want to
solve this problem.

Let me describe how everything is setup in my database.

Under Tables.....I have the below fields and info under fields.

Plan Number Company Name Insurance Rate Deductions
1 ABC 0.9%
00 2 guy1 1.2%
3% 3 Lunch 3.4 %
6% 4 tecknic 2.9%
9%

Under queries
I have the information that brings up the fields and records when I
call on them

Under report
I have the report that gives the format the information is to appear
on the page.
-----------------------------------------------------------------------

Now when I run the query, it asks me to enter upto 6 Plan
Numbers........ I enter 1,4,5,7,3,2,.........this then opens a page
that displays the information for these Plan Numbers....eg

Plan Number 1 2 3
4
Company Name ABC UUAY ASD
FAS Deductions % % %
%
Insurance Rate % % %
%

Now the problem is that when I enter Plan Numbers in the sequence
1,4,5,7, 3, 2
access automatically sorts them either in ascending or descending
order 1,2,3,4,5,7 or 7,5,3,2,1.....even when I remove the Filter/sort.

I hope I am clear on this and have explained it properly. If anyone
could again help me out.....I would really appreciate it.

Applying CRITERIA to a query and specifying a SORT on that query are two
completely different things. There is no way to do what you want without
writing some code that will basically rewrite the query for each set of
numbers you specify.

 

[David]

Is there anyway possible to get some help on writing the code.
May be just the basic format.

Thanks

 

[Rick Brandt]

Is there anyway possible to get some help on writing the code.
May be just the basic format.

Here is a basic query that I think will do what you want for 3 supplied
search parameters. The field being searched is Field1.

***Start SQL***
SELECT Field1, Field2, ...Fieldn,
SWITCH(Field1 = [Enter First Number], 0, Field1 = [Enter Second Number], 1,
Field1 = [Enter Third Number], 2) As SearchList

FROM TableName
WHERE Field1 In([Enter First Number], [Enter Second Number], [Enter Third
Number])

ORDER BY SWITCH(Field1 = [Enter First Number], 0, Field1 = [Enter Second
Number], 1, Field1 = [Enter Third Number], 2)
***End SQL***

The SearchList field will return a 0, 1, or 2 for each returned row and
since we are sorting on that field you will see the output values in the
order that you entered the search criteria. The problem is the SWITCH
function needs to be configured for the exact number of search choices you
are going to enter. If that number is going to vary you would have to use a
VBA code routine to create the SQL string for each situation. If you enter
5 search criteria then the SWITCH function needs five sets of arguments, if
you enter only two criteria then it needs only two sets of arguments.

The code would basically involve a loop that continually prompts the user
for search criteria. You could use an InputBox and test for a zero length
string being returned. When the user enters a number you store that in an
Array of values. When they press [Cancel] you will get a ZLS back and you
know they have entered all the choices they want. Then based on the number
of loops performed you would know how many arguments that the SWITCH
function required and could build the SQL string up from there.

 

[Duane Hookom]

I'm not sure if this will work for you but consider querying the Employees
table in Northwind.mdb where the EmployeeID is an autonumber. You might want
to add a few more employees to get the employeeIDs into double digits. Then
create a query like the following where you can sort on the calculated
Sequence column.

SELECT InStr("," & [Enter Numbers like 4,5,12] & ",","," & [EmployeeID] &
",") AS Sequence, Employees.*
FROM Employees
WHERE (((InStr("," & [Enter Numbers like 4,5,12] & ",","," & [EmployeeID] &
","))>0))
ORDER BY InStr("," & [Enter Numbers like 4,5,12] & ",","," & [EmployeeID] &
",");


--
Duane Hookom
MS Access MVP

Is there anyway possible to get some help on writing the code.
May be just the basic format.

Here is a basic query that I think will do what you want for 3 supplied
search parameters. The field being searched is Field1.

***Start SQL***
SELECT Field1, Field2, ...Fieldn,
SWITCH(Field1 = [Enter First Number], 0, Field1 = [Enter Second Number],
1,
Field1 = [Enter Third Number], 2) As SearchList

FROM TableName
WHERE Field1 In([Enter First Number], [Enter Second Number], [Enter Third
Number])

ORDER BY SWITCH(Field1 = [Enter First Number], 0, Field1 = [Enter Second
Number], 1, Field1 = [Enter Third Number], 2)
***End SQL***

The SearchList field will return a 0, 1, or 2 for each returned row and
since we are sorting on that field you will see the output values in the
order that you entered the search criteria. The problem is the SWITCH
function needs to be configured for the exact number of search choices you
are going to enter. If that number is going to vary you would have to use
a
VBA code routine to create the SQL string for each situation. If you
enter
5 search criteria then the SWITCH function needs five sets of arguments,
if
you enter only two criteria then it needs only two sets of arguments.

The code would basically involve a loop that continually prompts the user
for search criteria. You could use an InputBox and test for a zero length
string being returned. When the user enters a number you store that in an
Array of values. When they press [Cancel] you will get a ZLS back and you
know they have entered all the choices they want. Then based on the
number
of loops performed you would know how many arguments that the SWITCH
function required and could build the SQL string up from there.