I'm trying to sum revenue by unit by program by segment but it's seem to be summing it by all units or all programs. my joins
are the first option egaual to both tables. it's a select query and only has two tables. the unit, program, segment are all 'group by' and the revenue is 'sum'. could my joins be wrong or is there another reason i'm not getting the sum by unit by program by segment?
I have a sales form. The sales form has many Call, Meetings and Emails linked to each sales record. I want to total all the number of calls, meeting and email records related to the sales record to give a total- Touches.I've created 3 queries;
1 - Counts Calls
2 - Counts Emails
3 - Counts Meetings
These all work fine however when I combine them to attempt to count the results it doesn't produce the correct results.I have a second query as well (no pun intended).
I am trying to sum together a column that has values in Time. The results displays as a decimal. How can I have the result display as a Time i.e. 1:20 (1 hour 20 minutes).
Hello! I'm new here, and I'm back into Access after a few years of not using it.
What I'm trying to do seems simple, but I can't seem to get the sytax down.
I have 6 columns that have dollar figures in them. I want to get the totals for all 6 columns to show in one field. I'm using the sum([field]) to total the individual columns, but I can't seem to get all of them together in one sum. Is this possible?
Thanks!! :D
hey all, thanks to anyone that can help
i have 5 fields facid custid empid amount invdate.
i need to be able to get totals on the amount based on fac, cust & emp based on a date range given by user, but here's the kicker.. i need to get the current month totals, month totals - 1year (so same month but for last year)
the date range totals, last yeardate range
current ytd, last ytd
i did it using code, but the performance isn't that great. there are 2800 customers it has to cycle through and figured one of the resident experts can help with a query.
i've used dsum() but not quite getting the results i should.
i think one of the problems is the user has the option of filtering the data based on the 3 fields.
anyways if you can help i'd greatly appreciate it..
Hi all, sorry to bother you all, especially if this has already been answered, but I can't seem to find the solution to it. Assuming that what I've been doing has been correct that is.
I have three (3) tables with the following fields (where PK is primary key);
[tbl Ingred]: IngredID (PK), ProdID_fk, CompCode, IngredName, Weight, Symbol, RP01, RP02, RP03, RP04, RP05, RP06, RP07, RP08, RP09, RP10
[tbl Prod]: ProdID (PK), ProdName, FP, BP, SBU, ProdNotes
[tbl RPCalc]: RPs, RPs_long (PK), Sym, Limit, Sym_1, RP_1, Comp_1, Add, RP_01, RPNotes
[tbl Prod] has a one-to-many relationship with [tbl Ingred]; that is, for one ProdID, there can be many IngredIDs. The tables are designed so that the final user-displayed form is 'user-friendly'.
I've made a qry called [qry Qcalc] used to calculate the the Quotation (Q) of each of the 10 RPs (RP01 - RP10). Which simply for the first one is; QRP01: [Weight]/[tbl RPCalc_1].Limit, where the [tbl Ingred].[RP01] is linked to a corresponding [tbl RPCalc], by the [RPs] field. As for one [RPs] there may be multiple [RPs_long]. The way it is set up is so that the [RPs] and [RPs_long] are mapped correctly, to be used for calculations.
However, I am now stuck with a problem. I need to sum up these QRP values ([QRP01] - [QRP10]), based on (at least?) two conditions. The user can enter in their RPs; e.g. R10, R20, R21 in any of the [RP01] to [RP10]'s, and in any order.
What I need is to be able to obtain a sum of the Q values for each [RPs_long] (e.g. R10, R20, R21), rather than being the sum of the fields [RP01] to [RP10]. Also, each [RPs_long] may or may not be able to be summed up, depending on another field [Add] which has a "Yes" or "No". "Yes" is for it being able to be summed up, and "No" for it not being additive.
So the end result would be; for each [ProdID], there will be multiple [IngredID] where each will have up to ten (10) "RPs" (that being the [RP01] to [RP10] fields). These inputted "RPs" are then related to [tbl RPCalc] to be able to obtain the Q-values. If the Q-Value is not greater or equal to 1 (>= 1), then the sum of the Q-values for each [RPs_long], provided that the [Add] field is "Yes", will then be able to determine what are the final output RPs to be used, i.e. for the sum of the Q-values are greater or equal to 1 (>= 1), that [RPs_long] will be used.
I've been stuck on this for quite some time, and I know that if I change the whole table structure (like having a field for each RPs in the [tbl Ingred]), I might be able to make it much easier for myself. However, by doing so, it will not be able to offer as much flexibility in the calculations, and also, may not make it as 'user-friendly'. Another thought was maybe to create another table with just the [CompCodeID] (based on [tbl Ingred].[CompCode]), [Weight], [RP], but I'm not too sure how this will work either, but I might give it a shot if this current method doesn't work.
I hope that this made sense, as it is all quite complex (and confusing) to me. Any help on this matter will be much appreciated. Thanks in advance.
I have a linked table. The table looks like:
Course 1 Name | Course 1 Grade | Course 2 Name | Course 2 Grade |
Student 1
Student 2
Student 3
I need to write a query that will consolidate all the courses (15 total) into multiple rows like this:
Course name | Course Grade
Student 1
Student 1
Student 2
Student 2
What is the best way to do this?
[AR&R Hours]+[PTO # of Hours]+[Meeting/Calls # of Hours]+[System Issues # of Hours]+[BAU Training # of Hours]+[Exception Processing Hours]+[AIG Related Activities]+[Admin Work Hours]+[Migration Hours]+[Project Hours]
Need the fastest and easiest way to auto calculate the above in a field on a form. i can get it to calculate in a query but need it to do in the form as well.
I have 4 revised ship date columns, an est ship date, and an actual ship date. I have a column summing days late for est ship date and one summing revised days late. The expression I am having problems with is to calculate the total days late for the revised. I want it to check the 4th revised and if it has a date calculate from that date. If not I want it to go to the 3rd and calculate from that, and so on. I have pasted the code I was trying to use. It just goes all the way to the bottom of the code and calulates from the 1st revised ship date. I have also attached a jpg of the layout of the columns. I also want to do this in the query using expression builder.
Days Late Revised: IIf( [4th Revised Ship Date] <>null,(DateDiff("d", [4th Revised Ship Date] ,
[Actual Ship Date] )-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,1)-DateDiff("ww", [4th Revised Ship Date] ,
[Actual Ship Date] ,7)),IIf( [3rd Revised Ship Date]<>null ,(DateDiff("d", [3rd Revised Ship Date] ,
[Code] ......
I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc
View 3 Replies View Relatedhello there...ive a question...i have a summing query which searches for the word "processed" in a specific column of a table...currently if "processed" is not found within the column the query will not return anything...is there a way to have the query return a zero instead?
View 3 Replies View RelatedWhen i'm summing up values in a column like "Hours:[production]+[nonproduction]" how can i have access treat blanks as zeros? I tried
"Hours: IIf([nonproduction]="",0,[nonproduction])+IIf([production]="",0,[production])" and it just gave me a #value error
Hello,
I'm trying to sum numbers in a query. The table that Im summing from is set up as follows. The 1st column is just an autonumber. The 2nd column is an individuals. The third column is a date. The fourth column is a transaction (Buy or Sell). The fifth column is an amount (in $). and the 6th column is an Account (RRSP or RESP).
I would like to sum all the buys for each individual for each account. However I'm finding this difficult to do. Here is what i have so far. It sums all the 'buys' for the entire table.
SELECT Transactions.[Customer ID], DSum("Amount","Transactions","Transactions.Transaction=1") AS Expr1, Accounts.[Account Type ID]
FROM Transactions INNER JOIN Accounts ON Transactions.[Account ID] = Accounts.[Account ID];
Thanks very much,
C
SELECT Commissions.TransactionID, Commissions.OriginatorID, Commissions.AmountPaid, Commissions.DatePaid
FROM Commissions
WHERE ((Commissions.DatePaid)> DateAdd("d", -32, Date()));
I want to add up commissions paid in a month using a simple query and im not sure how to proceed from here. This example will pull records for the last 32 days, but now how do I do the sum commissions.amountpaid to work?
Thanks for any help.
Scott
I have 2 fields, Accounts, Accounts CMO. I need those two fields to show in the same table in a new field called Total Accounts. I am summing them, but getting irregular results, some are right, and some are 4 times more than they should be? How would you go about doing this, maybe I am missing something.
View 1 Replies View RelatedI have a query and one of the fields is numbers. When I run the query I would like for that column to total the numbers. How can I do this and the have the total appear in the Query?
View 1 Replies View RelatedHello Everyone,I am trying to perform a sum of records for the table belowSeatType| Ext rent | |Internal rent | Work Transfer 2 2VendorOther 2 3LOB BRF GTI BRF 2 5Displacement so that when I run the query I get the following followingSeatType| Ext rent| |Internal rent| | Total |Work Transfer 2 2 4VendorOther 2 3 5LOB BRF GTI BRF 2 5 7Displaceme What I mean is that from the first table how is it possible to get the results below. The query would generate a new column and place the sum of the data in the each row(record) in the same row in the total column. I would be really grateful. Have been bashing my head all day over this. PS I dont know any VBA. Is it possible without VBA?Thank you.
View 5 Replies View RelatedI have a sum query issue I am trying to work out and I have trouble trying to coordinate it.I am trying to build sum queries inside one select query that would allow me to sum point values in a separate table. The problem I am foreseeing is that I have three tables all connected with realationships. The structure with the fields is below:
Table1:
Test Case
Description
...
Table2:
Run
Description
test_case
...
Table3:
Task
Points
Group (Run)
Date1
Date2
Date3
...
Relationships:
Table1.Test_case-->Table2.test_case ( 1-many)
Table2.Run--> Table3.Group ( 1-many)
I want to be able to sum the total amount of points in a test case. Currently I am able to sum the number of points per run by doing a sum query on SUM(Table3.Points) in a select query that queries Table2 and Table3. But I try to do that with select query for Table1 and Table2 the query returns the same sum for all test cases. That sum is equal to the point total for the 1st entry in Table1.
Can I add a criteria somehow to make the sum work for each Test Case entry in Table1?
Also in regards to this same summing issue I spawned a second issue. I wanted to run a sum query on Table3 that would allow me to sum all the entries in that table that had valid entries in the Date1 field. I tried the following criteria.
Not IsNull([Date1])
The query came back with the same output as the above sum query.
What I am trying to accompolish essentially is the to get two sums out of the query. The first sum would be to sum all of the points totals for each Test Case. The other being able to sum point totals based of if a Date1 Field being filled in for each Task.
I am pretty sure I can do this in one query and just manipulate the data in the form of expressions and sum queries.
I am still searching through this forum for valid suggestions. But any thoughts will be welcome.
Thanks in advance.
I have a crosstab query that pulls revenue the first month after the start date of the program.
Some of that revenue is in January, some February, etc. I am trying to combine them to be in one field, either in
the query itself or in the resulting report. How do i do this? Currently I have tried this: =Sum(Nz([1],0)+(Nz([2],0)+Nz([3],0)+Nz([4],0))) in the report but it hasn't worked.
Thank You!
I have a query which displays sales and cash deposits for the day. I want a sum of all cash deposits.
So it looks like this..
Date________________Amount_______________SumOfAmou nt____________Exp1
17/7/2013___________$55___________________$55__________ __________$55
17/7/2013___________$22___________________$22__________ __________$22
I tried doing it by two methods.. neither of which are working.
In the Amount field, I put it as a Total: Sum. I also tried creating a new field which is an expression which sums up the Amount field. Neither are summing it and just displays the value.
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...
Creating a Query that would give me results showing in the attached file column D. I am trying to build volume incentive database to track how much we are going to save once we hit certain volume and rate drops.
View 2 Replies View RelatedI have a a dataset that has recorded encounters with a number of Banded (identified) animals. It is currently setup so that each encounter is its own row; with all the information, including "Band_Num" repeated, but "Encounter_Date" being unique. I wanted to query the table and get it to display the results as "Band_Num" on a row, followed by x columns for with each unique "Encounter_Date" represented. I thought a "Group By" function would work, but that requires a sum, which is not useful.
Complicating this is the fact that not every individual is seen a set number of times, so Animal 1 might be seen 3 different dates, but Animal 2 only on 1 date.
I have a shell of my database in the below link for reference. I am trying to create a query for a report that will allow me to track charges and payments per client. Each charge has a ChargeID and each payment has a PaymentID. Multiple payments can be applied to one charge so the same ChargeID can show up multiple times with a different PaymentID. Payments that are catagorized as Third Party Payments will have not only a PaymentID, but a TPPaymentID.
What I need to do in the query and final report is track the total charges (even though the ChargeID may show up multiple times, I only want the charge itself to be calculated once and the charge to only be listed once for each client) and track the total payments as well as sum the total payments for each charge for each client.
I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?
View 1 Replies View Relatedi'm going to start this by telling what i am working toward in the end as it is where the problem obviously stems from.
i am trying to create a report which will tell me how many times 'hcapp' is in a table. there are three different fields where 'hcapp' can be placed...i can easily create three different queries which return how many times 'hcapp' is mentioned in one field, but i have yet to figure out how to look through all three fields in one query...is it possible? if not, how can i have a report call all three of these queries and then sum the total?
i've a question that hopefully has a simple answer.
i have a query that returns three different numbers (each in its own column)...anyway, how can i make it sum the number of these three numbers...the best case scenario would be if i could have a fourth column appear in the results of the query with the sum...
it would look something like this
num1 num2 num3 sum
1 2 3 6