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 am creating a Sales History Report. It displays products sold by customer by Sales Rep. The groupings are Sales Rep, Customer, and Product. I am hiding the detail section which contains invoice line item records. So the report shows the product subtotal by Company. The problem I have is I need to see the product subtotals in the Sales Rep and Report Total sections also. So the report would look something like this:
Sales Rep A
Customer A Product A Product B Product C
Customer B Product A Product B Product C
Sales Rep A Total Product A Product B Product C
Sales Rep B Customer R Product A Product B Product C
Customer S Product A Product B Product C
Sales Rep B Total Product A Product B Product C
Report Total Product A Product B Product C
Is there a way to accomplish this without doing sub-reports?
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
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?
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'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?
Not sure if the question belongs in queries or reports. I've a list of product codes that we produce daily, weekly, monthly. Some repeat several times within a week or month. I want to list them in order by cost. I can do that, but when they repeat, I wanted to combine them into one subtotal, then get sorted by cost with the other product codes.
Right now, I'm dumping the table into Excel and doing it manually. I sometimes have 7 lines of the same product code. I insert a line and subtotal them by their product code. Then, I delete the data leaving the subtotal line. I then can sort them with the other subtotal and other once product codes.
:-[ I'm hoping this is an easy answer. I'm not good with Access. It's turned out to more of a trial and error thing for me.
ProductA would also have a minimum inventory level of 6 in another table. So the parameter I would like to create would sum all the different lots of ProductA and compare it to the MinLevel entry.
Hi there - I've looked at quite a few posts and although some of them seem to start of heading in the right direction there doesn't appear to be anything that quite hits the spot for what I'm looking for.
I'm trying to create a query that creates the following (see spreadsheet as typing it out didn't help.)
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers] but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
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 have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
I have a form built with multiple buttons. Once the user clicks the button and enters their parameter, I am using VBA to export the data to an excel pivot table. I would like to turn the subtotals to false so as the user clicks the check boxes in the pivot there are not any totals, subtotals, or grand totals. I am not sure how to add that to an existing query?
Hi 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!
