Getting Totals By Adding Other Fields Within A Table
Mar 25, 2008
Hi there
I am completely new to access so if this question seems really simple then i apologise. If this has appeared elsewhere on this forum i apologise again!
Can you tell me if the following is possible
I have a table called AA1 containing the following fields:
(PKey)Refno
StudentNo
PC1
PC2
PC3
PC4
PC5
PC6
Range1a
Range1b
What i would like to do is create another two fields called PC total and Range total whereby i create a formula that will add the totals of the PCs for each student )which will then be entered into the PC total column and the same for the ranges.
I am unable to do it using the query function as i have over 225 diffrerent fields overall so i thought if i could create a totals field i could use these for my queries therefore negating the need to include all the seperate fieids, pc1 pc2 pc3 etc...
the above fields are all in number format.
I have probably not explained myself very well and if you need any more information (im guessing yes) then please let me know.
thanks in advance
Jemma
View Replies
ADVERTISEMENT
Aug 13, 2015
I've got a table with data that's been imported from Excel. I need to run a query that pulls the same values of field one and adds the values of field 2. For instance:
Field 1; Field 2
Jones; 200
Smith;150
Jones; 300
Smith;100
In this example, I need the sum of field 2 for the records where Jones is the value in field 1.
So the result in this case is that records where Field 1 = Jones, Field 2 = 500.
View 2 Replies
View Related
Jan 29, 2014
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code:
SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay
FROM [Tble-wcDelays]
GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID
HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
That is the query.
View 14 Replies
View Related
Feb 20, 2007
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.
View 1 Replies
View Related
Jul 18, 2013
I am trying to add two columns with multiple records in them together using VBA.
I have one column named "Quantity" and that has 5 records going down
Column1(Row1) = 2
Column1(Row2) = 3
Column1(Row3) = 1
Column1(Row4) = 2
Column1(Row5) = 2
I have another column name "Quantity2" and that has another 5 records going down
Column2(Row1) = 1
Column2(Row2) = 4
Column2(Row3) = 2
Column2(Row4) = 2
Column2(Row5) = 2
I need a code to simply add them all together together using VBA and put the value back in a text box.
So the Value in the text box would be = 21
View 9 Replies
View Related
Feb 6, 2006
I have 2 fields in a table 1. called CommAmtDue and the other called CommAmtPd, When the user inputs a value in the CommAmtPd I want the CommAmtDue - CommAmtPd = CommAmtBal
I want this value to be stored in another field in the same table called CommAmtBal
I can do the formula in a query but how can I get the value to be stored in the field to user for later.
Thanks
View 14 Replies
View Related
Mar 20, 2008
This is going to sound like a bizarre question, but, here it goes. Is there a way to add more than 255 fields to a table? Or is 255 the cutoff? Also, if you have two table with different information on one client, is there a way to add a client to one table and have it automatically add it to the second? Sorry about all of the wierd questions. Let me know if you know anything.
View 3 Replies
View Related
Sep 8, 2006
Hello first of all I have learned a lot from this forum so thanks you all who post questions & solutions. Ok I have a table which I use to query info and export to excel. My question is there a way to add sum totals to a few of the columns once it is in excel. I was thinking it would be easier to do a sum statement then just add it as the bottom row while it was exporting. Or would it be easier to do it once it is in excel. Here is my code:
Private Sub Command63_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()
strSQL = "SELECT tblCONSOLIDATED.ACCOUNT1, tblCONSOLIDATED.COMPANY_NAME, tblCONSOLIDATED.CUSTOMER_TYPE, tblCONSOLIDATED.ADDRESS1, tblCONSOLIDATED.ADDRESS2, tblCONSOLIDATED.CITY, tblCONSOLIDATED.STATE, tblCONSOLIDATED.ZIP, tblCONSOLIDATED.CONTACT_NAME, tblCONSOLIDATED.E_MAIL, tblCONSOLIDATED.TELEPHONE, tblCONSOLIDATED.FAX, tblCONSOLIDATED.REP_NUMBER, tblCONSOLIDATED.PROMOCODE, tblCONSOLIDATED.SALESCODE, tblCONSOLIDATED.CURRENT_YTD, tblCONSOLIDATED.PRIOR_YTD, tblCONSOLIDATED.PRIOR_TOTAL, tblCONSOLIDATED.YEAR2_TOTAL, tblCONSOLIDATED.YEAR3_TOTAL, tblCONSOLIDATED.YEAR4_TOTAL " & _
"FROM tblCONSOLIDATED"
strWhere = "WHERE"
strOrder = "ORDER BY CURRENT_YTD DESC"
If Not IsNull(Me.txtCSONME) Then
strWhere = strWhere & " (tblCONSOLIDATED.COMPANY_NAME) Like '*" & Me.txtCSONME & "*' AND"
End If
If Not IsNull(Me.txtCSOSLD) Then
strWhere = strWhere & " (tblCONSOLIDATED.ACCOUNT1) Like '*" & Me.txtCSOSLD & "*' AND"
End If
If Not IsNull(Me.txtCSOARN) Then
strWhere = strWhere & " (tblCONSOLIDATED.CONTACT_NAME) Like '*" & Me.txtCSOARN & "*' AND"
End If
If Not IsNull(Me.txtCSOCTY) Then
strWhere = strWhere & " (tblCONSOLIDATED.CITY) Like '*" & Me.txtCSOCTY & "*' AND"
End If
If Not IsNull(Me.txtCSOST) Then
strWhere = strWhere & " (tblCONSOLIDATED.STATE) Like '*" & Me.txtCSOST & "*' AND"
End If
If Not IsNull(Me.txtCSOZIP) Then
strWhere = strWhere & " (tblCONSOLIDATED.ZIP) Like '*" & Me.txtCSOZIP & "*' AND"
End If
If Not IsNull(Me.txtCSOSSM) Then
strWhere = strWhere & " (tblCONSOLIDATED.REP_NUMBER) Like '*" & Me.txtCSOSSM & "*' AND"
End If
If Not IsNull(Me.txtCSOM1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PROMOCODE) Like '*" & Me.txtCSOM1 & "*' AND"
End If
If Not IsNull(Me.txtSLCYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.CURRENT_YTD) BETWEEN " & Me.txtSLCYYD1 & " And " & Me.txtSLCYYD2 & " AND"
End If
If Not IsNull(Me.txtSLLYYD1) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_YTD) BETWEEN " & Me.txtSLLYYD1 & " And " & Me.txtSLLYYD2 & " AND"
End If
If Not IsNull(Me.txtSLPYR11) Then
strWhere = strWhere & " (tblCONSOLIDATED.PRIOR_TOTAL) BETWEEN " & Me.txtSLPYR11 & " And " & Me.txtSLPYR12 & " AND"
End If
If Not IsNull(Me.txtSLPYR21) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR2_TOTAL) BETWEEN " & Me.txtSLPYR21 & " And " & Me.txtSLPYR22 & " AND"
End If
If Not IsNull(Me.txtSLPYR31) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR3_TOTAL) BETWEEN " & Me.txtSLPYR31 & " And " & Me.txtSLPYR32 & " AND"
End If
If Not IsNull(Me.txtSLPYR41) Then
strWhere = strWhere & " (tblCONSOLIDATED.YEAR4_TOTAL) BETWEEN " & Me.txtSLPYR41 & " And " & Me.txtSLPYR42 & " AND"
End If
If (Me.PROSPECTBX) = True Then
strWhere = strWhere & " (tblCONSOLIDATED.CUSTOMER_TYPE) Like 'P' AND"
End If
If Not IsNull(Me.txtSLCLS) Then
strWhere = strWhere & " (tblCONSOLIDATED.SALESCODE) Like '*" & Me.txtSLCLS & "*' AND"
End If
If strWhere = "WHERE" Then
strWhere = ""
Else
strWhere = Trim(Left(strWhere, Len(strWhere) - Len("AND")))
End If
Set qryDef = dbNm.QueryDefs("qrySALESDATA")
qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
DoCmd.OutputTo acQuery, "qrysalesdata", "MicrosoftExcel(*.xls)", "QUERY RESULTS.xls", True, ""
End Sub
Sorry for adding the whole thing but I'm not sure it should be done after "Query Results.xls" is written or during the select statement.
Thanks in advance for your help.
Bryan
View 1 Replies
View Related
Jan 13, 2005
Probably simple, but I cannot see how I add fields from different tables to a form that has already been designed. I can see how I achieve this when designing from scratch.
Thank you
View 3 Replies
View Related
Jul 17, 2014
I have some tables in many .mdb that I will like add or subtract fields. I will like to use VBA to do the job without using Access.03 interface.
I do remember using TableDefs object & the functions .Delete & .Append - That is right way to go is it not??
View 8 Replies
View Related
Sep 4, 2015
I have 3 reports
Report 1. is attendance from January to June
Report 2. is from June to December
Report 3. is from January to December
The are all run from the same query with different Between Dates.
The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values
Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.
This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.
Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.
If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.
The way it is now I have to run all 3 report and do the math by hand this way.
Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?
View 1 Replies
View Related
Oct 4, 2007
If I have a make table query where I want to add in some blank fields, say: Field1: “” , Field2: “”, etc…. is there a way I can make these fields a yes/no type instead of the default text?
I know I can manually go into the table in design view after I run the query, but I was hoping I could do it beforehand. :)
Thanks!
View 1 Replies
View Related
Dec 3, 2007
Hello: Is there (simple) way that I can populate a field based on the values of two other fields? For example, in a list of new employees, if a new employee starts on Nov 30 and works in the Administration Department, his human resources contact is Mary. If a new employee starts on Nov 30 and works in Sales, his contact would be Fred. I hope this is clear! Many thanks!
View 12 Replies
View Related
Jan 31, 2005
I have a form which I've spend many hours designing to look and behave as I need it. It is bound to a table (get's it's data from there). I now want to add one or more new fields to the table (which I've done ok), but I can't seem to get the Form to see those fields. I've tried creating new controls on the form and the control source list to select from doesn't include my new fields.
Even if I write VB code embedded in the form to refer to the fields, the debugger trips on the field names saying no such method/object. The only way around it I've discovered is to create a new bound form which will mean re-dooing al the layout work again.
Surely this is a common situation which has an easy solution ?!!
View 4 Replies
View Related
Sep 5, 2013
We recently have had a change to our hourly rate that we pay and now the Totals calculation is not adding up the numbers correctly.
In the query that the report is based from:
ExtendedTotal: CCur([Hours]*[Rate])
In the footer of the report: (Provides a Total for each Day)
Sub-Total: "Control Source" is: =Sum(CCur([ExtendedTotal]))
Report Footer: (Provides a Grand Total for the Month)
Total: "Control Source" is: =Sum([ExtendedTotal])
Problem comes in with the odd dollar rate time partial hours. For example:
$9.73 x 4.5 hours = $43.785 (Rounds to $43.79) which is fine...
but when you have a whole column of these rounded numbers, the totals are coming out off by pennies which add up to a good bit at the end of the month. I cannot match up the totals with the invoices that are coming in either.
View 14 Replies
View Related
Mar 14, 2008
I've got a fields called rev code that contain the following values:
field name: 110 131 250 255 258
field value: 7.49 6 11.25 12.11 78
I'm writing a query that pulls from the first two digits of the rev code and need to round off to the nearest dollar so in my query I'll have a column 11 with a value of 7, a column 13 with a value of 6, then I need to take columns 250 255 and 255 add the values together and round off so I get a column 25 with a value of 101.
How do I do that?
View 14 Replies
View Related
May 13, 2013
I am a beginner in access and I want to populate a new field (REPUN_1 which corresponds to SEGMT_ID in the other table) in my table and this field is coming from another table. The values of the fields need to correspond to the row of my actual table (JMTable) having the same CO_ID, MOVEPLANCD and TTY_NO as the table I want to extract the values from (DI_Treaty_Crosswalk).
Here is my query:
UPDATE JMTable SET [REPUN_1] = DI_Treaty_Crosswalk.SEGMT_ID WHERE JMTable.TTY_NO = DI_Treaty_Crosswalk.TTY_NO AND JMTable.CO_ID = DI_Treaty_Crosswalk.CO_ID AND JMTable.MOVEPLANCD = DI_Treaty_Crosswalk.MOVE_PLAN_CD
and it doesn't work since access ask me to enter a parameter value.
View 1 Replies
View Related
Apr 25, 2012
I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?
View 7 Replies
View Related
Oct 23, 2013
I want to add three fields in a table namely from, to and diff .
How to add them in a form so that I can enter
The time in (xx:xx) 24hr format in the "from" and "to" fields and calculate and save the time difference in minutes to the "diff" field automatically.
I am using access 2010.
View 8 Replies
View Related
Oct 24, 2014
I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.
I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.
I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.
I then have a reconcile form which is on a datasheet form straight from the tab;e so it is editable which has a checkbox that then once ticked copies the total from the Order_Total in the table to the reconciled field in the same table and then I use a report to show who owes me still.
So I have made a Select query from the Orders table & Order_Details table and used a Group by Order number (Which is the link between the 2 tables) this shows correctly but now not editable because of the rules So I am trying to add an editable checkbox on the same form.
I tried to use a dlookup makes all of the boxes either ticked or not. I've been looking at Recordsets but I'm unsure if this is what I need or not really
So really I just need to see if I should be putting the Total from the Subform into the mainform and then entering it into the table (Like previously) using a calculated unbound field and then using the Afterupdate event to insert the data into the table. but for some reason it just wont work.
I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to 0.00 but then the table shows correctly which ever record the form is working on.
Or should I be using the new query based idea to create the reconcile form and if this is the best way I just don't now where to start on how to get a multiple table query that I can then add a reconciled checkbox which then copies the Order_Total from the table to the Paymet_Recieved field.
View 13 Replies
View Related
Aug 24, 2005
Hi,
I have a query which finds the averages for 10 diferent fields grouped on a different field.
I would like to produce a chart comparing these 10 vaules in a bar chart, 1 chart for each value in the grouped by field.
To do this I think I need to have the fields as rows, with the grouped field values as fields.
Does anyone know how to do this without writing a module from sctrach, or can I produce the chart I want from the data in the format I already have it in.
Thanks in anticipation
Sue
View 3 Replies
View Related
Sep 4, 2014
I'm fairly new to Access but I've been tasked to re-jig an old databse running on Access 2003. So far the database is doing more or less what I want it to but I need to generate a report showing totals from multiple fields in combo boxes. As an example, I have a combo box for a job booking-in form which requires the type of task to be selected e.g Video, photography, editing. The report needs to show the total booking numbers for each tasks (combo field) at the end of the month. I've managed to create reports for totals of a single field in the combo box but I'm struggling in achieving the multiple totals.
View 7 Replies
View Related
Dec 10, 2014
I have a Report that has calculated fields in the details section. I want to total those fields in the Report Footer. When I run the report I am prompted to enter the value for the calculated fields and the Totals do not appear. Access must be inspecting the report before it actually runs. And so, since the calculated fields do not exist until the report is run then Access doesnt find them during the inspection and hence prompts for input.
View 2 Replies
View Related
Aug 15, 2013
I reached the limit of 255 fields in a table. I just need to add one more field so I deleted several fields I no longer needed thinking I would then be able to add one more new field. However, I am still unable to add one more field. How to free up fields that are no longer needed?
View 8 Replies
View Related
Jun 6, 2007
Hi,
I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.
When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
View 3 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
