If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
sorting order of columns in access
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 |
#2
|
|||
|
|||
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. -- Duane Hookom MS Access MVP "David" wrote in message ... 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 |
#3
|
|||
|
|||
Duane Hookom wrote:
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 we are talking in terms of records associated with a Jet database, their physical order is sequential and predictable i.e. primary key order for rows inserted before the last file compact and datetime order thereafter. If you do not specify an ORDER BY clause you will get the physical ordering. I'm not sure 'predictable' means the same as 'reliable' but things aren't as random as you imply. Jamie. -- |
#4
|
|||
|
|||
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 "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP "David" wrote in message ... 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 |
#5
|
|||
|
|||
David wrote:
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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#6
|
|||
|
|||
Is there anyway possible to get some help on writing the code.
May be just the basic format. Thanks "Rick Brandt" wrote: David wrote: 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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#7
|
|||
|
|||
David wrote:
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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#8
|
|||
|
|||
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 "Rick Brandt" wrote in message ... David wrote: 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. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sorting Percentages | Yubasus | Running & Setting Up Queries | 11 | December 9th, 2004 08:43 PM |
Match two Excel Columns in Access | CR | General Discussion | 1 | November 12th, 2004 11:59 AM |
Sorting data in various columns | carolmcg | General Discussion | 2 | October 14th, 2004 02:25 PM |
Wrong sort order of report's details section | dsjohn_242 | Setting Up & Running Reports | 6 | July 12th, 2004 10:21 PM |
Sorting of mixed zip codes (5 digit and 9 digit) in ascending order | Jason Morin | Worksheet Functions | 2 | January 26th, 2004 09:08 PM |