The following is produced by a query i've built....
4a (2007 Jan - Jun)
5a (2008 Jan - Jun)
4b (2007 Jul - Dec
5b (2008 Jul - Dec)
I need it in numerical order not alphabetical so it looks like this:
4a (2007 Jan - Jun)
4b (2007 Jul - Dec
5a (2008 Jan - Jun)
5b (2008 Jul - Dec)
Any ideas
Below is the code to my crosstab query. I want to sort
descending) on the GrandTotal. I can apply a sort on the
results of the query but cannot sort in the query. It is
my understanding that this is due to this being a crosstab
query. Has anyone any advice on how I can somehow
automatically descend sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No).
So what I am trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
I'm trying to sort my query results by the month value "Data (IMPORT).KP_MM_DS" but I get alphabetical results.
SELECT [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, Round((Count([Data (IMPORT)].KP_ISSU_ID_NB)/20)) AS CountOfKP_ISSU_ID_NB, [Data (IMPORT)].KP_MM_DS
FROM ([Data (IMPORT)] LEFT JOIN [Facility Lookup] ON [Data (IMPORT)].KP_FAC_NM = [Facility Lookup].[FAC NAME]) LEFT JOIN DEPTLOOKUP ON [Data (IMPORT)].KP_DEPT_ORG_NM = DEPTLOOKUP.[EPM DEPARTMENT NAME]
WHERE ((([Facility Lookup].[REPORTING MCA])<>"XXX") AND (([Data (IMPORT)].KP_SB_RGN_CD)="N"))
GROUP BY [Data (IMPORT)].KP_YR_NB, [Data (IMPORT)].KP_MM_DS, [Data (IMPORT)].KP_MM_DS;
I have after much stress gotten my comments history field to appear on a form.
The following code gives me what I needed
Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))
I have only one simple thing left, that is perplexing to say the least.
Some comment fields will be many short notes, spread over time.
My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.
i made a report based on a query. when i run the query, my results are sorted by Job Time Ascending, like i want...
when i made my report i chose job id as the main group priority in the wizard..
now in my report it displays the jobs in ID ascending, rather than Job time ascending as my query says..
i dont want to re-do the report it took ages.. how can i fix this?
I have a query that sorts players batting averages from highest to lowest. When I run the query just by itself it sorts it right however when I open the report based on that query it does not sort in descending order like I asked. What am I missing??
View 3 Replies View RelatedWhen I'm applying a sort and filter in a form, Access is updating the Filter and Order By properties of the form, so that it is possible to re-use in conjunction with Filter on Load and Order By on Load properties. However, a consequence of this is that when the form is closed, it prompts the user whether they want to save the design of the form. I want to circumvent this as I don't want to re-use the sort and filter and I don't want to be prompted to save the design of the form.
Although I can circumvent this by closing the form using a method that doesn't prompt for saving, the additional complication here is that the form in question is in the Navigation subform of a Navigation Control. Hence when I click on a another Navigation button, it (not me) closes my current form and hence prompts me whether I want to save the design of the form (if I have been sorting and/or filtering). I can't see how to circumvent this and the prompting is resulting in unacceptable usability.
I used to queries ,1 to get items that are taken ( its all about sign in sign out for equipment) and other query is list of all items.
How can i make 3rd query which will give me all but taken items from query1?
(of course items from query 1 are in query2)
thx in advance
I have a query that has four fields. One is used only as a sort field. It works fine. However, if someone resorts the open query on one of the other fields (not the one used as the sort in the query), then inadvertently says “yes” to save when exiting, the next time they open the query, it is sorted by the field last sorted on, not the one set in the query
This is no biggie but just an annoyance. Any suggestions?
Thanks,
SKK
Hi all,
I have a form in Access 2K that properly displays city, state and zip codes. As I type in each letter of a city's name into the combo box, I get the first city in the sorted list displayed (autofills). That's fine when there is only one zip code for a city, but when I enter "chic" for chicago I still get the first chicago zip code in my table.
It sure would be nice if I could do a "sort within a sort" so that if there were two or more records with the same city name then that whole group would drop down so I could choose one of the other zip codes. If this is doable, kindly point me to an example as I need all the help I can get.
Thanks in advance for any responses.
D
I need to sort my query in a rather complicated way, and haven't been able to get the examples I've found on here to work.
I have the following fields
[Category]
[Name]
[Page]
Here is what I need.
Where [Category] = "Table of Contents"
Sort by [Name], Then by [Page] Descending
Else
Sort by [Name], Then by [Page] Ascending
All records will be queried at the same time, so "Table of Contents" categories will be included in the same query as other records, but need a different sort order. I know this is stupid, but leave it to government to impose some nonsensical rule on you.
Is there a way to do this?
Thanks,
Doc
I have query that uses the following forumla "Team HCP2: ([Member Frt HCP]+[Member Back HCP]+[Guest Frt HCP]+[Guest Back HCP])/2" to calculate team handicaps. The formula performs as I need it to but when I try to check the sort box to sort "Ascending" it asks for a parameter.
All I want to do is run the query and have the results of the calculation field sort the column of results in a ascending order. All non calculated fields in the query are sortable by using the sort by ascending feature in the design mode.
Best regards,
Dave
Hello,
I have an access query that I run on a regular basis but that doesn't always sort. It sorts most of the time, but once in a while it won't, which makes it really annoying and impractical.
Does anybody know why this is happening?
Thank you.
The query is:
SELECT P.GEOSORT, P.TERR_CODE_IND AS TERR_CODE, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_SHORT_NAME, P.M23VA, P.M22VA, P.M21VA, P.M20VA, P.M19VA, P.M18VA, P.M17VA, P.M16VA, P.M15VA, P.M14VA, P.M13VA, P.M12VA, P.M11VA, P.M10VA, P.M9VA, P.M8VA, P.M7VA, P.M6VA, P.M5VA, P.M4VA, P.M3VA, P.M2VA, P.M1VA, P.CMTHVA, P.M23VC, P.M22VC, P.M21VC, P.M20VC, P.M19VC, P.M18VC, P.M17VC, P.M16VC, P.M15VC, P.M14VC, P.M13VC, P.M12VC, P.M11VC, P.M10VC, P.M9VC, P.M8VC, P.M7VC, P.M6VC, P.M5VC, P.M4VC, P.M3VC, P.M2VC, P.M1VC, P.CMTHVC, P.M23VSAA, P.M22VSAA, P.M21VSAA, P.M20VSAA, P.M19VSAA, P.M18VSAA, P.M17VSAA, P.M16VSAA, P.M15VSAA, P.M14VSAA, P.M13VSAA, P.M12VSAA, P.M11VSAA, P.M10VSAA, P.M9VSAA, P.M8VSAA, P.M7VSAA, P.M6VSAA, P.M5VSAA, P.M4VSAA, P.M3VSAA, P.M2VSAA, P.M1VSAA, P.CMTHVSAA, P.M23VSAC, P.M22VSAC, P.M21VSAC, P.M20VSAC, P.M19VSAC, P.M18VSAC, P.M17VSAC, P.M16VSAC, P.M15VSAC, P.M14VSAC, P.M13VSAC, P.M12VSAC, P.M11VSAC, P.M10VSAC, P.M9VSAC, P.M8VSAC, P.M7VSAC, P.M6VSAC, P.M5VSAC, P.M4VSAC, P.M3VSAC, P.M2VSAC, P.M1VSAC, P.CMTHVSAC INTO SORTED_P
FROM P
ORDER BY P.GEOSORT, P.TERR_CODE_IND, P.GEO_CODE, P.GEO_DESC, P.PRODUCT_LOGICAL_ORDER;
In order to do a report, I would like to make it so that each customer is on its own row within this query, with his or her total days added up. I have no idea how to go about it.
http://img329.imageshack.us/img329/6313/untitledao2.jpg
appreciate any help:o
I need to create a query for an error report.
What I need is a query that will find any duplicate records from field '1' in my table.
But then I want it to check another field '2' in the same table for any records that meet criteria 'A' but also have entries with a different critiera.
Not sure if I'm explaining that right.
So say we have a table like this
field 1..........field 2
XX.................A (the criteria we want)
XX.................C (oops, somone entered another entry with a different criteria)
So it would tell us that the 'code' in field 1, we have the criteria A and C at the same time. not good.
I hope that makes sense.
I have made a query where the user have to enter from date and to date, and the result will be between these dates. If I write, lets say, from 01.01.2008 to 29.02.2008 It brings the results for all entrys between these dates, But I wonder if it is possible to make two results for that one, one for january and one for february. (All in one query, just sorted after month) Or maybe in the report perhaps. But I have no idea if this is possible, or how to do this. I know how I can sort it after date with the same query, but I want the results per month. Would be very nice if I can get it listed in a report, so that I can print out one page with results for january, february, march etc.
View 1 Replies View RelatedI don't know if I'm going about this problem correctly. In a nutshell:
I have a table which contains a number of fields, only a two of which are important for this task, as indicated below.
| ID | Type |
Where ID is an alpha-numeric identifier (say a store number), and there are three potential types, plus Null values. For this, we'll assume the three types are Fruit, Vegetable, and Grain (plus Null).
An example dataset might look like:
0-4 Vegetable
1-2
0-1 Grain
1-2 Fruit
0-4 Vegetable
1-2 Vegetable
0-1
0-2 Fruit
0-4 Grain
1-2 Fruit
0-4
0-1 Vegetable
0-3 Fruit
If I want to know how many stores have Vegetable, I could put "Vegetable" as the criteria in the query and set UniqueValues to Yes. Then I would get three unique values, one for each Vegetable existing at a unique store.
But, what if I want to know at which stores Fruit exists and Vegetable DOES NOT. Is there a way within Access to find all the records with "Fruit" as the criteria and then eliminate those with ID numbers matching the dataset with "Vegetable" as the criteria?
In other words, if I do my "Vegetable" criteria I end up with the resulting dataset:
0-4 Vegetable
1-2 Vegetable
0-1 Vegetable
If I then use "Fruit" as my criteria, I get the following dataset:
1-2 Fruit
0-2 Fruit
0-3 Fruit
Since Store 1-2 has Vegetable, I want to eliminate it from the list so that my resultant list is:
0-2 Fruit
0-3 Fruit
Is that possible?
Sorry for the long bizarre example, but I thought it the easiest way to make the situation clear. I'd really appreciate any suggestions anyone has on dealing with this situation!!
Thanks
I have a query that draws from two tables, and the field in question looks like this:
X: [TableData]![FieldA]*[TableNumbers]![A]+[TableData]![FieldB]*[TableNumbers]![B]
It all works fine and dandy, but once I set it to sort by this field and run the query, it gives me the parameter prompt, asking me to enter the Parameter Value of FieldA and then for FieldB.
Is there a work-around for this within the query?
The only other solution I have in mind is making another table from this query, and then creating another query just for sorting said table, but that seems inefficient at best.
I have a query that I want to sort based on a table. (SS BELOW) I want the OS in qry2FINAL to be sorted based on Trn-OSstops table. So the OS column of the query should be sorted as which is the order shown in the table. I tried linking the 2 tables in my query but I had no clue what to do from there. Any help?MV6HP108PT337HP113 (sometimes there is an extra field, but can I make it show up last?)http://i10.tinypic.com/68hkwhe.gifEdit: I looked at my SS and it might be confusing. Ignore all of the records in the Trn-OSstops table except for the highlighted one.
View 14 Replies View RelatedI have a calculated date field in a query...if I try and sort by this field I get a data type mismatch.
[CONTREFF] is a date field in a table, [TERM] is a number field in a table. I am trying to calculate the year the contract expires in the "EndTerm" field. The calculation works fine, but I can't sort it.
EndTerm: DateSerial(Year([CONTREFF])+[TERM],Month([CONTREFF]),Day([CONTREFF]))
Please Help!!! Thank you ...
I am trying to create a work schedule, some customers are scheduled for morning and others in the afternoon. I am trying to figure out how to sort AMs and PMs so I get a line with every other one.
View 2 Replies View RelatedHi,
I need help with an access 2000 multiple queries problem.
I have the following:
Table 1 = info below / table 2 = computer types / table 3 = offices 1 through 10
field 1 = name (20 employees)
field 2 = computer (5 different type of computers)
field 3 = office (10 different offices)
field 4 = issued
field 5 = not issued
field 6 = stolen
On a form I have the 2 combo boxes (computer and office) to select and check boxes to select issued or not issued or stolen.
I need a report printed based on the multiple criteria query only showing the results that I ask for.
For e.g. in office 5, computer IBM, issued.
Print Report: lists names of all in office 5, with IBM’s, issued only!
etc.
Please help.
Below is the sql code I have for a CrossTab Query. Total as GrandTotal
shows GrandTotal of all weekly columns. Is there anyway I can get the
Query Display to sort on the GrandTotal Column?
PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory
ORDER BY Sum([Trends-1-3TON-WEEK].Totals)
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;
I have a created a crosstab query which gives me the results I need, but I want to sort the row headings differently. These are not numbers, but machine sizes which range from 4 Metre to 20 Metre. Currently, the crosstab gives me:
10 Metre
12 Metre
14 Metre
17 Metre
20 Metre
3 Metre
4 Metre
5 Metre etc
But I need to show it as:
3 Metre
4 Metre
5 Metre
10 Metre
12 Metre
14 Metre
17 Metre etc.
The field is short text data type and data comes from an ODBC linked table to SQL server table, and I am using Access 2013.
Basically i have a mortgage application system which im running a query on to see which applications have been completed in the last week or period up to now ie. last week, last month, last 3 months.
basically each application or CaseID has one or more applicants. The majority of them have two applicants. A few have one.
Problem is when i run this query which shows me: - CaseID, CLIENTNAME, LENDER, PRODUCT, MORTGAGE RATE, LOAN AMOUNT -
it brings up these fields but i only want one occurence of each CaseID????
CaseID Forenames Surnames PRODUCT RATEDateCompletedLoan required
2821NothandoDube71116.6920/01/2004£63k
2821Sipho Dube 711 16.69 20/01/2004 £63k
2909Hobbly Chise 726 15.69 31/01/2002 £164k
Get the picture? Both Sipho and Nothando are applicants on the same mortgage but i only want to show one name. Anybody know how to help me out???
Ive taken up this existing system, so i think there's possibly a normalisation problem thats causing this. But i need a quick fix for now rather than redesign the whole system.