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 Complete Forum Thread with Replies
Related Forum Messages:
Sorting Alphabetically & Numerically
I am trying to figure out how to sort a column in access the same way it comes out in excel. I have a text list of "buildings" ie: 89, 33, 270C, 270B, 270A etc. In excel this simply sorts alphanumerically ie 33, 89, 270A, 270B, 270C. I made the mistake of changing the format from text to number, lost all the data and successfully scared myself into not wanting to experiment anymore so any input I could get would be very appreciated
View Replies !
Sorting Months (not Alphabetically)
I have a series of subforms that show customer mix percentages by month. These forms will be used for data entry and to ease the process I would like to have months in order of our fiscal calendar, July through June. Right now the months are listed alphabetically. Is there a way I can list the months in order without having to resort to a naming convention such as: 01June 02August 03September ect. Thanks
View Replies !
Sorting Months (not Alphabetically) In A Form
I have a series of subforms that show customer mix percentages by month. These forms will be used for data entry and to ease the process I would like to have months in order of our fiscal calendar, July through June. Right now the months are listed alphabetically. Is there a way I can list the months in order without having to resort to a naming convention such as: 01June 02August 03September ect. Thanks
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 !
Blank Fields In Crosstab Query Based On Previous Query
:confused: I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing.... First goal: to COUNT the number of times a TYPE of visit is made. There are several different TYPEs but only interested in tracking 2 of them. When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact) The SQL was written by Access not by me. :) Here is an example of the Crosstab SQL (which is using a previous query): TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE SELECT qryTest2.CID FROM qryTest2 GROUP BY qryTest2.CID PIVOT qryTest2.TYPE; ----------- qryTest2 SQL: (Grouping by to remove dups) SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#] GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In")) ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME; Thanks for you time! :)
View Replies !
Crosstab Query Sometimes Loses Fields
Hi Folks Not sure if this should be in the Queries or Reports forum? I have a MakeTable Query which selects data between 2 dates to create the following Table. IncID, IncType, IncDate, AggType 15, Agressive, 23/02/2006, Verbal 23, Agressive, 14/05/2006, Threat 72, Agressive, 18/11/2006, Physical 89, Agressive, 20/12/2006, Verbal I then have a crosstab query that counts the different "AggType" like this. (This data is output to a report as both data and a chart) Verbal, Physical, Threat 2, 1, 1 This all works great IF the date range is large enough that all 3 AggType fields are included. BUT, I my date range was from 01/05/2006 to 31/05/2006, I would only have 1 record (23, Agressive, 14/05/2006, Threat ) so the crosstab would result in ONE field only. (Threat) The problem is that my report is looking for 3 fields (Verbal, physical and Threat) How can I stop the report from giving an error if fields are missing from the crosstab result? Thanks
View Replies !
Stopping Message Popups In Update Query
Hi again all, Im wondering how I can stop the messages that pop up when im about to run a update query, I get the following 1. You are about to run a update query blah blah.... 2. You wont be able to undo the changes this action blah blah 3. You are about to update X Rows Idealy Id love to be able to turn off the messages in just this query, and not globally over the whole database, can this be done? Also, Id like to get a error if there is one.. so only stop those message that are currently showing... Any help would be awesome, Cheers Ezy
View Replies !
Sorting Between Two Fields?
Hi, im new to the forums here and was hoping to find the help I need. Im working with a form that has two parts, in the main form it had combo boxes that have data from a table (example: Location, Supervisor, Date.). The child form inside this form displays the information according to the main form. Now my problems started when I was requested to have a search for "From and To" dates. Im not sure exactly sure how to do this. Any input? Thanks -Hz
View Replies !
Crosstab Query Based On Crosstab??
Hi all, I am utterly unsure if what I want to do is even possible: I have two crosstab queries, qryRewCOCredit and qryWrapCOCredit which show the changeover (CO) times for the specified machine when they are NOT zero. (all zero entries don't show up). There are many cases when there is a CO for the Rewinder on a specific day, but not for the Wrapper, and vice versa. I want to make another crosstab query which performs a calculation. To keep it simple: If (RewCOCredit>WrapCOCredit) Then 5-RewCOCredit Else 'WrapCOCredit>RewCOCredit 5-WrapCOCredit Please help!!!
View Replies !
Sorting Multiple Fields
I have a database with different "Categories": hotels, kennels, pet sitters, etc. I need to sort it so that I can export and print it. It needs to be sorted as follows: State|City|Category. Whenever I do a sort it ends up giving me all of the hotels first (by state and city), then the kennels (same way), and so on. I need them integrated so that within each City, first come the Hotels, then the Kennels, then the Sitters, etc., and then on to the next city. Is there an easy way to do this, without knowing SQL or other programming?
View Replies !
Calculating Between Fields In A Table And The Sorting
Below is an example of my table "Pricedata" include 03 fields: Stock Name ___Price________Status Date A________________10________ __1/6/2008_ A________________11___________1/7/2008_ A________________12___________1/8/2008__ B________________22___________1/6/2008_ B________________25___________1/7/2008_ B________________29___________1/8/2008__ C________________2____________1/6/2008_ C________________2____________1/7/2008__ C________________3____________1/8/2008_ D________________56___________1/6/2008_ D________________45___________1/7/2008_ D________________27___________1/8/2008__ I want to create a query like this: can calcualte the price change and % price change in certain period, and then sort in % change. This result should be like the table below: StockName Change in three days % Change in 03 days C 1 50% B 7 31.8% A 2 20% D -29 -51.78% Thanks all,
View Replies !
Search Alphabetically
I'm currently developping a database and in the process of re designing my queries for my users needs. one of these is to be able to search entries of the database determinable by their first letter (e.g. find all customers whose name begins with 'A'). Can anyone help me with how to do this. So far I have managed to create a 'Like' query, so the searches are not case sensitive, but i still need to make it search alphabetically. Any help, much appreciated Sam
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 !
Stopping Pop Up Window Of F12 Key
In order to prevent inexperienced users corrupting the design of the Forms, Reports etc. of my Access DB, I have unselected the relevant options at Tools – Startup and it doesn’t display the Objects window. However, I find that when I press F12 key, a pop up window appears with ‘Save As’ ‘Save ‘frm……’ To: As …..’ with OK and Cancel buttons. Though a user can save the form with a different name and this does not interfere with the proper functioning of the DB, I find this an irritant pop up window. Is there a way to stop this pop up window?
View Replies !
Stopping Email
I have a form with a command button which sends a message containing information from fields on the form. This works fine. Problem is, the user can use the "Email Button", when there are fields not filled in. Is there a way to stop the email message from being sent?
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 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 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 !
Stopping Database Being Copied
I have a customer who is concerned that their access database may be copied and used by a competitor. I have told him to set up passwrods for all users amd hide the system file which stores the password info so database won't open off site if copied. He doesn't want to do this though. Is there any other way, possibly by encrypting it? Regards :confused:
View Replies !
Stopping Duplicate Numbers
I've got an incrementing invoice number that is just a +1 of the last invoice number. Had a problem today where one user was creating an invoice and took a while to complete the record. As soon as the record was created the invoice number went in as 123 but the record didn't save until she had filled out all the form. In the meantime someone else started to create another invoice and ended up with 123 as well because the previous record hadn't saved. Does anyone have any suggestins how I can stop this from happening? I can't save the record straight away, which would be the obvious answer as they need to fill out quite a few required fields before the record is saved so that they don't have incomplete records. Thanks DBL
View Replies !
Stopping Number Rounding Up
G'day All, I have a table that allows for data to be automatically imported into it or manually entered. When data is automatically entered into the table the number field shows the number as a decimal, but when I manually entered data into the column it rounds it up or down to the nearest whole number. I have checked the the decimal place setting on the particular item and it is set to allow for 2 decimal places the format is fixed and the number itself is Long integer Any help would be a appreciated.
View Replies !
Stopping Queries From Rounding Off
I have two tables. Table1 has two monetary value fields (both set with auto decimal places) and table2 has one monetary field. Then I have an append query which appends all data from table1 to table2, except for the monetary fields which are added up and then appended into table2's single monetary field (which also has decimal places set to auto). The formula I use for this is as follows: Total Value: [Gross Value]+[Adj'mt Value] Problem is, the total figure appended to table2 is rounded off to the nearest integer, whilst still displaying decimal places (45.00, 201.00 etc). Is there some sort of variable to set in the query to stop it from rounding off the result of this formula? Any help would be appreciated. Thanks.
View Replies !
|
TRACKER
