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
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;) Hey everybody,
I am working on a database used in recording device characteristics/test information. The main table of information has dozens of columns for test/part detailed information. When inputing the data for each specific test, many of the info. details are repeated when testing say 20 devices of the same part all at once. Rather than retype every piece of detailed information in every field, everytime, is there an easier way? Does anyone know of a way to make specific fields copy/paste the previous record's information in the fields automatically when a new record is created? Please, if anyone could help or has ANY ideas, let me know...
Thanks
gunwax
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!
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 RelatedOn my database I have a text box that accepts a map grid that starts with two letters. If the letters arnt within certain perameters then the box turns red and a message box appears informing the user of this. My probem is that I am using a continuous form and if one box goes red, they all go red if the grids are OK. my code is below, can anyone help?
Private Sub Grid_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Grid) Then Exit Sub
If Me.Text289 = "UK" Then
Select Case Left(Me.Grid, 2)
Case "ST", "SY", "SU", "SZ"
Me.Grid.BackColor = 16777215
Case Else
MsgBox "Invalid Biagram for your chosen Database.", vbExclamation
Me.Grid.BackColor = 225
Cancel = True
End Select
End If
If Me.Text289 = "Germany" Then
Select Case Left(Me.Grid, 2)
Case "MB", "NB", "NC", "MC"
Me.Grid.BackColor = 16777215
Case Else
MsgBox "Invalid Biagram for your chosen Database.", vbExclamation
Me.Grid.BackColor = 225
Cancel = True
End Select
End If
If Me.Text289 = "Desert" Then
Select Case Left(Me.Grid, 2)
Case "NJ", "NH", "NK"
Me.Grid.BackColor = 16777215
Case Else
MsgBox "Invalid Biagram for your chosen Database.", vbExclamation
Me.Grid.BackColor = 225
Cancel = True
End Select
End If
End Sub
i developed a program and i used ms access for the database.
i compiled the program i made.
the question is, can i install the program i made into another computer eventhough it doesn't have a ms access? because i tried to install but it doesn't work at all. what do i need to do in order to make it work.
your response is greatly appreciated.
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
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.
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.
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 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
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
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.