Calculating Totals From Filtered Datasheets
Sep 3, 2015
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.
View Replies
ADVERTISEMENT
Jun 28, 2005
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
View 2 Replies
View Related
Feb 9, 2007
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.
View 5 Replies
View Related
May 26, 2007
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
View 1 Replies
View Related
Jun 20, 2007
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.
View 4 Replies
View Related
Apr 1, 2008
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
View 10 Replies
View Related
Jun 28, 2005
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
View 4 Replies
View Related
May 14, 2005
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!
View 1 Replies
View Related
Jun 29, 2006
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!
View 4 Replies
View Related
Feb 22, 2006
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.
View 1 Replies
View Related
Mar 13, 2007
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!
View 3 Replies
View Related
Apr 17, 2007
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
View 2 Replies
View Related
Aug 22, 2012
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.
View 2 Replies
View Related
May 4, 2007
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.
View 1 Replies
View Related
Jul 9, 2013
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 Related
Apr 10, 2014
I've done this once entirely by accident and can't seem to duplicate it...
I have a report. It has the following:
Report Header: Logo and title
Department Header
Supervisor Header
Group Header
Detail
Department Footer: Totals
Report Footer: Overall Totals for all departments
Here's my question.
I have combo boxes on my main form that filter this report. The combo boxes are referred to by the query that runs the report. How do I get proper unfiltered overall totals in my report footer?
View 4 Replies
View Related
Jul 6, 2006
Hey ho
I have a table full of dates and each date has multiple time slots, and each time slot has multiple data values.
What I would like to do is have 2 cascading combo boxes (for date and time), so when you click the date it returns only the information for that date in the datasheet, and then click on the time combo box (which displays only time slots for that date) and returns the specific time slots for that date.
Any help with this would be most grateful!!
View 2 Replies
View Related
Nov 1, 2007
Access 2007 apparently no longer has the autofill feature with datasheets. This was an option that I used frequently and not having slows down greatly my particular tasks.
Is there some way of activating it?
View 5 Replies
View Related
Jun 24, 2015
My goal is to use a combo box on a nav pane to filter the following 5 nav panes with data sheet views on them. The combo shows 'component' and should filter each datasheet to only show records with the same 'component' selected in its respective component column.
What I'm currently dealing with is said component combo disappearing upon nav change, and I can't get it to filter the datasheet..but that is likely due to the fact that I cant get it to hold value on page switch.
View 2 Replies
View Related
Sep 4, 2007
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!
View 14 Replies
View Related
Aug 16, 2014
I need to make the text size smaller to fit everything on the screen (users use 17" monitors..). I have gone into each form and subform and changed the text size for each text box to 9, but when I view them in form view the size has not changed ?
Also .. is there any way to make the autocentre option actually work as it's name suggests !! ? it does in most cases centre horizontally, but never vertically, quite often putting the top of the pop up form over the ribbon bar.
I would actually like to be able to totally remove the ribbon bar so that only the database forms are the only thing the user sees.
I vaguely remember being able to do this in Access 97 (Which was the last version I used - a long time ago !) but this seems to be more difficult in 2010 version.
View 5 Replies
View Related
Aug 2, 2005
I would like to know how I can put filter fields on a DAP that when you press a button will then filter a table. Also, does anyone know of any good references on working with DAP, becasue from what it seams like, there isn't any technical information out there.
View 1 Replies
View Related
Sep 27, 2004
Hi everyone, I am new to writing queries in access and I have got stuck with this one.
I have 2 tables containing similar data from 2 different paradigms.
The main columns I am interested in are not unique - labelled ID and Delta Ex (a score). The same ID values can occur more than once in the same table, so I am interested in calculating the average Delta Ex score for each ID value in each table.
I then want to ask what ID values occur in both table 1 and table 2 and then calculate the average score of each common ID values across both tables. i.e. ID 1 score = avg(table1.delta ex, table 2.delta ex).
I then want to rank the top 50 scores.
So far I have the following to find common ID values, with a score above a threshold set by the input string threshold. These are ordered with in descending score value.
SELECT [MODEL 1].[ID], [MODEL 1].[Delta Ex]
FROM [MODEL 1], [MODEL 2]
WHERE ((([MODEL 1].[Delta Ex])>[threshold]) And (([MODEL 2].[Delta Ex])>[threshold]) And (([MODEL 1].[Accession])=[model 2].[accession]))
ORDER BY [MODEL 1].[Delta Expression] DESC;
With a second query I can select the top 50 scores:
SELECT TOP 50 [query 1].ID, [query].[Delta Ex]
FROM [query 1]
ORDER BY [query 1].[Delta Ex] DESC;
However, I can't for the life of me work out how to obtain an avg score for each ID value.
Any help would be very much appreciated.
J
View 2 Replies
View Related
Jan 25, 2006
Hey all, I have been trying to filter my report However It doesnt seem to be working the way I would like it to. I have the ability to filter on my form, so I can search Wood* and then there will be 27 records displayed so I can view each one speratly. however now I would like to have it on a report. I would like to then click the report button, and view those 27 records on my report as the layout that I have provied I can see multiple on a sheet. Any ideas?
View 4 Replies
View Related
Feb 9, 2012
I have two tables, say customers and purchase orders. I am trying to write query that returns a list of customers based on a wild card purchase order search.(FYI, the query is being used in a VB.NET application)
Code:
SELECT Customers.WarehouseID AS Customers_WarehouseID, Customers.Name, PurchaseOrders.PurchaseOrder, PurchaseOrders.WarehouseID AS OBRs_WarehouseID
FROM Customers INNER JOIN OBRs ON Customers.[WarehouseID] = OBRs.[WarehouseID]
WHERE (((OBRs.OBR) Like '" & strPOFilter & "*'));
This query almost works how I'd like it to, except in cases where a single customer has multiple purchase orders that begin the same way. So, for example, if customer "mike" has purchase orders "00554" and "00553", if you search for "00", mike will be returned twice. How can I modify the query to only return the customer once?Is it possible, or should I filter out repeated offenders in my code?
View 2 Replies
View Related
Jun 8, 2005
I have a form that contains a pretty large list of items and several columns and the list can be sorted by each of these columns. When a user double-clicks on a particular item, another form pops up that lets the user view/modify the item. The problem I'm having is that I still want to be able to use the navigation buttons in that second form, but right now its just saying 1 of 1 (Filtered).
The way I'm applying the sorting is by updating the Rowsource field of the list with an "Order By" statement
Any help would be appreciated
Adam
View 2 Replies
View Related
