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.
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.
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()
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
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.
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?
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.
I have a table that has a column of Equipment Numbers (e.g. CN330) and another column with corresponding costs. The Equipment Numbers are not unique. A number may appear many times in the column and the cost may be different each time. (The source data for this is an Excel spreadsheet provided by another department).
I need the totals for each unique Equipment Number but not in a "messy" format like subtotals. I need to use it as a lookup for another part of my database so want just two columns; unique Equipment Numbers in the first column and the total cost for each in the next column.
I have the unique Equipment Numbers in the first column of a new table but cant figure out how to get the total costs.
Is it possible to set up a view within an access app to show a number of different "grand totals", related to one or more tables. For example, I'd like to display the total value of all orders, the total number of orders and the total number of items ordered, on a single view. In this case, all the information comes from one table - Orders, which contains fields - Total Cost and Quantity. I've tried to create a Summary view, but that requires me to use the "Group By" function, which I don't want here, so I tried to do it with queries, but it seems to involve multiple queries, which then means multiple views.
I have set up this database here for my new sole trader business. In it I enter data of my purchases and my gross sales. I have generated 2 queries both based on the same tables. 1 calculates the purchases and the other the sales.
I want to be able to subtract the purchases from the sales so that I can see my profit at any point in time.
The complication is brought on I think from the fact that I am using one table for both Purchasing and Sales. However, I use a filtering method to separate the two.
I am trying to obtain totals from two columns in the list box into text boxes on the main form, but my third argument is not working as expected.The source of one of the tex boxes is:
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!
I have a report...with one number at the end of a line, beneath is a sum() of all the numbers above.
To test this report, I have ensured there is just one line to 'sum' ....this particular number happens to be $15.82, yet the field showing the sum of this is displaying $15.83 ...I'm going potty here trying to work out where the extra 1 is being added...it's not a rounding error as there are no further decimals involved, both fields are set to 'fixed' 2 decimal places.
i have a tbl that stores items that have an expiry. in a child table i want to store the expiry. but as it is different for each item i need a way of doing this.
i thought that by having
DateAdd('m',3,Date()) in one of the fields this would work.
on click event i would like the date add to come into play but the way i am doing it comes up with an error. too few arguments.
this is what i have so far
Code: Dim rs As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb
I have created a profiling database for my school. Each student can access their own form via the school's admission number - which I use as the primary key for most of the tables on the form. One section of the form shows a photo of the student. I take this photo from the school's SIMS photo file which had all the children's photos. Each photo in the file uses the school's admission number as its filename (for example 000358.jpg ).
I want to update the photos in my table by using a macro or piece of CBA code. Unfortunately, I don't know how to do this. When I set the database up I did each photo one at a time, so it took several hours.
I have a main table with subject id and other fields. I also have 16 other tables which have questionnaire information at 17 different timepoints. In those tables, the subject id is the only connected field between the main table and 17 different years. When I add a new record (by subject id) on the main table, I want it to add that same subject id to the 17 other tables because that subject id will eventually get all 17 years worth of data. But even using subforms, it will not add a new subject to the other tables unless I add some sort of information such as (date received) into the subform.
I am looking for a way to store thumbnail images for specific records. I want an idiot proof way of selecting a jpeg image and then storing a 200x120px bmp version of the file in the database. Is this achievable with VBA code?
I want to send out my database to a client so they can add records into it via the forms i've created, but I don't want them to be able to change the coding etc. But I also want to be able to update my database and send it back out to them but obviously the records they've entered need to remain. Do i convert it to an accde and then back to an accdb, make my changes then convert again to accde?
The database's core is 3 tables: a table for service trips that students can go on, students who may go on those trips, and a junction table linking the two in a many-to-many relationship. Data entry is done literally from the trip table with a +/- node allowing addition of students to the trip detail table.
About 6 months ago, the user's computer was replaced and their office suite was upgraded from 2003 to 2010. Since then, periodically they have called me to say that they are not being allowed to add students to trips. Checking and discovering that I, accessing the same database from another machine, am able to add students to trips;Compacting and Repairing the databse, which did NOT resolve the issue;Opening a brand new database and importing all the objects into it, which DID resolve the issue, temporarily.I just received an email today stating that it has just happened for the 4th time. I can repeat my above-mentioned fix which will probably resolve the issue - again - temporarily.
I have a database where on the form we are just put in total deductions. We want to make sure that the total deductions is correct so while we are entering this number we want to do an excel thing by hitting = 1 + 1. Anytime we do that we are getting an error message. I really didn't want to have to set up a table to store these different amounts because we really only need the total. Is there a way to do that? I changed the format from currency to general but I'm still getting the error.
I have a form for adding new records. They are for people and I add in a bmp image of them. I want to create an automation. Where you press a button to add a photo. Find a jpeg you want. Then it is resized, converted to bmp and saved in the database. Is this easy enough to achieve? (size 256x188px)
I'm currently working on a way to import data from many word documents into a table - I've got this working fine using the following code:
Code:
Private Sub bImportFiles_Click() On Error GoTo bImportFiles_Click_Err Dim objFS As Object, objFolder As Object Dim objFiles As Object, objF1 As Object Dim strFolderPath As String
[code]....
However, I'm wanting to add a link to those files to the table so that the user can click on the record (or select one and then use a button) to open each document in it's native environment (MS Word). I've tried adding a self-referencing hyperlink to a form in the word document and then reading that in - but that doesn't seem to work. As these files may move around (and there are a lot of them) I need to make sure it's done automatically so can't really get the user to write it in manually.
When using the runtime version, there is an issue with trusted locations. The only way around this, it seems is to edit (add) the registry key,My research tells me the key to be added is:
If I use the Packing Wizard with Access 2007 to prepare an installation disk, there is the option for Additional Registry Keys, which looks promising, however, I am unsure how to proceed. You are asked: Root, Key, Name and Value. There are 4 options for Root: Default root, Current user, All users, Classes root.
