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 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
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)
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?
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.
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
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!
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.
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?
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.
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?
I have a query that lists three columns, is there any way I can have a total at the bottom of each columm? I Need to show the total for each column in my report.
I have a rather simple query that shows the number of layoffs in a given period and would like to know if I can have the query sum the numbers.
Company City Date Incident Laid-Off CompanyABC CityABC 5/3/2007 Mass Layoff 207 CompanyKLM CityKLM 5/5/2007 Mass Layoff 54 CompanyQRS CityQRS 5/7/2007 Plant Closing 436 CompanyXYZ CityXYZ 5/9/2007 Plant Closing 75
Is it possible to somehow display the total (772) underneath the "Laid-Off" column? Or create a separate column that only displays the "Laid-Off" total?
Or would it be easier to just build a report from the query and display laid off totals on the report?
(I apologize for the "table." I tried to import the query results as an image but couldn't get it to work.)
I have a table that has a column of Equipment Numbers (e.g. CN330) and another column with corresponding costs. The Equipment Numbers are not unique. A number may appear many times in the column and the cost may be different each time. (The source data for this is an Excel spreadsheet provided by another department).
I need the totals for each unique Equipment Number but not in a "messy" format like subtotals. I need to use it as a lookup for another part of my database so want just two columns; unique Equipment Numbers in the first column and the total cost for each in the next column.
I have the unique Equipment Numbers in the first column of a new table but cant figure out how to get the total costs.
My problem is if there is no FaultTotals nothing shows up. I would still like for the Four Columns to show data and have a 0 in the FaultTotals.
How can I accomplish this?
In a nutshell I am totaling all Cosmetic Faults based on the SystemGroup CTWT and a Date Range. If there are no Cosmetic CTWT Faults for the DateRange I want a zero. The query works great as long as there is at least One FaultTotal for the Date Range.
SELECT "1-3" AS Truck, "Cosmetic" AS Category, WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS FaultTotals FROM WorkUnitsFaultsMainTBL WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)="Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174"))) GROUP BY WorkUnitsFaultsMainTBL.SystemGroup HAVING (((WorkUnitsFaultsMainTBL.SystemGroup)="CtWT")) ORDER BY Count(*) DESC;
I have a table with numberous records with Values in a number of fields. In a report I am showing all these records but at the end I want to tally the values.
Whats the best way to do this?
I thought of placing a text box in the form that will run some code to calculated the total....
I'm trying to create a query to work out the total amount to invoice based upon some selections. Currently my query looks at the values in several fields (numberOfRollsUsed etc) by taking the value in these fields and multiplying by a fixed amount to calculate the total.
What I would like to add to the query is fixed values based upon some check box selections. So if check box A is selected, add 5 to the total, is check box B is selected, add a further 10, and so on. All fields and check boxes are held within the same table (Job).
Am I being daft or trying to do something in the worst way possible? I'm actually adding this to an existing system so I don't have so many options to completely redesign the system to calculate this in a better way.
I have a database which has a table for the quotes prepared, each record has a quote amount. In a query linked to that table I have the ability to get just a specific month view which shows all totals in that month. I want to calculate a grand total for the amounts shown in quote amount:
Record 1 : 100.00 Record 2 : 100.00 Record 3 : 50.00
Grand total : 250.00
Then I want to display this grand total on a form which is visable on a screen based in the office that has an auto refresh on it so after 2 minutes any new records added to the table will increase the grand total on the form on the screen.
I have worked out how to do the totals bit, but now I need getting this figure (I.e. 250.00) on a form without having to run the query and have it looking in the background.
Hey 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!
I am trying to run a simple update query to copy data from one column (Addrl1)to another column (Working_Addrl1) within the same file and I can't for the life of me figure it out. Then I need to repeat for addrl2 and addrl3 to working_addrl2 and working_addrl3.
In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 08/07/2006, 22/07/2006, 05/08/2006 etc
I have a column in an Access table listing various dates. I want the next column to be populated with the next pay period end date after that date.
So if DATE is 05/07/2006 I want PAY PERIOD END to be 08/07/2006 and if DATE is 09/07/2006 I want PAY PERIOD END to be 22/07/2006 etc
