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 RepliesHello, I am a newbie access user, so this might be a stupid question, but here it is:
I have a lot of yes/no fields labeled "10/1", "10/3", "10/5", ... (dates)
They are to record attendance on particular days.
I want to make a query that gives me the sum of a student's attendance and I have no idea to go about that. Any help is much appreciated.
Thanks!!
Hi,
Not sure if this is possible (sounds simple hopefully) but I was wondering if there was a way to sum certain fields in a record on a continuous form.
E.g. Field1 and Field2 in record 1.
Is it something simple like summing columns, i.e. Sum(Field1)?
Any help would be much appreciated.
Hi,
I am trying to figure out the best way to design a table. The table is going to contain data concerning checks that have been given to a church. Each check could be broken down into 3 different categories; Tithes, Offering, Other. In other words one check for $100 dollars might be Tithes $40.00, Offering $30.00, Other $30.00. I need to be able to enter these three amounts into the record and then have them totaled in another field.
Is it best to do this all in one record or should I have two tables? If I have two tables how do I get one form to enter the break down into one table and the total in another table. Fyi, if I do this in one record I don't want them to have to enter in the total. I want the total that is entered to be calculated by the three previous fields.
Thanks for any help you can be on this.
Is there a way to use a query to sum totals of several fields per record? In the db I have designed, I have 5 seperate fields for estimates from different departments. There is also a field for Total estimates. What I would like to do is have the Total Estimates field autocalculate the total sum of the other 5 estimate fields. Is this possible to do through a query? If so how? If not - is it possible at all?
Thanks!
Hi! I'm trying to create a query using option groups, i have two options the first one is STOCK and the other is PD.
If i select stock the the query i want to create to introduce it on a form is:
I have 5 fields
-denominación
-reserva
-almacen
-cantidad
-stmax
If i select stock
then the query is
If (reserva +almacen)<cantidad then on a sub form have to appear the table with all the rows where (reserva +almacen)<cantidad.
but if i select PD only have to select on stmax the rows where value=PD
PLEASE i need help with this....
I have run into this problem with several databases I have created. I have my tables all set to store my information. I create the form to enter the information. I try to create the field to calculate a total and every time I enter the amount in the form, I get '?name' or '#name'.
With this particular database, I am looking to track expenses. Expense categories on the left, dates across the top, amounts in the middle, totals across the bottom for dates and down the right for categories.
If there was a way to attach a document, I would send the expense report. If you want to look at it, let me know and I will send it. It's a small xls file.
Thanks.
DT
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 number of fields that hold values (scores - either 0,1 or 2) that need to be summed but in some cases the value is 99 (which is recognised by our stats program as a N/A type of anwer- This occurs when a question is optional and the user hasnt answered it. I did not want to value to be null (since its not like the user didnt answer it because they missed out on it by accident but rather they could not) and it couldnt be 0 either since 0 actually means something different in the context of the questionnaire.Lets say I have Q1 which has a value of 0, Q2 has a value of 1, Q3 has a value of 99 and Q4 has a value of 2I want to get the total of Q1+Q2+Q3+Q4 for a particular entry but if the value of any or all of the questions are 99 I want access to ignore it and just sum the rest of the values i.e. the sum should read 0+1+2+(99)=3 not 102I want to be able to include this sum function in the expression builder if possible since I am using it for a report. Can someone please help?
View 3 Replies View RelatedWould like to create a sum of a field in a sub form. I am using =sum([name of field]) in the footer of the sub form but it only gives me the answer of the first entry in the form. I also created an unbound box in the detail portion =[table1 subform].[form]![name of field]. What am I doing wrong?
View 1 Replies View RelatedIn the footer of my report I have a field that sums the Transaction Amounts - =Sum([Transaction Amount])
I would also like to break the totals down further by Transaction Type Codes.
Something like =Sum([Transaction Amount]) where Transaction Type Code = AL-Exp-Paid
I am involved in centrally managing a project , a part of which involves employees of certain companies being given a weekly subsidy for study on academic courses. I have successfully set up a table for all the learners and a related table for companies.
Each learner will receive a fixed weekly rate subsidy in the first year, and a lower fixed weekly rate subsidy in the second year. Somebody (not me) will need to check their paychecks to ensure that employees have been paid and all is above board. The learners/employees can start on the programme at any time. I need a way to track this (total claimed amounts, where they're up to etc).
My theoretical solution, is to have a field for each week a learner is on the programme (52 first year, 52 second), and instead of entering a currency amount in the field, the check-person can enter the date they saw the evidence and are happy with it. I thought then if I create a query that searches any dates that fall within the current claim period (eg >01/01/2015, <=Date()), and multiplies field count that fall within those parameters by first/second year subsidy rate if they're in that table. I think my logic is sound, I am just not sure how to write the query/expression, or if it's possible, or if there is a much simpler way to do it.
[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 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 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
In my tables i have used calculated fields. one of the fields is to "total expenses." In a report, i need to show the sum of all the "total expenses", the filed populates in the report but the cents are missing. for example if the amount is 6080.40 it shows as 6080. how can i get around this? I have tried changing the decimal point value to 2 at which point the value turns to 6080.00 when it should be 6080.40 (i am a beginner at this i am assuming the answer will probably involve c++ or visual basic's, two concepts i am not familiar with.)
View 2 Replies View RelatedSELECT 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 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! 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
Hello 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'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 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.