To 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 RepliesHey all! This is my first post. Been searching through the net all day trying to find a solution to this problem. Basically i have a table that looks like this (regular text is what i have and bolded text is what I need:Name Date Qty MOBrad 12/12/2007 23323 4423John 12/11/2007 3445 4432 John 12/11/2007 344 4432 John 12/11/2007 45 4432 John 12/11/2007 44 4432 John 12/11/2007 3445 4432 Grand Total: (Qty)And then I'd like to be able to carry this over and display a grand total at the bottom of every page of a report that I would need to generate. Our company produces forms and we sometimes have 60 - 70 people working on a single job. We want to see their hours individually but we would also like to see a grand sum of all their hours. If someone could help with this or needs more info let me know. Thanks for all your help!
View 14 Replies View RelatedIs it possible to set up a view within an access app to show a number of different "grand totals", related to one or more tables. For example, I'd like to display the total value of all orders, the total number of orders and the total number of items ordered, on a single view. In this case, all the information comes from one table - Orders, which contains fields - Total Cost and Quantity. I've tried to create a Summary view, but that requires me to use the "Group By" function, which I don't want here, so I tried to do it with queries, but it seems to involve multiple queries, which then means multiple views.
View 2 Replies View RelatedI have a very simple query on an accounts form to show a running transaction history.
Identifying from the TransactionID (shown for display purposes only - normally hidden) three or four postings make up one transaction.
Using TransactionID 10 as an example, I'd like to have a sum of total [Credit]-[Debit] and have the query display on one line (either at the top of £1,429, or at the bottom of £16,995) to identify this is in fact one transaction, having three posts.
Transaction 9 will have obviously have one total, as this is a single post.
11 the same as 10, by having one total Transaction Value either at the top of the row or bottom.
I 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 have the following tables:
tblInvoice
tblInvoicePrePayments
They are related with a one-to-many relationship. The related field is "InvoiceId"
I want to create a query that shows selected fields from tblInvoice and the sum of field "PrepaymentValue" from table tblInvoicePrePayments.
I want to be able to edit the fields from tblInvoice in the query.
If I create a totals query (qryInvoicePrePaymentsSum) on tblInvoicePrePayments, using the fields InvoiceId and PrepaymentValue, and then create another query with tblInvoice & qryInvoicePrePaymentsSum, the fields in tblInvoice cannot be updated!
Is there a way to append a pivot table to a table or possibly make a query based on a pivot table? I need to get a count of Part Numbers and I need the average price for all these parts. Additionally I want to ignore a count of less than 3.
Also I am having trouble filtering on the count in the pivot table... haha, so I was gonna Query on it later on.
I've recently been building a database on Access to replace the rather clunky and slow one we currently use here (built in Excel).Generally, I've been making it up as I go along which has worked quite well for me so far. However, I've encountered pivot tables. Normally, it shouldn't be a problem; the tables themselves are easy enough to understand. However, I've found that the tables aren't nearly as flexible as I would have liked. At least, not in a way directly apparent to myself.
(After trying, and failing, to include links to Imgur with details on my issue, I have included a .zip file with both images I was going to show)Generally, in Excel, the table works everything out for us, then we sort by largest to smallest and work out the totals for the last 7 days and the last 7-14 days (the week before last). After that, the last formula works out a percentage (loss or gain). [excel.png]
In the access table, though, I can't find a way to make this happen. There's no readily apparent button to add a new calculation field. I've tried using the create calculated total button, but that seems intent on making a separate total for each day on the table, which I don't want. [access.png]
Also, I can't figure out how to sort the records by grand total descending. I would have thought right clicking the grand total and clicking sort descending would have done it, but apparently that would be far too easy. I feel that it's prudent to mention that I'm not all that experienced with Access. I completed MOS and ECDL courses when I was in primary school, but it's been a really long time since that and all of the Office applications have changed rather dramatically.
I searched the internet and this forum for the answer to this. I did find the exact question posted in this forum way back in 2002 but there was no reponse...
When updating an Excel Pivot table using an Access Union Query as it's source, I receive the message "[Microsoft][ODBC Microsoft Access Driver] Too Few Parameters".
I found a rather elegant solution here (http://groups.google.co.uk/group/microsoft.public.excel.querydao/browse_thread/thread/1ca76034adc10c1a/204261bda38c118c) Unfortunately, this appears only to work for Access 2003. Does anyone have a solution for Access 2000?
Perhaps I should insist that our IT department upgrade. :p
Thanks in advance for any assistance.
- Matt
I need to import data in this format:The top row of numbers are AttributeIds .In order to import it into my DB I need to convert it into the following fields and records.Any fields that are null should not get a record in the conversion.
View 3 Replies View RelatedI have a PivotTable that I am trying to put together that will give me the following:
Types of Payments - Left
Accounts Where Payments came from - Top
Amount of Payments - Data
I want it to look like a spreadsheet where it will show me all the Types of Payments even though I don't have any data in there. Almost like an Excel Spreadsheet but I want it in Access. I was able to get the Columns to show up even though I had some blanks but now I need the Rows to show up.
I have the following (simplified) normalised data table:
[Account] [SrcUD2] [Amount]
col1 row1 1000
col1 row2 500
col1 row3 500
col1 row4 1000
col1 row5 1000
col1 row6 0
col1 row7 1000
col1 row8 1000
col1 row9 0
col2 row1 100
col2 row2 1000
col2 row3 200
...
I use the query below to pivot the data into the right format for export:
Code:
TRANSFORM First(Amount)
SELECT SrcUD2
FROM source
WHERE LocName="myLoc" AND Entity="LE01" AND PeriodName="QA - 2014" AND ScenarioName="Actual"
AND (Account="col1" Or Account="col2" Or Account="col3" Or Account="col4" Or Account="col5" Or Account="col6" Or Account="col7")
AND (SrcUD2="row1" Or SrcUD2="row2" Or SrcUD2="row3" Or SrcUD2="row4" Or SrcUD2="row5" Or SrcUD2="row6" Or SrcUD2="row7" Or SrcUD2="row8" Or SrcUD2="row9")
GROUP BY SrcUD2
PIVOT Account
which yields the following table as the query result:
SrcUD1 col1 col2 col3 col4 col5 col6 col7
row1
row2
row3
row4
...
which is great except that I want to transfer the results to Excel using the CopyFormRecordset method without the metadata of column 1 (row1, row2 etc.
I have a database with an import process which normalises incoming data and appends to various tables. No issues with that. I also have a function within that process which counts the number of new entries for a summary popup when the process has completed.This works by querying the staging table, prior to the append, into a recordset and using the .RecordCount to increment the count (multiple files can be imported at once so this effectively provides a running count, per file, to give a total for the whole import)
I thought it was working fine but this morning I noticed that the count which appeared on the popup was 1 greater than the number of actual new records. I checked the source files and noticed that, for whatever reason, there was a duplicate entry in there. So I presume that's why the count was out by 1.
There's no integrity issue in the main tables as the composite primary keys ensure that duplication shouldn't be a problem. Indeed, the record in question, duplicated in the source, appears only once in the main table post-import. So not too worried about that.
However, I need the count in the popup to be accurate (it tells the users how many new entries require further investugation). And what's puzzling me is that I use DISTINCT in the query, which I would have thought should eliminate any potential dupes in the recordset and thus provide the correct count. It seems it doesn't?
Code:
Public lngNewBalancesTBI As Long ' Defined in a separate module...
-------
Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String
[code]....
Why the dupe, which is still present in the staging table, also makes it over to the recordset, even though I'm using DISTINCT?
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
I'm creating a job/timesheet database and have a problem...
I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.
There are then various tables that support the main table, which provide actual employee names etc.
I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.
The way I was going about this was
- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.
- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.
Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.
Ive 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
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
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
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.
After you "group by", is there a way to have the next field presented without a sum or avg etc.. if I know there is only ever one value to present?
View 1 Replies View RelatedI'm trying to build (a rather simple) totals-query, but the "last" function doesn't give me the right values.Suppose I have a database where the amount of products that were sold are stored per month. It's possible that some products are not sold in some months. Now, I want the LAST REAL VALUE (= amount) for each product, no matter what month :
prod1 = 4
prod2 = 3
prod3 = 3
How can I do that, because the LAST-function gives me
prod1 = (empty)
prod2 = 3
prod3 = (empty)
I have a number of querys which search by month and year, I want to be able to sum up 3 of the querys together and display this in a text box on a form... I have tried dsum etc but I can't seem to do it...
So basically I want the totals which are dsumed into the same form to calculate together in a different query if required... This would be simple except one of the months if from the previous year...
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.