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 Complete Forum Thread with Replies
Related Forum Messages:
PivotChart Report
I have a report with an embedded PivotChart. When I export this report, the PivotChart can’t display in MS word file. How should I do to export a report with a PivotChart to MS word file format? Thanks
View Replies !
PivotChart Only On First Page
Hi, I have a PivotChart embedded in a Report using a master/child relationship (to the field [Course ID]). The chart works fine on the first page of the report, showing just the data for the first course. However, on subsequent pages there is no chart shown at all. If I remove the master/child link then the summarised chart appears on all pages. So whats going on? - I'm sure this worked a couple of days ago. Not sure if it failed when I ran an Office update in the last few days. The PivotChart is a form used directly on the main report. The main report has a [Course ID] Header and footer. The PivotChart form is in the detail section. Access 2003, recently updated, on XP. Thanks, Darren
View Replies !
Creating Custom PivotChart
I am trying to make a special kind of blob chart. It has to mimic the charts I have attached. I have a high number and a low number for everything that someone might want to put on the axes. The eventual goal is to have a form were the user selects what goes on each axis, and have a few other possible choices to limit how much is displayed. What I am making doesn't have to be as fancy, flexablity is more important. Can Access make this kind of graph or am I on my own, making shapes in VBA? http://www-materials.eng.cam.ac.uk/mpsite/interactive_charts/energy-cost/generics.jpg http://www-materials.eng.cam.ac.uk/mpsite/interactive_charts/energy-cost/metals.jpg
View Replies !
Rolling Average In PivotChart
I have a multi-pivotchart I am using to display data. Each chart shows a score for the month (taken from the date field). What I would like to do is display the three month average as a fourth column in each chart. (or at least the average alone since I only show 3 months anyways) i.e. jul, aug, sept, ave I have an average column under grand total in pivot table view but I cannot seem to translate this into a pivotchart that displays what I describe above. I do know this is far easier with MSGraph but I want to use pivotcharts for various reasons. Any help is greatly appreciated.
View Replies !
PivotChart Export Into HTML
I've a query that gathers data from another db via ODBC. The query takes a fairly long amount of time to run. The query outputs a PivotChart. I want to save/export this PivotChart as HTML and load it up on a webpage. I could probably do this by saving as a DataAccessPage but that would mean running the query at the time of accessing the website. So, I want to avoid this long delay by saving it as HTML. I also want this HTML PivotChart to have the ability to "pivot" the categories by filtering/removing/adding categories that you typically do in a regular PivotChart. The 'Save As' option does not have the HTML option. Exporting to HTML only exported the text data but not the PivotChart itself. I'm very certain this can be done. I know this can be done because I had done it once but now can't remember. Can anyone help?
View Replies !
Queries - PivotChart View
I am made a query, but I want the query to always open up in the PivotChart View...how do I do that? Even simpler: I want to have a button created on the initial form that opens when database starts, which will then open the pivotchart view with the person having the ability to drag items in the chart field list to create a chart to their liking, holding the data accordingly. Please Help!
View Replies !
Why On Earth Is My Access PivotChart Doing This?
I've written a couple queries in Access that create a BOM for components that we sell in our company. The data is withdrawn from our company system into a pyramid structure (MACOLA). Basically, all I did was recreate the "Indented Bill of Materials" report from our system. However, when I try to run the data through a PivotChart Form, it truncates all the data (i.e. if I need 16 of one component to make another, the chart will display 16 as it should, but if I need 4.75, it will display 4). The data in the final query where the report is drawn from is correct. It displays numbers both whole and decimal. I've looked everywhere in the properties to try to eliminate the problem, but I haven't found anything. How do I fix this? P.S. I've developed other PivotCharts that don't have this problem. I've even rewritten the queries using a different approach with the same results. Any Ideas? Thanks in advance.
View Replies !
Pivotchart Report First Record Only
I am trying to follow this Microsoft Tutorial and it worked when I put the subform (pivotchart) into a form but not when I put it into a report. The report shows the pivotchart correctly filtered for the first record but the chart is blank for the rest of the records.
View Replies !
Pivotchart With Series On Different Scale Axes
Hello all. My client has decided they like the pivotchart format better than the regular chart format, especially for its dynamic quality ( it can expand and contract with form adjustments. One chart I am having trouble replicating in pivotchart format is one that plots two data series. One series is made of columns representing large numbers, generally several hundred or thousand, and the other data series needs to be a line which usually ranges between 0-10. Obviously they cannot be represented on a y-axis with the same scale. This is simple to handle in MS Chart. Is there a way to make a pivotchart with a dual-scaled y-axis, and map a data series to each one? Thanks in advance.
View Replies !
Using PivotChart To Display Survey Data
I have this pivot chart (please see attached screenshot) that I was hoping to use to display results of a survey that was conducted by our nonprofit organization. I have it filtering by a few demographics (age, gender, parental status), and then the results of a few other questions below it. My problem is that I'm not sure what results I'm looking at -- whether they're totals of all the responses, just the "true" responses, or...? The survey asked questions similar to what you see in the field list. Each question is a column header in my table. Then the row headers are each respondent. The choices for answers were "true", "false", or nothing. So in all the cells, i entered t, f, or nothing accordingly. I'm not sure if I'm not using the pivot chart correctly, or if I didn't design the table for the data correctly.
View Replies !
PivotChart Category(x Axis) As Variable
Hi all, I want to create a pivotchart from a query, embed it in a form and use a combobox in the main form to allow the user to select the x axis value. So in a query with fields city, state, country, etc the user would be able to chose one of those fields as the category(x axis across the bottom) to display data. Is this even possible? If it is, how do I do it? As it is now I can add multiple categories to my chart- but it just makes a big mess of the chart. I can embed a chart in a form and use a combobox- but it allows me to choose from within a field- but not from among fields. Also- how do I access the code to manipulate the chart? Thanx in advance
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 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 !
Form(pivotChart) Save As Data Access Page
Hi, I tried to save two Forms (PivotChart) as Data Access Page, the first one works , but the second one just displayed an empty graph (two axes). The forms were generated from two different queries which from the same tables. Any ideas how to solve the problem? Thanks! glenn
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 !
PivotChart Decimal Place On Fixed Number Format On Averaged Field
I have a pivotchart which I cause to take a huge list of people's test percentiles and calculate the average for each year. I then display the average above each bar. The problem is that there are too many numbers after the decimal place for each figure. I went into the properties and changed the number format to "fixed" but it doesn't give me the option to change the number of decimal places that are used (it does every where else in MS Office!). It seems that it just uses the regional settings for the computer. But the problem I have in changing that is that I have other pivotcharts in the same report that does something similar but needs a different number of decimal places. Is there any way around this without having to create another query to calculate the average and then having the pivotchart display the info from the new query (this is a huge database that I put together with many charts and I don't want to have to redo the way that it works just because of this decimal place issue)?
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 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 !
|
TRACKER
