ok, i am using pivot in a query. this returns 24 values, one for each hour in the day. i need to sum the values of the first 12 items returned in the query. i need them in the same query. how can this be done? the column is names from 1 through 24, 1 for each hour..
View RepliesIve got a query (SearchCriteria) whose data looks like this:
Call ID....Problem Source............Problem Source2
15366....Complaint....................
15224....Collateral Order............Complaint
15734....Delivery.......................
15733....Delivery......................Order Taken
15738....Delivery.......................
15137....Complaint.....................
14238....Other.........................Delivery
15072....Complaint....................Delivery
What Im trying to do is create a query on the back of the one above which will have Problem Source in column 1 and then 2 more columns with their counts in them. So:
Problem Source......CountOfProblem Source...........CountOfProblem Source2
Collateral Order................1............................ ..................1
Complaint.......................3................. ..............................1
Delivery..........................3............... ...............................2
Other.............................1............... ...............................0
Ive created a Query with this SQL:
SELECT SearchCriteria.[Problem Source], Count(SearchCriteria.[Problem Source]) AS [CountOfProblem Source], Count(SearchCriteria.[Problem Source2]) AS [CountOfProblem Source2]
FROM SearchCriteria
GROUP BY SearchCriteria.[Problem Source];
but this gives me:
Problem Source......CountOfProblem Source..........CountOfProblem Source2
Collateral Order.................1........................... ..............1
Complaint.........................3............... ..........................1
Delivery...........................3.............. ...........................1
Other..............................1.............. ...........................1
I guess this is because it's filtering on the first Problem Source and then looking for non-blanks in the second Problem Source which isnt what Im trying to do!
Any inspiration greatly appreciated as always...
I have a query I saved which pulls data and a form that creates a pivot table based upon the query.
Is there a way to create a query based upon criteria such as dates to limit my recordset? I'm trying to set date values in a form and update my saved query but I cannot figure it out. Any help would be appreciated.
Here is the SQL I use to create the standard query. What I plan on including through vb is a "Revenue_Date" variable "FROM" and "TO" date in the code to limit the output based upone the dates entered.
SELECT PARENT, TYPE, SUM(TOTAL) AS COMBINED
FROM [
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'1. IND_Amount' as TYPE,
IND_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'2. SBG_Amount' as TYPE,
SBG_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'3. IND_Bonus_Amount' as TYPE,
IND_Bonus_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'4. SBG_Bonus_Amount' as TYPE,
SBG_Bonus_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'5. Licensing Fees' as TYPE,
Licensing_Fees AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'6. IND Misc Expenses' as TYPE,
IND_Misc_Expenses AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'7. SBG Misc Expenses' as TYPE,
SBG_Misc_Expenses AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'8. Other Receivables' as TYPE,
Other_Receivables AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
UNION
SELECT
tblStmt_Tracking.Parent_Carrier_Name AS PARENT,
'9. Unknown_Amount' as TYPE,
Unknown_Amount AS TOTAL
FROM
tblStmt_Tracking INNER JOIN tblCheck_Log ON tblStmt_Tracking.Check_Assignment_ID = tblCheck_Log.Check_Assignment_ID
]. AS BREAKOUT
GROUP BY PARENT, TYPE
ORDER BY PARENT, TYPE;
I have set up a query to filter information between 2 dates using
Between [Start date] And [Finish Date] to filter the info. I'm using office 2003 which allows me to set various pivot charts based on this info with no problems at all. unfortunatly for me several other people are using office 2000 which doesnt have the same chart functionality. so i'm setting up another switch board with 2000 type charts. Right my problem as long as I dont have the Between [Start date] And [Finish Date] codes in my query I've produced the charts I require with no problems. As soon as I add the filter into the query when i go to edit the chart it comes back saying problems updating data. This I presume is because the query wants an input. Is there a way around this?????
I've spent hours searching for this, hopefully one of you can brighten my day!!!!:D
many thanks
EQ
Hi Forumers...
I have data in a table that looks like this...
SITE-ID | VALUE
Site 1 | 20
Site 1 | 21
Site 1 | 16
Site 2 | 8
Site 2 | 9
Site 2 | 12
etc...
I would like to create a query that allows me to show summary statistics for each site. eg...
SITE-ID | VALUE_MIN | VALUE_MAX | VALUE_AVG
Site 1 | 16 | 21 | 19
Site 2 | 8 | 12 | 9.667
etc...
Am relatively new to MS Access and can't work out how to create a query that does this. Any help will be appreciated.
Using MS Access 2000 (9.0.7616 SP-3) on Windows 2000
Thanks,
Chris Medlin
I have a simple crosstab query. I have source as the row heading (basically source is just string category field) and Create_Date as the column heading. This gives me a count of the tickets for each source by day.
My Issue is that when Access breaks my Date/Time field into the column heading (Format([Create_Date],"Short Date")), it must turn the date value into a string, then sort that way, because my columns come out as so:
1/18/20081/19/20081/2/2008 1/20/20081/21/2008
Notice that 1/18/2008 comes before 1/2/2008… so it is ordering the columns as a string.
How do I get Access to order the columns by date?
Thanks,
Gary
Can anyone help me convert this pivot query to work in sql server please?
I'll love you forever if you help me please :pTRANSFORM Count(Employees.MaritalStatus) AS MaritalStatusCount
SELECT Employees.MaritalStatus
FROM Employees INNER JOIN Offices ON Employees.OfficeId = Offices.officeId
WHERE ((Not (Employees.MaritalStatus) Is Null))
GROUP BY Employees.MaritalStatus
PIVOT Offices.officeLocation;
Can you do a pivot table from a query in Access 2000? I found links that shows new features in Access 2003 that allows it, but no definitive information if it can be done from Access 2000. My tables can pivot fine, but no luck on my queries? If not, is there an easy way to get a query to a table in order to do a pivot table?
View 1 Replies View RelatedSo, I've recently learned that Access 2013 took a step backward and got rid of PivotTables. Unfortunately for me, PivotTables were perfect for summary reports I have to create with large data sets (some with millions of records).
What are my alternatives to PivotTables within Access 2013? I was playing around with the report builder, but it seems to retain all line items and doesn't allow me to collapse everything into a one page report.
Hello,
I need to export a Pivot Table view to Excel. The issue is with Calculated Total columns which are not exported in excel. Do you know if there is any way I can export the Calculated columns in excel?
Thank you.
So I have a code that opens a query but I want the PivotChart to show. Below is my code related to opening the query.
If vartyp = 0 Then
varQueryName = DLookup("Query", FileName, Criteria)
If IsNull(varQueryName) = False Then
DoCmd.OpenQuery varQueryName, acNormal
End If
I was just wondering if I could use a query and table to create pivot chart? and If so how I could do this? I need information from both in order to create the chart I want.
View 1 Replies View RelatedI have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies View RelatedTo keep it short and sweet, my query set up is: Employee, Team Name, and then I have the same field in the column as I do in the data and it presents pass/fail data. I have the data shown as a percent of the row so that there is a success rate shown but I want to remove the grand totals from the rows because it is obviously going to be 100% every time and it's unnecessary.
View 13 Replies View RelatedI have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
View 1 Replies View RelatedI 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 form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).
I have 3 queries which count how many patients come in 5, 4 and 3 times/week.
In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".
I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.
(I'm not using SQL view, I'm using the query design view)
In the "update to:" row, I use the Build function and locate the count I'm looking for.
Problem: when I run the query I get the error: Operation must use an updateable query.
Here's my problem. I need to generate a report that says how much of each individual product was produced and as well as the total produced for a specified category in a time period. Something like the following:
05 Catagory A: 02 Product AA, 01 Product AB, 02 Product AC
10 Category B: 07 Product BA, 03 Product BB
04 Category C: 01 Product CA, 01 Product CB, 01 Product CC, 01 Product CD
etc...
I currently have a query that queries a database and pulls out all products that were produced in a specified period and the categories they belong to and dump them into a local access table. Now what I need to do is search through the query results and count up how many of each product were produced (02 AA, 01 AB, etc...) and the totals for each category. The number of categories is pretty limited (6), but there are hundreds of product codes, so I need a way to do this without having to type in each induvidual product code as the requirement by which the query searches. Also, the product codes that get returned are different every day.
I was thinking something along the lines of take the product code of the first row and check for any others in the results that match and write that into another table. Then move onto row 2 and use its product code as a search parameter and search through the query results for any matches. Then continue that until the end of the query results. Can I do that? Is there a better way to achieve what I need?
I am trying to filter a form to show the entire weekend's activity on Monday but only yesterday's activity Tuesday through Friday. Using this code I can return Friday's results on Monday and yesterday's for the rest. How do I get the range Friday to Sunday?
IIf(DatePart("w",Now())=2,Date()-3,Date()-1)
Using >Date()-3 doesn't work.
Thanks
Bruce
I have a query that displays results in a form, but if the query is null, I want to display a different form, or just an error message that says something like "your query returned no results" (right now it will display the form with no fields)
I am a beginning Access/VBA user and have searched and browsed the forum for combinations of null/query/form, but haven't found what I need. Can anyone point me in the right direction?
Thanks for any help.
I am wanting my query results to appear in my main form so that i can edit them, rather than a table the query is just a look up of my form that stores about 500 records and stores all the same fields
any help would be appreciated
thanks
Is there a way to write one query that will work for different criteria and is run by different buttons to produce bar graphs or a basic report depending on the button the user clicks??
View 5 Replies View RelatedHi all :o
I am working on a query but can manage to get it to do what I want.
I have a main table with a job_ID and a faults table which documents faults that occur related to that Job_ID the tables are related with a one to many relationship. I want the query to bring up all the jobs even those with no faults, at present it is only bring up those jobs that have faults, how do I include details of all the jobs and if there are no faults then that field is left blank, probably very simple to do but after working on this db for hours my brain is no longer functioning!! :eek:
Thanks
Jackie
Hello all,
I have a table called tblODF and within that table, I have these fields:
- ODFNumber
- ODFScanDate
- Status (Combo box: Pending, Complete, Licensing)
- LastFollowup
In my query, I have the same fields, except I added a calculated field.
I added DayCount to
Codecalculate:(Date()-[ODFScanDate]
This should calculate the number we've had the ODF.
What I want to happen is, let's say the status is Maturity, I want the DayCount to say 0. If it's pending, I want it to calculate the actual days.
Is this possible?
Thanks