Arrg! Totals Work In One Field And Not Another
Feb 2, 2006
Hi all. I have three tables in my dbase related by user ID. All of the fields from the main table (client) are on the "Client" form. This table/form contains a number field called "Total Hours". There is a subform (Service Items) on the "Client" form which contains info from the "Service Items" table. The two are related by Customer ID. I can get a total of the number of hours spent on each client. It shows as a field in the footer of the "Service Item" form like so:=Sum([LaborHours]). Fine that part works.
What I dont know how to do is add another field to that footer that shows the result of the "Total Hours" from the Client table minus the Sum of Labor Hours from the Service Item table.
Basically a client would start with a set number of hours (Total Hours) and count down (minus the sum of LaborHours). How can I show the remaining hours?:confused:
It seemed like it would be easy but dang! Thanks for any help you can give...
View Replies
ADVERTISEMENT
Dec 6, 2004
I have 4 fields in my table and form (Towing Charges) (Storage Charges) (Other Charges) and (Total Charges). I need to have Towing Charges, Storage Charges & Other Charges to calculate and populate in my Total Charges field. I tried a script in the control source of the field, but its not working. I probably have the script wrong.
Thanks Everbody
View 3 Replies
View Related
Jan 4, 2007
Hi Folks,
First of all i apologise for posting double, but i m compelled. I badly need help. i m not an expert like you guys. I hope someone will surely come to my rescue.
I have a problem with summing values in a query that is based on 5 tables.
In my form i have a text box which shows the total quantity on hand. The formula comes from all 5 table fields. for example:
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
The formula works this way:
Expr1: [trx_qty]+[recd_qty]-[Trx_Qty1]-[Sent_qty]-[Issued_Qty]
When the records are limited to one row only then all works as desired, but as soon as i add more records in REPAIR RECEIVED Table and REPAIR SENT Table i get multiple rows in the form. Also the sum is not correct. For example:
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
In the above example the formula gives following result
Expr1: 40 + 5 – 10 – 10 – 20 = 5
Expr1 = 5 (This is quantity on hand)
Bu now when I add more rows to other tables I get multiple rows and incorrect sum. Example
GOODS RECEIVED GOODS SENT REPAIR RECEIVED REPAIR SENT ISSUES
Trx_Qty Trx_Qty1 Recd_Qty Sent_qty Issued_Qty
40 10 5 10 20
5
I m attaching the database for you to have a look at it and come up with the solution.
Please help me I m badly stuck
I tried using group by and sum functions but no use
[Sumoftrx_qty]+[recd_qty]-[Trx_Qty2]-[Sent_qty]-[Issued_Qty]
Please Help me out of this mess. It will be highly appreciated
i will b gr8ful
Thanx once again for investing ur time in reading
Note: Expr1 formula may be different in database as quoted here on this forum due to trial and run.
Regards
Darno
View 5 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
Feb 13, 2015
I 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.
View 4 Replies
View Related
Jun 13, 2013
I'm trying to create a query that will sum the total time of a specific field. It seems to be doing it, however the value is off by by a couple minutes on all my examples.
Table
- BusArrivalTime
- BusDepartTime
Query
In my Query I'm making a new field like this. It correctly figures out the difference.
BusWaitTime: DateDiff("n",[BusArrivalTime],[BusDepartTime])
Report
Next I'm displaying that Query information inside of a Report by putting this in the Text Box on the Report. It correctly displays the time in the Hours/Minutes format.
=[BusWaitTime]60 & Format([BusWaitTime] Mod 60,":00")
Lastly, I'm using another Text Box on the Report to Sum the Grand Total of the Wait Time for all my records. Here is what Im putting in the Control Source
=Sum([BusWaitTime])60 & Format([BusWaitTime] Mod 60,":00")
...and it's summing my records, but the value is off by a few minutes and I cant figure out why.
In my example, I have 3 records with times of 3:14, 1:35, and 3:20. It should be totaling a figure of 8:09 but its coming to 8:14 instead.
View 7 Replies
View Related
Jul 18, 2013
I have 2 tables (person / merit) currently the merti table has 4 fields (MeritID, PersonID, Issuer, Type) I'm trying to count the the Type field 7 times, once of each type (MC/MI/MN/MP/MS/MV) and once to count a grand total.
I want to produce a query to use in a mail merge that will list the total for each merit type and one grand total.
I've been trying a few different things including sub queries but I've not managed to get the query to produce a single record that counts each individual type.
View 4 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
Sep 27, 2005
Could someone look at my code and tell me why my “Me.TargetDate.Undo” won’t work?
First, let me say that the TargetDate field has an On-Click event that calls a module written by Allen Browne), which is shown below and works great.
=CalendarFor([TargetDate],"Select a date from the calendar ")
What happens is that the Me.TargetDate.Undo event always keeps the date selected in the calendar.
Based on something I read from Rual Guy on another issue, I’ve used the TargetDate_LostFocus routine to check for some additional data entry errors. I’ve tried moving the code in “Private Sub TargetDate_Exit(Cancel As Integer)” to the LostFocus, but it doesn’t make any difference to the Undo. I’ve also tried “I’ve tried “Private Sub TargetDate_Change(),Me.Undo…” but that doesn’t work either. :confused:
Private Sub TargetDate_Enter()
If Not IsNull(Me.TargetDate) Then
OldTarget = Me.TargetDate
End If
End Sub
Private Sub TargetDate_Exit(Cancel As Integer)
If Not IsNull(Me.TargetDate) Then
If DateDiff("d", Me.DateOpened, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is before…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf DateDiff("d", Date, Me.TargetDate) < 0 Then
MsgBox "You have selected a date that is prior to today's date…bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
ElseIf Weekday(Me.TargetDate) = 1 Or Weekday(Me.TargetDate) = 7 Then
MsgBox "You have selected a date that falls on a weekend..bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End If
End Sub
Private Sub TargetDate_LostFocus()
If Me.TargetDate > OldTarget And Me.cboAssignToID <> OldAssignee Then
MsgBox "The target date can only be changed if …bla bla"
Me.TargetDate.Undo
Me.TargetDate.SetFocus
End If
End Sub
Ever thankful for this forum,
Christine
View 1 Replies
View Related
Nov 20, 2014
I am trying to make a query using fields from multiple tables. I used the Expression builder to create it and it is a formula field where multiple table fields are involved. But whenever i run the query Access asks for a parameter value in a modal dialogue for the value of the fields. Is it because the field names has Square brackets around it? But access puts that automatically. How to make it work properly?
View 11 Replies
View Related
Mar 17, 2014
I have a
Order Table:
Order ID(Autonumber)
Client ID(Text)
Client Name(Short Text)
Install Gross Rate (Currency)
Install Discount Given in % (Number)
Install Discount Given in GBP (Currency)
Then I designed a Query to calculate the Install Net Rate
Query
I selected
Client ID
Client Name
Order ID
Expression: [Install Gross Rate]*(1-[Install Discount Given in %]/100)-[Install Discount Given in GBP]
I run the query, but only ONE order calculated correctly, rest of Orders returned blank row.
View 10 Replies
View Related
Jan 14, 2015
I am attempting to use a form field as the source for a query filter criteria. Everything works fine if I simply use an "if equal" filter condition. As soon as I try a "like" condition, nothing works.
I created a test table with just one column (fld1). The table contains three records with the following values: BRDODS, BRD, TLAODS.
The following hard coded query returns two records, as it should.
SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1) Like 'BRD*'));
I also created a test form (Form1) with just one text field (Text0). My intent is to soft code a criteria value via the form field instead of hard coding the query, as above. When I populate the form field with BRDODS, the following soft coded query returns one record, as it should.
SELECT Table1.fld1
FROM Table1
WHERE (((Table1.fld1)=[Forms]![Form1]![Text0]));
When I enter LIKE "BRD*" in the form field, no records are returned. I should get two records, just like the hard coded query above.
I've tried all variations of the LIKE statement in the form field, but nothing works.
View 8 Replies
View Related
Feb 27, 2006
We are creating a simple database to maintain driver license information for faculty, staff, and students who use cars from the university’s motor pool.
To do this, I have created two tables: tblDRIVER and tblLICENSE.
The fields in tblDRIVER are:
pkfDriverIndex
strLastName
strFirstName
strInitial
strAddress1
strAddress2
strCity
strState
strZIP
datBirthDate
The fields in tblLICENSE are:
pkfUpdateIndex
fkfDriverIndex
datDateUpdated
strState
strLicNumber
datExpirationDate
ynViolations
ynActive
Information about the driver is stored in tblDRIVER and the driver’s license information is stored in tblLICENSE. Periodically, we run a report that identifies drivers whose licenses are due to expire within a certain number of days. All this works fine.
My problem is that I am trying to create a lookup form that will load from a data entry form that will permit the Motor Pool Clerk to look up a driver by name and review the licensing information (which appears as a subform).
All this sort of works- I am using a combo box (based on a query) to look up a driver’s last name (which it does) and to populate all the driver’s information on the look-up form (including license information in a subform). Currently, the combo box locates the driver (including the unique index, last, and first names), and populates the form with first and last name but the rest of the information is not displayed on the look up form. Worse still, sometimes one driver’s last name matches up with the first name of the next driver listed in the table! This seems to happen only if a look up is attempted more than once. What gives.
Thanks for the help.
View 9 Replies
View Related
Sep 10, 2007
I have an expression in one column of my query and It keeps returning a negative result and there are no negative numbers in the source data. Can anyone help. I just want to sum the sums the two IIf expressions, but its not working. Thanks
Other Qty: Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=95 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null)) Or Sum(IIf([QSum]![ACCOUNT]=2 And [QSum]![ICUNIT]=100 And [QSum]![UNITS]="ITEM(S)",[QSum]![QUANT],Null))
View 4 Replies
View Related
Jul 27, 2007
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!
View 10 Replies
View Related
Nov 10, 2006
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 Related
Mar 7, 2007
i 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 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 2 Replies
View Related
Aug 8, 2005
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.
View 5 Replies
View Related
Sep 12, 2005
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.
View 2 Replies
View Related
Feb 22, 2005
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
View 10 Replies
View Related
Jun 23, 2005
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.
View 1 Replies
View Related
Jul 6, 2005
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
View 1 Replies
View Related
Aug 25, 2005
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
View 6 Replies
View Related
Sep 12, 2005
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
View 2 Replies
View Related
Nov 9, 2005
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.
View 6 Replies
View Related
