Queries :: Sorting A Query In Unconventional Way?
Aug 1, 2015
I want to sort the results of a query as follows.
The query returns 4 fields
ID, Field1, Field2, Field3
For the first 500 rows I want to sort by Field1 with the remaining rows sorted by Field3.
I can identify the first 500 rows bi ID being less than 501.
I've done something similar where I've displayed a value of a field in a different order by coding something like
ORDER BY Iif([field3]=9, 0.5, [field3])
which sorts the 9's first followed by the rest in ascending order but can't work out what to do here.
View Replies
ADVERTISEMENT
Feb 5, 2014
I have a query that pulls info from a table. One of my fields is frequency and in that field i have weekly, annually, daily, etc.
How can I make the query sort starting at the daily one and move out to annually?
View 2 Replies
View Related
Mar 21, 2013
i intially, I wish to have attendance stats for 3 groups of people over 2 days in a report
i have the two queries for the raw data, but wish to combine into a report that will ultimately become a "dashboard" report for half a dozen queries., and can only get one or the other to work do i need to combine the queries into a summary query of sorts?
View 1 Replies
View Related
Jan 28, 2015
I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
View 9 Replies
View Related
Apr 11, 2007
Hi,
I have a macro that runs a 'make table' query followed by a series of 'append queries' each time the macro is called. I require the data from each append query to be pasted below the existing data already created by the original make table query and the append queries that have already run. The issue that I am having is that the data in fact appears to be appended in an unpredictable way (ie sometimes I run the macro and find that data from the append queries is below the data from the make table query, at other times I run the macro and find that data from the append queries appears above the data from the make table query. Unfortunately, this causes problems with downstream queries, which rely upon finding the last row of data subject to various filters. Can anyone help me understand how Access determines the sorting/positioning of new data appended to an existing table, and therefore resolve this issue?
Thanks in advance,
jc
View 3 Replies
View Related
Oct 14, 2013
I have a query that will sort groups of zip codes using the following expression: Between [T_ZipRange]![StartZip] And [T_ZipRange]![EndZip]. The first and last zip codes are entered and the expression returns the zip codes between the "start" and "end" range. The problem is that if a zip code is only 5 digits: xxxxx- instead of xxxxx-xxxxx, the zip code will show up in other sorts. The zip code field is a text field. How can I limit the sort, based on the number of digits in the zip code?
View 2 Replies
View Related
Dec 6, 2006
I have a set of two queries (which I'll call A and B, for convenience). Query A reads a table containing customer order numbers, a sequentially-assigned line number for each item requested by a customer on a particular order, and a part number associated with each order line. The line numbers are not integers, but run out to three decimals - so 1.000, 1.100, 1.150, 1.200, etc. would all be valid line numbers. All Query A does is to sort the table, with the primary (first) sort being order number, and with line number being the secondary (2nd) sort. Each record also contains a field equal to the integer value of the line number, so for lines 1.000, 1.100, 1.150, 1.200, this third value would always be 1; similarly, for lines 2.000, 2.100, and 2.200, the third value would be 2.
Query B uses query A as its source of input, and performs grouping (using the "Group By" function) so that query B's output displays only one record for each unique combination of customer order number and INTEGER line number; the part number displayed on each row of query B's output is supposed to be the part number on the first row of query A's output whose order number and integer line number correspond to each group in query B. (I am using the syntax of "First(Part Number)" in query B to report this.) This seems to be working most of the time, but not consistently; sometimes, for each unique order number / integer line number combination, Query B reports a part number from the wrong row of Query A. So, for example, order 12345, integer line 1, as reported from query B, may report the part number on line 1.300 from Query A, instead of line 1.000 from query A as intended.
Does anyone know a fix for this? I've got a team of anxious coworkers who are hoping for it.
Thanks a lot!
View 1 Replies
View Related
Jul 7, 2014
I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.
This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.
1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.
Is there a way I can reformat MTHYR or a completely different solution?
My current code is
Code:
TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number]
SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number]
FROM VOR_CALD_REGION
GROUP BY VOR_CALD_REGION.[Dealer Name]
PIVOT VOR_CALD_REGION.MTHYR;
View 3 Replies
View Related
Sep 16, 2013
I've created a query based on 2 other queries.
I then filter the results of the third query based on 2 dates.
This worked great for 2013.
As a test I started making some records for 2014 and I've now found that my third query is not filtering the dates properly.
The filtering is happening based on date fields that contain only month and year eg: "09-2013".
My problem is that when I try to filter for records in 2014 it brings up results for 2013 as well.
If for example I had a record in August 2013, September 2013 and August 2014.
The sort would be:
08-2013
08-2014
09-2013
NOT:
08-2013
09-2013
08-2014
How do I make the sort apply to month then year to get the correct results returned?
View 6 Replies
View Related
Apr 23, 2013
I'm doing a query and I want it to show records from this week only.
Is there a formula I can use in criteria?
View 3 Replies
View Related
Jan 27, 2015
I have a field on a table that is a DateTime format and it needs to be that way. I am trying to query that table for all the dates to use in a combobox but I only wat the date not the time. The problem is when I format the field to just date I can't sort it anymore as a date and I can't group them. I still get multiple lines in my combobox because there multiple times for the same day. what is the proper way in a query to extract just the date from a field and still sort it as date ie 1/12/2015 is greater than 12/15/2014?
View 8 Replies
View Related
Mar 10, 2006
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 5 Replies
View Related
Aug 9, 2007
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 4 Replies
View Related
Feb 1, 2007
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 2 Replies
View Related
Feb 6, 2008
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 1 Replies
View Related
May 13, 2005
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 1 Replies
View Related
Jul 12, 2005
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 4 Replies
View Related
Nov 9, 2005
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 3 Replies
View Related
Apr 28, 2006
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 2 Replies
View Related
Nov 24, 2014
How to sort a subset in a query. I have three fields BatchID,Month and Year. The BatchID can have multiple months and years. I would like to order the final sort so as the Batchs appeared in the order of the highest date combination with in each Batch.
The following is the SQL of my first Query which establish's the lowest and highest date in each batch.
Code:
SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month])) AS Expr1FROM tblRevOnlySelYear
GROUP BY tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID
HAVING (((tblRevOnlySelYear.OperatorID)=1))
ORDER BY Max([Year] & IIf(Len([Month])=1,0 & [Month],[Month]));
The next SQL is the second query which results in the Batchs in the correct order by year but the months are not correct.
Code:
SELECT tblRevOnlySelYear.BatchID, tblRevOnlySelYear.OperatorID, tblRevOnlySelYear.Month, tblRevOnlySelYear.Year, qryRevTest4_1.[1stDate], qryRevTest4_1.LastDateFROM qryRevTest4 AS qryRevTest4_1 INNER JOIN tblRevOnlySelYear ON qryRevTest4_1.BatchID = tblRevOnlySelYear.BatchID
WHERE (((tblRevOnlySelYear.OperatorID)=1))
ORDER BY qryRevTest4_1.LastDate;
View 8 Replies
View Related
Apr 3, 2007
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 1 Replies
View Related
Nov 6, 2006
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 1 Replies
View Related
Jul 27, 2014
I have a query where I ORDER BY ClientId, MatterId and Transdate. The result of the query is correct. All of the transdates are in ASC order within the Matter.
When I run the report for a MatterId the relative transactions are not sorted on transdate
Can there be something in the report that could be effecting the order of the transactions.
View 3 Replies
View Related
Dec 30, 2013
I currently have records that end with a letter and 2 numbers. For example, A1, A2, ... , A10, A11. When I try to sort my table/query by these values, A10 & A11 come before A2. It seems that it is sorting by the first digit shown. Is there any way to fix this quickly within table/query properties so that this can be displayed in proper numeric order?
View 7 Replies
View Related
Sep 16, 2013
I have created a form based on a query. The funny thing is when I tried to sort fields on the form, the following message box pops up:
Syntax Error (Missing Operator) In (Field Name)
I just did the exact same thing several weeks ago, and that first form could sort fields normally. The only difference between the first one and this is that this second query was based on several tables, while the first was based on a single table, although I doubt that is where the problem is.
I forgot to add that I could sort fields where the field name doesn't have spaces in it. For example, the field name "Customer ID" triggers the syntax error, while the field "S/N", "Company", etc. can be sorted like normal.
View 7 Replies
View Related
Jun 14, 2007
Hi Everyone,
Could anyone tell me how I sort a field so it sorts by the surname, not the first name? It's one field with 2 names seperated by a space, i.e. George Michael.
Many thanks for any help.
Essex
View 5 Replies
View Related
