Queries :: Add Comparatives To Grouped Totals Queries
May 23, 2013
I have a totals query of phone call charges by phone number and I want to show a "budget" amount for each number (same amount for all) and a variance against that budget.
How I can do this at the grouped (phone number) level rather than at the individual call level?
Please see current query below:
SELECT Call1CurrentTbl.電話番号（MSN）, Sum(Call1CurrentTbl.料金) AS 料金OfSum
FROM Call1CurrentTbl
GROUP BY Call1CurrentTbl.電話番号（MSN）, Call1CurrentTbl.[レコード区分], Call1CurrentTbl.表示区分
HAVING (((Call1CurrentTbl.[レコード区分])=2) AND ((Call1CurrentTbl.表示区分)=10));
I have simple table data structured as follows;[Origin], [Code], [Weight]. The Code field is a lookup field that will contain one of 8 choices; 10, 13, 13c, 23, 25, 27, 27a, & other. The other fields are pretty much self explanatory. Sample data would look like this:
What I need to create is a totals query where I first group by origin value, then a column for each "code" value which totals the weight for that "code". A sample output would look something like this:
The only way I can think to accomplish this is to restructure the original table to include a field for each code and enter the weight in the appropriate 'code' field. If this is the only solution then Ill have to live with it, but is there any way to create this output using my original structure?
Invoice Number | Customer Name | Item | Item Cost | Invoice Date | Paid | Date Paid
Example of data:
AK001 | A Brown | Blue Car |1000 | 1/4/2013 | Yes | 20/4/2013 AK001 | A Brown | Red Car |2000 | 1/4/2013 | Yes | 20/4/2013 AK001 | A Brown | Yellow Car |500 | 1/4/2013 | Yes | 20/4/2013 AK002 | A Brown | Black Car |1000 | 7/4/2013 | Yes | 20/4/2013 AK003 | B Smith | Blue Car |1000 | 12/4/2013 | Yes | 25/4/2013
I want to create a report from this table that outputs as:
Invoice Number | Customer Name | Total Price | Invoice Date | Paid | Date Paid
Example of report from Example Data:
AK001 | A Brown |3500 | 1/4/2013 | Yes | 20/4/2013 AK002 | A Brown |1000 | 7/4/2013 | Yes | 20/4/2013 AK003 | B Smith |1000 | 12/4/2013 | Yes | 25/4/2013
Is there an easy way to do this.. or will I need to make a new linked table with the invoice number as a lookup?
I am having trouble writing what I think should be a fairly straight forward query... I am have a table with 2 fields, the first contains a list of subjects, the second contains a list of scores that students got in their tests in that subject.
So the data looks like this:
Maths 54 Maths 69 Maths 41 English 71 English 55 Art 44 Art 43 Art 66
I would like to write a query that selects only the highest score for each subject and presents it like this:
Using Access 2010. Have been using a simple query to count similar items. All of a sudden I cannot get the sum of the count. I don't know if I have done something wrong or my program won't work correctly.
The DB is attached. The only query shown is an example of what I want to total, but I cannot get any total.
I have a database that tracks students and their test scores. I am trying to figure out a way to where it will pull the student's latest test score and compare it to see if they fall within standards. So far I have 3 tables.
The query I have written looks like this: SELECT tblStudents.studentID, Max(tblResults.resultDate) FROM tblStudents LEFT JOIN tblResults ON tblStudents.studentID = tblResults.resultStudent GROUP BY tblStudents.studentID." The data pulls just fine at this point. What I'm trying to figure out is how to then add more columns to this query to start doing comparisons. When I try to add more columns it tries to use them for additional grouping and adds many more records.
I got this far: SELECT DISTINCT count(a.ID) as mCount, year(dimDate.Date) as [Year], month(dimDate.Date) as [Month] FROM admin AS a INNER JOIN dimDate ON (dimDate.Date >= a.sDate AND dimDate.Date <= a.eDate) OR (dimDate.Date >= a.sDate AND dimDate.Date = null) GROUP BY year(dimDate.Date), month(dimDate.Date) ORDER BY year(dimDate.Date), month(dimDate.Date)
these are then used as the start of a between lookup against [tblMaster] along with the last record in the qryPeriod (2014 - 04)...I will need 4 queries so If someone can sort out last period - 8 I can probably do the others.would DLookUp do this say return the last 4 values, Last 6 Values and Last 8 Values
I have a rates table with 3 fields : [RateDate], [CurrencyID], [FXRate]
I also have a currencies table with 2 fields : [CurrencyID], [CurrencyCode]
Where [CurrencyCode] is just the 3-character currency code (i.e. EUR, GBP, USD etc.)
The rates table uses a composite primary key across [RateDate] and [CurrencyID] - i.e., there should only be one unique record for each combination of date and currency.
Not every date will be present in the table and, within each of those dates, not every currency will be present (but at least one, obviously)
I want to write a query which will return two fields. The first field will contain all of the unique dates in the table for which the [FXRate] field (for any currency) is blank / null. Straightforward enough :
Code: SELECT [tblRates].[RateDate] FROM [tblRates] INNER JOIN [tblCurrencies]
[Code]....
But this gives me a separate record for each date / currency combination in the returned dataset. I just want one record per date and all of the currency codes in a single string (separated by some delimiter) as a text field.
I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.
The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.
PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))
I have a table of transactions (close to 1m records) from which I want to query totals by currency and direction (ins and outs) and then also show the proportion of the overall total for each currency. All amounts are absolute (i.e. ins and outs both represented by positive numbers)
To throw some added complexity into the mix, the transaction table records only refer to account (AccountID), which links to a separate account and currency tables.
I can query the sum totals by currency and direction (although I'm not totally happy with the SQL syntax .
I'm struggling to get the proportions per currency though? I tried adding :
Code: ((SumPayUSD+SumRecUSD)/Sum(tblTransactions.USDAmount)) AS Proportion
But it tells me that the specified field 'tblTransactions.USDAmount' could refer to more than one table listed in the FROM clause of the SQL statement.
I currently have a query of between dates which the user enters, but when I try to get a total count of model numbers it gives totals for each date. I am trying to get a count of model numbers between these dates with the dates excluded in the grouping.
I have a registration form where a person can select if they will attend any or all of 3 events. I want to create a report that will say x registered for event 1, y for event 2, and z for event 3.
I have 3 queries which seperate the people by whether they are attending event 1,2,3. In these queries I have CountofID field so that I can sum it.
When i create a query that Sums the CountofID field for each one, it multiplies the values. I played around unsuccessfully with crosstab queries, but to no avail.
I have two tables, joined together with a Union query, and what I'd like out of it is a Sum of Categories & Sub Categories, based on all days this year. When run on their own, they give me exactly what I need, but when used with UNION ALL, I have duplicate "Sub Category" Entries...
One from one side of the query, and one from the other..Is there anyway this can be put on just the one line?the SQL I'm using is this...
Quote:
SELECT tblTopLines.Category, tblTopLines.SubCategory, Sum(tblTopLines.Credit) AS SumOfCredit, Sum(tblTopLines.Debit) AS SumOfDebit FROM tblTopLines WHERE (((tblTopLines.TransDate) Between #1/1/2015# And #12/31/2015#)) GROUP BY tblTopLines.Category, tblTopLines.SubCategory HAVING (((tblTopLines.Category)="Car")) UNION ALL SELECT tblSplits.Category, tblSplits.SubCategory, Sum(tblSplits.Credit) AS SumOfCredit, Sum(tblSplits.Debit) AS SumOfDebit FROM tblSplits WHERE (((tblSplits.TransDate) Between #1/1/2015# And #12/31/2015#)) GROUP BY tblSplits.Category, tblSplits.SubCategory HAVING (((tblSplits.Category)="Car"));
As I said, SQL isn't my thing, but every stone unturned is another feather and all that..
I'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...
I have a table for timesheet entry for a local building firm. I have a separate table containing employees and rates. I have created query "Qry_ Time Costs" which calculates the cost of hours worked by each employee by multiplying the hours field in the timesheet entry table by the rate field in the employees table.From "Qry_TimeCosts" I have created "Qry_TimeCosts1" in which i have included a running total field for time costs per day using the DSum function.
SELECT Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], Sum(Qry_TimeCosts.Hours) AS Hours, Sum(Qry_TimeCosts.Cost) AS Cost, DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "") AS RunTot FROM Qry_TimeCosts GROUP BY Qry_TimeCosts.[Project Title], Qry_TimeCosts.[Build Element], Qry_TimeCosts.[Date Worked], DSum("[cost]","qry_timecosts","[project title]='" & [project title] & "'" & "AND [build element] ='" & [build element] & "'" & "AND [date worked] <=#" & [date worked] & "#" & "") HAVING (((Qry_TimeCosts.[Build Element]) Is Not Null));
This seems to be working for some projects and not for others. In particular dates 3rd 4th and 5th of June seem to be showing null fields, where all other dates have values. A few projects are showing inaccurate running totals whilst others are working fine.
I want to sum the total of all the purchase prices together so I know the total value on order. I keep getting an error: "You tried to execute a query that does not include the specified expression Purchase Order' as part of an aggregate function
I have a parameter query with a totals row that displays averages. Is there a way to have the average row use only specific records in its calculation based on one of the field's values WHILE still displaying all the records returned by the query.I want only data that has a "YES" value used in the average while still displaying the records marked as "NO"
The data within my tables is formatted correctly and when I run a standard query on the data, it comes through the query with the same formatting. However, when I run a query that needs to total the values (whether it's sum or average) the values lose any formatting (and by total I mean the one in design view, not in datasheet view).
I then need to manually format each columns "Format" and "Decimal Places" properties to what I want. I have quite a few queries with quite a few columns, so this is very time consuming. Is there a way to do this faster without VBA? In Excel I can simply highlight multiple columns and format all of them or apply a format painter. I don't see any similar functionality in Access 2010.
I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:
Field 1; Field 2 Jones; 200 Smith;150 Jones; 300 Smith;100
In this example, I need the sum of field 2 for the records where Jones is the value in field 1.
So the result in this case is that records where Field 1 = Jones, Field 2 = 500.
I am trying to create a Totals Query which returns a data set between two dates. So far I have managed to select the data I want (Please see attached screenshot). However, I only want to select records between a date range working on my field [DueDate]. If I add the due date field to the current query then it removes the grouping and all records are displayed.
I have a main report with 5 sub reports. There is a detail page for each company and a totals page at the end. The record source for each sub report is a Union query (combines the detailed information with the total information.
One of the sub reports displays currency amounts. The detailed reports display the currency correctly: $26,001 (no cents). The totals sub report does not display the currency formatting: 468934
When I run just the totals query the amounts display correctly ($468,934) by using the CCUR(TotalAmount) variable type conversion. The VarType for the amount field in the totals query is 5 (double precision).
When I combine the detail query and totals query into a Union query the detail amounts display correctly but the total amount is missing the formatting.
Here is the union query.
SELECT TblCompany.TblCompanykey, FormatCurrency(ProviderCostsRetrieval([TblCompanykey],1),0) AS TotalCost FROM TblCompany ORDER BY TblCompany.TblCompanykey UNION ALL SELECT 9999 AS TblCompanykey, CCur(Sum(([QryRptProviderCostsDuringPeriod.TotalCost]))) AS TotalCost FROM QryRptProviderCostsDuringPeriod GROUP BY 9999;
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!
