Sorting Records In A Query
I am having a problem sorting records in a query. I have about 5, 000 entries in a table and when I build my query based on certain fields, the numbers in certain columns are not in order although I use the sorting feature. ( e.g.) I have 5 columns with numbering data contained in the columns, and I have it set up to sort in ascending order, which it did, but I noticed that the Volume # column sorts the Volume numbers differently (see example below):
V. 1
V. 10
V. 11
V. 12
then it goes back to
V. 2
V. 3
V. 4
V. 5
V. 6
V. 7
V. 8
V. 9
It looks like it sorts anything with a 1 first then the rest later. How do I fix this?
Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
Sorting Records
I'm new to this but I am currently working on a DB for work. We are tracking employees that are trained by using an "X" in a Field to signify that they are in the process of being trained. Once they are trained they will enter the date it was completed. I need a query that will give me the number of "X"s and the number of dates. Currently it only gives me the total for all "X"s and dates. Is it possible to split the fields in a query and further receive the percentage of each including how many employees do not have any "X"s of dates? Any help would be greatly appreciated. I hope that this make sense.
View Replies !
Sorting Records In A Lookup
Hi all, Pretty straight forward question but I can't seem to find the answer anywhere on the net. I have a table with a text field that's values are pulled from another table via the Lookup Wizard. Everything works fine, and I now have a drop-down box that displays all of the fields I have set up with the Lookup Wizard. But, how do I sort the records that appear in that drop-down? They are not ordered in the same way that the table they are pulled from is, nor do they seem to be ordered based on any one column. Any help is much appreciated.
View Replies !
Sorting Records After The Last Space
Hello all, I have a field in a database that has people's full name (e.g. Jane B. Doe). I would like to sort by their last name. Can I build a query that looks at everything after the last space in their name? Thanks in advance. Ron
View Replies !
Sorting Records By Times
ok so im not sure how to explain this but ill give it my best shot. I have an excel sheet that has a list of order info. heres a sample of what it looks like: 70144:37.0Starting order split <34010993;01>gregory_pm34010993 70245:16.0Script complete for <34010993;0106>Rx <24073318>gregory_pm34010993 70103:37.0Starting order split <34010995;01>knox_br34010995 70204:26.0Script complete for <34010995;0101>Rx <24239630>knox_br34010995 70126:49.0Starting order split <34011015;01>flander_ar34011015 70232:09.0Script complete for <34011015;0101>Rx <24008174>flander_ar34011015 70134:01.0Starting order split <34011015;01>flander_ar34011015 70127:08.0Starting order split <34011061;01>flander_ar34011061 70227:26.0Script complete for <34011061;0105>Rx <24240139>flander_ar34011061 70227:55.0Script complete for <34011061;0103>Rx <24240083>flander_ar34011061 70152:00.0Starting order split <34011173;01>parker_tp34011173 70252:46.0Script complete for <34011173;0101>Rx <24071140>parker_tp34011173 70103:35.0Starting order split <34011369;01>sexton_pa34011369 70204:00.0Script complete for <34011369;0101>Rx <24240569>sexton_pa34011369 70149:02.0Starting order split <34011668;01>knox_br34011668 70250:59.0Script complete for <34011668;0104>Rx <21441348>knox_br34011668 70134:20.0Starting order split <34011764;01>hicks_jo34011764 70234:44.0Script complete for <34011764;0102>Rx <22787965>hicks_jo34011764 70235:06.0Script complete for <34011764;0101>Rx <22787933>hicks_jo34011764 70236:11.0Script complete for <34011764;0103>Rx <22788283>hicks_jo34011764 70156:45.0Starting order split <34011855;01>knox_br34011855 ______^this line is the time ok so here is my problem. I need two records, one for the start of the order(the earilest time) and one for the closing of the order(the latest time). I also need the time inbetween the two. Thanks!
View Replies !
Sorting Records In Access DB
I have an ASP file that retrieves records from an Access database and displays them in the browser. The problem is that the records come out unsorted. I need to sort them by entry date. I have a field in the table called "EntryDate" that is in the format MM/DD/YYYY. Can I sort by this field? If so, what would the SQL statement be? Thank you.
View Replies !
Sorting Records In A Report
Is there a way to Sort records in a report by a Report total? I have a report that is created by a crosstab query that sums orders by Billing Client but the query also has a billing client qroup which can contain many Billing Clients. Since the report total (Counts) are by Billing Client I do not have the actually totals per client group in the query to sort by. I know how to do this in Crystal Reports by the report would needs to mulpiple workstation, which have the crystal reports engine but not the actually designer (In other works I don't know the code to print a crystal report from Access with only the crystal reports engine on the workstation).
View Replies !
Sorting Of Records In Text Box In Form
I have a text box called tboWorkstream on a form. I can scroll through the records using the navigation bar at the bottom. However, I want the records to be sorted by the Workstream Column, not the Workstream ID column (which is the primary key.) Is there a way to do this? I know you can list the way you want in a combo, but not sure how you do this in a bound textbox. Thanks sunil
View Replies !
Help With Sorting A Query?
I have a job database where I sort jobs by upcoming, inactive, closed and active. Each has a corresponding number 1-4 respectively. The main table I update the jobs in is the JOB TABLE. I print a weekly report for our weekly meeting but I only want the active jobs in the report. My question is: how do I perform a sort function in the query, so I will have only the active jobs in the database table show in the query? Thanks in advance! If more info is needed I wil post additonal.
View Replies !
Sorting In A Query
I'd like to sort in my query - used to populate a combo box - first by name, and then by ID. Is there a way to force Access to sort in a particular order? It seems like it starts sorting with the first field that's to be sorted, but I want it to start with field 2 and then sort by field 1.
View Replies !
Sorting A Query
I am fairly new at relying heavily on Access for my programming. Problem: I have a telephone database with call detail for every call. I only want specific records, which I am pulling by selecting sort (in query design mode) for that particular field. I need to sort like you would in Excel (Column A first, Column B second, and etc). Can someone help direct me in the right path as to how I need to run a mulitple sort? Your help is much appreciated.
View Replies !
Sorting A Table, Apply The Sorting To A Form
Hi all. I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number. I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all. Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on.. I'd apreciate some help with this :) Thanks Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)
View Replies !
Sorting Further From A Duplicates Query
I have a Duplicates Query that extract the following information from my Table Name Surname DOB Joined The query then extracts info such as... Bill Smith 25:12:70 1999 Bill Smith 25:12:70 2005 Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check. Could someone please assist me with this query if it is possible? Thank you in advance.
View Replies !
Sorting Crosstab Query
I have a crosstab query where my column headings are the names of the months based on invoice dates. what i currently have: Month: MonthName(Month([AnnualIncome]![InvoiceDate])) so this spits out April May and June in the column headings. The problem is that it is sorting it alphabetically, not by the numerical value - so my column headings are coming out as april, june, then may. (I assume once I have july that will come in between april and june.) Any help would be REALLY appreciated. Thanks in advance.
View Replies !
Sorting Query Results
Hi I have a problem sorting some query data and I wonder if someone could help point me in the right direction. I have a query with two columns, in the first column is a number (either 6 or 7) and the second is the name of a business. Eg Column 1 Column 2 6 Pizzas & Co 6 Pizzas & Co 7 Pizzas & Co 6 IPM Ltd 7 IPM Ltd 6 Computer Sales Ltd 6 Computer Sales Ltd 6 Computer Sales Ltd 7 Computer Sales Ltd 7 Computer Sales Ltd 7 Computer Sales Ltd 7 Computer Sales Ltd 7 Computer Sales Ltd And I want to produce some another query so that the output which looks like: Column 1 Column 2 (6's) Column 3 (7's) Pizzas & Co 2 1 IPM Ltd 1 1 Computer Sales Ltd 3 5 So for each company name, I am essentially doing a frequency count for all the 6's and 7's. I think this must be relatively easy to do, but I am getting it wrapped around my head! Many Thanks, Lee
View Replies !
Sorting Query & PivotChart
Hey everybody, I know this question maybe be very lame but its been ages since i've touched on my sql knowledge. I've set out my database and i have 1 table which is holding the info i need to extract so that i may put it into my report. I wish to count the different values in a specific row: Example: Whats the problem Behavioural Physical Physical Behavioural I wana be able to count these seperately as in: SELECT COUNT(Client.[Whats the problem] LIKE 'Physical') AS Phy AND COUNT(Client.[Whats the problem] LIKE 'Behavioural') AS Beh FROM Client; Result: Phy Beh 2 2 I havent got a clue if i'm doing it right but i need to get it to work in access 07. Also one more question i have created a Pivotchart in a form but when a create a button on my main startup form and click into the Pivotchart form all i get is the Datasheet view, how can i get it to open up the Pivotchart through the form. Any info would be greatly appreciated Thank You.
View Replies !
Genaral Month Sorting Query - Help?!
Hi i have a general query and im not sure which section to put it in yet Basically i need to have a date choosing system which allows me to enter dates once into the form and then print out monthly reports on which of our clients is to be sent a report, some clients are half yearly, some quarterly, some yearly, at present i have a drop down box for these choices and a drop down box for month is there a way of combining the half yearly and month ie May to come up with nov so that When i want to print out may's report, Mr X (who has a half yearly report) can have half yearly chosen and May chosen but still get on both the novembr and May print out along with mr Y who only has a yearly report in May I do not really want to have to change the values in the drop down boxs as i do not think employees would do it and the system would be inaccurate If a query could be made which somehow combined annual may and half Annual May, AND would also work out that half annual may would become Nov? ie: Annual Nov and Half Annual May Im confused about the best way to do this so any suggestions would be v much appreciated :) I dont know if the drop down system is the best way or how i would make a query to make this work and what the best method of making the reports is? any help much appreciated H
View Replies !
Sorting Query By Date & Employee Name
I created a query which at first was simply to run a query which asks you for a drivers name, however we have decided that now we want to sort by the employee name as well as by a specified date range. I originaly had it set up to select the driver name with this in the criteria field in the Driver Name column: [Enter Driver Name] Then I tried to enter the following in to the criteria field in the date column: Between [Enter Start Date: (Format MM-DD-YY)] And [Enter End Date: (Format MM-DD-YY] However when I run the query it asks me for the date about 4 times, and then it goes to a blank report. I am sure I am not doing this properly somehow, anyone care to help out?
View Replies !
Sorting Of Query In Subform Doesn't Happen
Hi, I have a query for a subform which is displayed in datasheet mode (see attachment). I've created a form based on a query and used it as a subform. The "casenumbers" per clients are displayed from 1 and upwards, I would like to have them sorted Descending. So in the query I sorted them that way. When I run the query they are displayed in descending order. When I open the subform based on that query however it is not. I can sort descending anyway by clicking the column on which I want to sort and do it that way, but that becomes bothersome and it doesn't need to be that way. Record source is ok: SELECT KlantNAW.ClientNumber, CaseInfoTable.Casenumber, CaseInfoTable.DateInitialCall, CaseInfoTable.DateCaseCreation, CaseInfoTable.CaseAccepted, CaseInfoTable.[1stAppointmentDate], CaseInfoTable.DateCaseClosed, CaseInfoTable.About1 FROM KlantNAW INNER JOIN CaseInfoTable ON KlantNAW.ClientNumber=CaseInfoTable.ClientNumber; Order by: Casenumber DESC I thought, well, lets create a new form based on the query to see if that changes anything, maybe I've changed something I shouldn't have. But nope, same result. How can make the form sort descending in datasheet mode?
View Replies !
Stopping A Crosstab Query Sorting Fields Alphabetically
HI all, In my database i have a crosstab query that has a text field as a row heading and a number field as a column heading. The query is sorting the row heading on the left hand side alphabetically. I dont want it to do this, i want it to show the fields as they are in the table. Is there anyway of stopping the query automatically sorting alphabetically? Thanks in advance
View Replies !
Query Problem: Records Linked To Records
Hi, I was wondering if anyone would be kind enough to help me out on this problem I have. I have a table: --------------------------------- Source | LinkedTo --------------------------------- A1 B2 B2 C1 B2 C2 C1 D1 C2 D2 C2 D3 B2 D4 A1 D5 Another table: ------------------------------- Item | Load --------------------------------- D1 2 D2 4 D3 6 D4 8 D5 10 So looking at the tables, C1 = 2 C2 = 4+6 = 10 B2 = C1 + C2 + D4 = 20 A1 = 20 + 10 = 30 Here, there are 4 levels that I have to go into to get the grand total. If I did not know how many levels there were, how would I be able to do this through code? through queries if possible?? If anyone could help, that would be great! Thank you in advance. Bear
View Replies !
Query Help - Sorting Address To Ship Address
I have a cvs file which I have linked to a db table the cvs has the following info: ID companyname CustomerName Address Address2 City State Zip Country / ShipName ShipAddres ShipAddress2 ShipCity ShipState ShipZipShip Country What I need to do is have a print out of this information, the shipping address etc needs to be on the left & customername etc on the right. Ship info is for posting. however if the ship info has not been filled in (no data) the the customer info needs to move to the left of the print page. (ie if the customer address etc is the same as the ship address then the ship address dosen't get filled in) I very green on access
View Replies !
Show All Records From One Query And The Sum Of A Field In A Related Query Record
I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath. That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal. I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By. What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable. Can someone see where I’m going wrong? Slaughter slaughter at mizzou dot edu
View Replies !
|
TRACKER
