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.
I've got a form for a volunteer tracking database I am trying to create that has all the voluneer's infomation and has a subform in it that shows the volunteer's activies and hours. I have a text box (txtHours) on the subform with the following Control Source "=Sum([hours])", with the visible property set to no. This sums the total hours volunteered by the individual.
On the main form I have another text box (txtTotal) with the following control source "=Hours.Form!txtHours" where:
Hours is the Name of the subform and the Form!txtHours references the control on the subform.
I am trying to create another textbox that will display the total hours for all records in the form, but not having any success.
Any suggestions?
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 a problem with a total on my main form (calculated from a subform) not updating immediately. The updated total only displays correctly after I’ve navigated to another record and then back again. I would like the totals to update immediately after I’ve entered the new data. Can anyone help? Here are the specifics…
My main form has a subform which displays a dynaset of records from a query. I’ve set up this subform so that I can enter a new record, which is then written back to the record source for the query. This is accomplished via a combo box from which the user selects a product, and an AfterUpdate event returns the price of that product to the TotalPrice control on the subform. In the footer of this subform is a calculated control (called AdExpenseSubTotal), which calculates the sum of the TotalPrice field from the query. The footer of this form is hidden, and I’ve set up a control on the main (TotalSpent) form which displays the value of the subform control AdExpenseSubTotal. This total seems to update immediately whenever I tab off the relevant field in the subform – so far so good, no problems here.
Now here’s where it gets tricky. My main form has another subform which is hidden. This subform returns a dynaset of records from another query which has the same record source as the query on the first subform. Like the first subform, this subform also has a hidden calculated control with an aggregate function (Sum), and a calculated control (VendorSpent) on the main form to display its value.
As I said before, when a enter a new record in the first (displayed) subform (via the combo box) and tab off it, the TotalSpent control on the main form updates immediately, but the VendorSpent control doesn’t. Obviously the new record I’ve added doesn’t immediately show up in the query dynaset of the second (hidden) subform from which the VendorSpent control on the main form gets its value. The VendorSpent control only displays the updated total when I navigate to the next record, and then navigate back again.
Is there some Event Procedure I need to add to the AfterUpdate property of the combo box on the first subform which immediately forces the second (hidden) subform to requery? I probably haven’t explained myself very clearly, but I can provide a copy of the file (it’s only 184 Kb) if that helps.
I have an application that has two tables, linked on a common key. The main data entry form is for invoices, and has a sub-form for parts used. A similar report/sub-report is used for reporting. Everything is fine for records that are entered via the form. My problem is that when I import data from other sources ( consolidation process ) the calculations are not performed. So when those records are displayed no totals appear until I click on a control that drives the calculations. I don't want to store totals in the database but I need to have them reflected on the viewed form without operator intervention. Otherwise I'm open to incorrect decisions when the operator doesn't notice that an invoice doesn't reflect the cost of parts used.I'm looking for a "one-time" process that I can invoke after loading new data that would "touch" each record (ideally each "new" record but there's no impact to re-calculating existing ones) and drive the calculations so that the viewed form is correct.It doesn't need to be "easy" or automated since the end users won't do it, only myself as part of the data consolidation.Any ideas?Thanks
View 14 Replies View RelatedI have a music database. It's somewhat over the top in terms of the information stored in it, but I like it. My issue is with getting the total number of tracks, as well as the total duration, for a given album (and for each disc, but if I can solve one that should also solve the other).
I have a lot of tables and forms, but for this problem I only need to deal with two of each. They are the tables for albums, the table for tracks, and their associated forms.
In my album table I have information such as album title, artist, release date, and album length. The tracks table contains information on each song - track title, track number, parent album, and track length.
My main form is for the album itself, and contains a subform for the tracks on said album. They correspond to the relevant tables.
What I want to do is obtain the value of the album length by calculating the sum of the track lengths for that album, and the number of tracks. Easy enough - Sum(TrackLength) and Count(TrackID) respectively, both of which can be stored in the footer of the subform.
My problem is that, rather than simply copying those values into text boxes on the main form, I want to copy them into actual fields in the album table. Furthermore, I want them to update as I add or edit the track lengths in the subform.
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 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 RelatedI 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,
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.
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
I have two tables, one for budget and the other for actual expenditure. I need a query or queries that will give me a cumulative figure on 3 groups based on the expenditure table. In my expenditure table I have the following fields.
ExpenditureID Autonumber
Period Text
CostCentre Text
CostCode Text
Value Currency
fldDate Short Date
I have managed to fnd a function that will give me a running sum for each individual group, but cannot seem to get it to work for more than 1 group. The function is this.
Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long
If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If
'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function
Is there any way I can see the results in one query or table? Any help appreciated.
Hi,
I'm having trouble trying to create an expression for generating cumulative totals by Month.
I believe this is the function I need to use -
DSum (expression, domain, [criteria] )
I need the layout to be as follows -
Months - Rows
Cumulative Monthly Despatches - Column
I have a field list with
Months
Despatches
Models
All of these are from another Query and 'Despatches' is an expression generated in the other query
Can anyone please Help?!
I have a table that has a start day and end day. I would like to get a total for all events occuring on the days of the week (mon, tues, etc.). The problem I am having is when the start day and end day are not the same I need to count that event for all the days in between also.
Example
(Start Day)/(End Day)
(Sat 1/1/2005)/(Sat 1/1/2005)
(Sat 1/1/2005)/(Sun 1/2/2005)
(Sat 1/1/2005)/(Sat 1/8/2005)
(Sun 1/2/2005)/(Mon 1/3/2005)
(Mon 1/3/2005)/(Mon 1/17/2005)
(Thurs 1/6/2005)/(Mon 1/10/2005)
(Thurs 1/6/2005)/(Thurs 1/6/2005)
Results:
Day/num events
Sun/5
Mon/3
Tue/2
Wed/2
Thu/4
Fri/3
Sat/5
Any help you can provide would be greatly appreciated.
Hi,
Im very new to MS Access so bear with me.
I have a table containing travel claims data. In some instances there are multiple claims attached to certain policy numbers. Currently the Table has a seperate line of data for each and every claim. What I need to do is to ensure that there is only ever one line of claims data per policy number and that columns containing data such as: Total Reserves / Total Paid / Net Reserves / Claims fees are totalled on that line.
The reason I need to do this is that I then want to run a query that compares total claims per policy against total premium per policy.
I would be grateful for any help.
Regards
Matt
I am working on a stat database for a dart league.
each player has the possibility of playing in 3 games a night.
I have been able to gather all the totals of scores wins lossess number of times played and a percentage on each type of game.
example 601, cricket, 301
when I try to total the numbers of wins acrost all 3 types of games
Total wins:[6w]+[cw]+[3w]
I only get a score when there is data in all 3 other wise it is blank.
I tried Total wins: IIf(isnull([6w]+[cw]+[3w]),0,(6w]+[cw]+[3w]))
and this give me a 0 unless all 3 have scores in them.
I need to know how to give me the total of wins over all and if the total is o the have a 0 entered otherwise give me the total.
any suggestions greatly appreciated.:eek:
I have a table that I want to sort by the by class year. But i have like 20 options that are yes/no that i want to count the total of each so that i can do charting in excel.I have no idea how to create the query. I know that I only have to count the yes's.Bascially I need to my query to show me something like thisYear AE ALD TUTOR FS2006 3 5 8 12007 5 5 9 0Year is a numberAE, ALD, Tutor, FS are boolean yes/noi don't want the select to find what is true for all just count each field individually but display it together.Does that make sense?I am really lost please help.
View 1 Replies View RelatedHi 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 have a Query which im using for a program that controls my Bill Of Materials(BOM) for my products Now i need a second Query to add up my total for each product.
Query1
BOM | ItemNo.| Cost
ProdA 1 $3
ProdA 2 $2
ProdA 3 $2
ProdB 1 $4
ProdB 2 $4
ProdB 3 $1
What i Want the second Query to read is
BOM | Total
ProdA $7
ProdB $9
How do i get it to add up.
Hi,
Am attaching a db related to my question
I have 2 tables "Summary Table" and "Select1Summary"
"Summary Table" shows projects, departments and the available hours for each department
eg.
In "Summary Table" for 71043-40 Project, there are 1808 hours available at the beginning
"Select1Summary" shows Project, department, Month and the hours each department will be booking
eg.
In "Select1Summary" Table(Actually its a summary query, but am putting it as a table)
for 71043-40 Project, Department 11 on 30/04/2007 will be booking 171 hours (ie in April dept. 11 books 171 hours for project 71043-40)
for 71043-40 Project, Department 11 on 31/05/2007 will be booking 135 hours
for 71043-40 Project, Department 11 on 30/06/2007 will be booking 108 hours
What i need to show is running totals in a new query like
for 71043-40 Project , Department 11 on 30/04/2007 Available hours = 1637 (ie 1808-171)
for 71043-40 Project, Department 11 on 31/05/2007 Available hours = 1502 (ie 1637-135)
for 71043-40 Project, Department 11 on 30/06/2007 Available hours = 1394 (ie 1502-108)
Kind Regards,
Charley