I created a crosstab query to give me the aggregate totals for each of my 10categories over 3 years. Categories going down, years going across. However when I try to create a report and sum the 3 years I don't get a true sum it adds the fields togethers. Example.
2004 - 0
2005 - 1850
2006 - 0
I get 01850. I need to get a true sum. Do I need to create 3 separate queries to bring the actual values in to do a calculation?
Thanx.
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
11.) Terminates Age 65
12.) Never Reduces
Thanks for your time, Brian
Hi folks,
I would like to add up all the records in a field to get a grand total. Can I run a query to do this? I've tried the Totals button, but doing this is just summing the individual records, not all of the together.
Thanks for any help you can suggest to this Access virgin.
I am calculating the total sales using the formula, in a new field;
Total Sales: ([Quantity]*[Price])+[postage]
This works fine and adds up the totals of each individual sale. But, what I really want to show is the grand total of all sales (the total of all figures in the new "Total Sales" Column)
Thank you
Hi,
Wonder could anyone help me…haven’t used Access for a while and I’m a little rusty :confused: :confused: I am trying to run a query where I need to calculate the total amount of money received between two dates.
My field are DateReceived and FeeAmount. For example I need to calculate the total amount of fees received between 01/01/2007 & 01/04/2007.
Any help appreciated
I am designing a new database for our life and disability department and they have several different policies where the base life insurance reduces at certain ages.
Please take a look at the scenarios listed below and give me any suggestions and tips on the best way to get this done. If you have any.
Below are the 12 scenarios:
1.) Reduces 75% at Age 70
2.) Age 65 to 70 the life amount is $9,000 and Age 70 and over the life amount is $6,000
3.) Reduces at age 65 by 92%, age 66 by 84.64%, age 67 by 77.869%, age 68 by 71.639%, age 69 by 65.908% and age 70 and over by 40%.
4.) On the day following the 70th birthday, the life amount will reduce to 70%. On the day following the 75th birthday the life amount will reduce to 40% of the 70th birthday reduced amount.
5.) On the Policy Anniversary Date following the 70th birthday - reduces 50%
6.) On the Premium Due Date following the 70th birthday, the life amount will reduce to 70%. On the Premium Due Date following the 75th birthday, life amount will reduce to 40% of the amount of the 70th birthday reduced amt.
7.) Age 65 life amount reduces to 65%. Age 70 life amount reduces to 35%. Age 75 life amount reduces to 25%.
8.) Terminates on the first of the month following the 65th birthday.
9.) Terminates Age 70
10.) Terminates on date of retirement or 1st or month following 70th birthday
11.) Terminates Age 65
12.) Never Reduces
Thanks for your time, Brian
I have a table with revenue by contract imported from crystal reports. This data is then thrown in a query to match the contract with the corresponding account rep. The query is set to prompt the end user to enter the account rep name so that only revenue (and related data) is shown for that specific account rep. All that works great. My problem is that I need to show column totals by account rep in the query so that it can be exported to excel and emailed to each individual account rep on a weekly basis. Can someone please help me?
I appreciate any advice!
For some reason I can't write the proper query to get a simple total from a single table. Here is the basic table design:
Transaction ID___Product ID___ Transaction Date____Transaction Quantity
1______________Product A____6/12/2006__________200
2______________Product B____6/12/2006__________500
3______________Product C____6/14/2006__________100
4______________Product B____6/15/2006__________200
5______________Product C____6/16/2006__________300
6______________Product A____6/17/2006__________500
I'm trying to get the total transactions quantity for each product so that I could end up with a form or a report that would show:
Product ID_____ Transaction Quantity Total
Product A______700
Product B______700
Product C______400
... and so on for each item.
What would actually be entered in the query? I'm assuming I would have four columns in the query:
transaction id____product number____transaction qty____expression
Then I would use the SUM feature so the "group by" row appears.
Then I would build the expression above in an expression column. If that's correct, could you give me the expression typed "exactly" as you think it would appear? I'm getting syntax errors when I'm trying it.
And then, once I have the expression built, what should be the "group by" selection under each of the columns?
Sorry for being so ignorant. The rest of my data base works great... but I'm just not getting this one thing!
Thanks anyone who can help me!
I have created a form with company ID, company name, and so on. I put a subform in the main form which includes invoice & points information. Somehow I need to calculate a balance for each customers points. In the form I have points earned and points redeemed...how would i get the totals for each customer's balance?
The main form is linked to the subform, so that you can see each individuals record of points. Please let me know how I can get the balance for each customer.
Thanks.
I have been going over this too much, and i haven't been able to figure it out, It is probably easy solution i am overlooking.
I have a form with a subform that has 2 fields that i want to total separately and then get a grand total.
I also want the user to be able to update the fields which updates the totals.
Anyone have any suggestions
Thanks in advance!
I am looking to do some calculations in a form that can be output to a report.
Basically within the form i have 4 controls as well as the others
Tables
Benches
Chairs
Other Equipment
These controls are bound to a table.
The first 3 controls need to have have fixed costs 2.00, 1.50 and 1.00 respectively. The last needs to be manual input as it can be different dependant on the items in the memo box.
I then need each one to calculate separately to give me a sub total then add all together to give a total
This total needs output to a report for sending out a bill.
I currently have the 4 controls with 4 unbound textboxes beside them for the subtotals:
TablesTotal
BenchesTotal
ChairsTotal
OtherTotal
4 unbound textboxes for the fixed amount and manual input amount (maybe this is where i'm going wrong)
TableCost
BenchCost
ChairCost
OtherCost
and then one more unbound textbox for the total:
TotalTotal
Do i need to create a table for the fixed costs? If so whats the best way to set this up. What should i be binding textboxes too if anything?
Hope this makes sense.
Thanks in advance
I'm trying to build some queries with calculations in, and I'm not able to do what I had hoped. Am I doing it wrong?
This is about sales.I'd like to firstly, be able to have a quick query, to see how many items a seller listed, with total price (this seems fine, if I count the item IDs, and sum hammer price in a query).And vice versa, by buyers.But, I'd like to sum for sellers, number of items Sold, Returned or Relisted.
I have a status (blank for active, "sold, "relisted" or "returned", and thought, I could "count", by the status = "Sold" (ie), but it won't accept the expression.
for sellers, I had hoped to have one query, with total items listed, total money made, then totals for still active, sold, returned and relisted items.
Am sure I'm doing something basic wrong, but I can't think what.
I have a database in which 3 different areas can receive a payment. Prepay Amount, After Hours Charge, Previous Balance. Each one of these payments has 3 different payment types.... cash, credit, check. Now, when a cashier takes a payment, they have to choose whether it's cash, credit, or check, and they can feasibly enter payments into all 3 fields. The question I have, is how can I do a balance sheet whereas everything is broken down into cash, check, and credit for all 3 payment categories. Then I will need it to add up the cash, credit, and check for a grand total.
For example:
Transaction 1 - Prepay -$20 check Balance -$10 check
Transaction 2 - Prepay -$10 cash After Hours -$20 cash
Transaction 3 - After Hours -$20 credit Balance -$30 credit
Transaction 4 - Prepay -$30 check After Hours -$20 check
Transaction 5 - Prepay - $40 cash Balance -$30 cash
How do I get the following output?
Prepay-------$100 (Cash $50 Check $50 Credit $ 0)
After Hours---$60 ((Cash $20 Check $20 Credit $20)
Balance-------$70 (Cash $30 Check $10 Credit $30)
--------------------------------------------------------
Total ---------$230 = $100 + $80 + $50
This is a basic example......each cashier handles about 100 customers per day. I don't want the cashiers to have to tab through a bunch of fields, and the cash/check/credit are on drop down lists.
I have data sheets as subforms on a Main nav page that show the students and the classes they are enrolled in. I have combo boxes to filter this for individual classes /students. On the Classes By Student DS I have the cost of each class. I want to total these classes per student and start building an accounts system that will show how much they owe and how much they have paid. I just need to start with the totals though.
On the Student by Classes DS I want to generate registers as reports. The only hard this in this is I would like to have a box for each class date on the form. I have put the term dates in a small table and I want a box for each class day from that.
I have a report that is calculating account revenue for my sales reps. I am able to generate the report, group by the sales rep and then total their total account revenue. Now I want to group on the total field that I just calculated. How do I do that?
View 14 Replies View RelatedHey 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 RelatedHi there, I am fairly new to access and am having trouble doing what I need to do. I have a table that contains 2 number fields, 14 yes/no fields, and one text field. I want to be able to display the total number of yes's per field, and the total count of each individual value for the number and text fields.
I was able to make individual queries that count all of these, however I'm unsure how I can make a query that will do it all together, or how to put them all together.
I may be doing this wrong, or there may be a better way of doing this.
Any help would be greatly appreciated!
Thanks very much!
is there a way that i can get a total from a number on a form and a number on a sub-form (I want to add the numbers from the form and sub-form and have the total appear in a text box on the form)
View 3 Replies View Relatedi want a field to total up the amount of times a person apears in another table. does it involve using the greek version of E?
View 2 Replies View RelatedHi,
I'm trying to get a total which runs off of a query in a listbox (disguised as a textbox) to always show 1 decimal place. Right now, it'll show one decimal place only if the number is rational. If the number is whole, it doesn't show any.
E.g. For 8.5, it'll show 8.5
For 8, it'll show 8, not 8.0
I want it to show 8.0
Any help would be much appreciated.
folks,
I have two simple tables in a database. One Table is the entry table and the other is the totals. I then created a Form and a subform from the two tables. What I would like is to maintain running totals in the subform as entries are made in the entry table. Attached is the Database. Please help.
Hi i have two problems.
I am creating a football dream team database with two tables:
Team table - Team ID, Team Name, Player 1, Player 2, Player 3, Player 4, Player 5, Player 6, Player 7, Player 8, Player 9, Player 10, Player 11 and Points Total,
All the player columns are lookup functions which are linked to player table
players table - Player ID, PLayer Name, Position, Club, Value, Points
My problem is i cannot figure out how to calculate the points total in the team table from inputting the points into the player table.
I also would like to know how to create a 'total' column by adding together other columns in a table.
Thanks in advance
I have a table. The table stores a workers name and information such as Primary Station Verified.
The Primary Station Verified is a yes/no checkbox. It also has the dates that each worker is verified.
I want to do a query that will show me the total number of people and out of that total number of
people how many are verified. Then I will turn that data into a graph.
How do I do the Query to show me (example) There are 50 people and (example) 25 of them are
verified? I know how to do the query to show all of the people and who are verified but I just want
the numbers.
I'm needing help with a totals query. I have a table with a Department field, a charge field, and a charge code field.
I'm running a query that first groups by the department, then uses the max function to give me the highest charge within that department. For the third field of the query, I want to return the charge code that aligns with the highest charge, unfortunately none of the general totals seem to work and I can't figure out an expression to work either
I have a table where 4 different people enter their amount of work sheets they complete each day via a form. Each person could have a different total each day. Example:
AJ=5
CH=10
MH=7
CA=3
Each person will have a entry for each work day of the month.
I am creating a query that needs to show each persons total for a month. I know how to get the month but how can I total each persons page count all together and then separately? Example:
All four combined total page count
AJ Page count individually
CH page count individually
MH page count individually
Ca page count individually
Thanks for all help
I'm trying to make totals in a query, but instead of using all, I need to leave one out, how to do that? for example the table looks like:
A 2 3
B 4 6
C 2 5
D 3 5
E 2 3
How can I leave row 1 out? So that the query will look like:
11 19