WHERE "Daily VDNs".ACD = "IVR VDN Filter".ACD AND "Daily VDNs"."VDN Number" = "IVR VDN Filter".VDN AND (("Daily VDNs".Date=?) AND ("IVR VDN Filter".NSWCTP Is Not Null))
But I cant get it to work when I change "Daily VDNs"."Inbound Calls" to a sum (code below)
SELECT "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP, Sum("Daily VDNs"."Inbound Calls") AS 'Sum of Inbound Calls'
Job Effort is a has 3 values: My Corrections, Others, YOYO
I would like to know the: Sum of TotalWorkedPgs when Job Effort is My Corrections Sum of TotalWorkedPgs when Job Effort is Others Sum of TotalWorkedPgs when Job Effort is YOYO
Because I need all of this information on a report.
I'm struggling with a non-updatable query issue. I have a table at the category level that should allow entry but it needs to pull in information from another query. In this 2nd query, its doing group by/counts/sum's from an item level table. From reading about the dynasets and trying to fix it, it looks like this the major problem.
The item level table has information that is changing based on other user input so I don't think using a make table would work. I tried setting the queries/form to dynaset (inconsistant updates) but no luck there. It still wouldn't allow any changes to the entry field. Any clue on how I could get around this?
Main Table Category Entry A. . . . ._____ B. . . . ._____
This query built using the main table joined with a group by/sum of the item table. Goal Query for Updates Category Sales Entry A. . . . . . . $10. ____ B. . . . . . . $25. ____
I have a query which returns records in 5 different fault categories; mechanical, service, electrical, damage and retrofit. The values the query return are based on another query and are a sum of the amount of money spent in each category.
I have put a sum fuction in the 'field' box like this; total: ([Mechanical]+[Service]+[Electrical]+[Damage]+[Retrofit])
I also have a criteria set up where every time the query runs a specific id has to be entered.
The problem is when i run the query with the function shown above i have to enter the id to run the query then it asks me to enter the id again for the 'Retrofit', it then runs the query but adds the value i have entered in the criteria for retrofit to the totals of the other fault categories!!!
If i remove the retrofit part of the sum funtion it works fine
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 form whose data source is a select query, q3, that is built from 2 other select queries. I'll call them q1, q2, and q3. q1 is a parameter query where I enter a "Cutoff Date" that the 3 queries manipulte and generate the desired results that appear in the form. The problem is that I don't know how to capture the parameter "Cutoff Date" from q1 to display on the form.
I've been trying to use a sum in a query to add up the currency of several records in a field from another table but as yet have had no success. The sum simply shows all the records but on there own and not in one total :confused:
I also need to use a count to total up the number of records in a table with a certain piece of data, in this case a 1, but again it does the same as the totals and simply shows each record on its own
If anyone knows what I am doing wrong I would greatly appreciate some help and advice
This has got to be really simple but I just cant figure it out because Im a novice.
I have a dbase based on clients and each client has a subform for where you can enter payments owed and payments received.
The queries run smooth when there is data but if there are no records for the payments owed or payments received, I get an nothing and #errors when I try and manipulate the data.
Im guessing this is a null value error and Ive tried the IsNull and Nz fuctions but I cant get them to work for me.
Should these functions be placed in the query which calculates the sum? If so, how?
For example, my query is
Field:Hearingwcb Table:HearingShowQuery Total:Group By Criteria [Contacts]![wcb]
This extract the appropriate records for the current client
Field: HearingFee Table: HearingShowQuery Total: Sum
This sums them up.
Im guessing because the sum is null, I am getting nothing. How can I get this to return a 0. I think that should take care of my problem.
I have a report that is broken up by invoice number. I want to create a text box that will sum the lines for each invoice number. I have the text box set to add the lines, but currently it is grabbing the first value for each multiple line invoice. Confusing, I know. See below:
Invoice number Date Amount 123456789 5/23/05 23,100.00 123456789 5/23/05 1,000.00 Amount: 23,100.00 <--- I can get this to = the first line for each invoice number but not total (should = 24,100.00)
Invoice number Date Amount 987654321 5/24/05 525.69 Amount: 525.69
I basically want to sum totals(or rather subtract totals) from groups in a report. The problem is that all my numbers are positive and need a way if possible to change some to negatives.
I have 3 categories set up e.g
Account Payable , Accounts Receivable and Non-Revenue
In the form used for input , there is a drop down box in which you select 1 of the three account types. There is second box in which you input the amount (always positive). This second box is common to all account types therefore no matter the account you input the dollar values into this second box.
Right now the report is summing all values in the group so that it's not subtracting account payable amounts from account receivable amounts...Any help would be appreciated, and hopefully it will be a simple solution. NOTE: i would like to stay away from writing or editing any code. Thanks in advance
I did a search for Gouping and sums, but nothing really helped me.
What I am trying to figure out: I have a table where I am trying to use 4 fields in a query.
Vendor, items, Cost, Date1
- I am trying to Group by Vendor, Sub-Group by Items (Easy, I did this) - Now I am trying to Filter this by date1 (Easy, I did this)
But I would like to sum the Cost for the Groups, but not list every repeated item. For Example:
Vendor A Brush 100 1/1/2005 Vendor C Book 50 1/2/2005 Vendor A Brush 100 1/3/2005 Vendor B Pencil 10 1/4/2005 Vendor A Hat 50 1/5/2005 Vendor B Pencil 10 1/6/2005 Vendor B Pen 10 1/7/2005 Vendor B Eraser 10 1/8/2005 Vendor A Shoe 40 1/9/2005 Vendor D House 1000 1/10/2005
Doing a search from 1/1/2005 - 1/9/2005 we get:
FORM OUTPUT:
Vendor A Brush 200 2 Hat 50 1 Shoe 40 1 Vendor B Pencil 20 2 Pen 10 1 Eraser 10 1 Vendor C Book 50 1
I have a query calculating the sum of checked boxes of field 'A' ('letters sent') and the sum of checked boxes of field 'B' (letters received). My sum field is counting the boxes that are checked but showing the values with a negative sign in front?? How do I correct this? Also - could I create an additional field in this query to calculate the percentage% (success rate) #B's checked/#A's checked?
well i created a relationship between 2 tables where:tableA = invoice datatableB = company IDsi have 2 questions that i have been unable to resolve on my own. 1. i want to create a query where i am able to find all of the invoices from a single company using it's ID and add up all of the account payable/recieveables i learned how to add all of the values on a field but i don't know how to add the values on a specific row. so while i can add all of the ap/ar on the table with all of the information, i can't add the ap/ar of a single company despite the fact that my table sorts them apart (because i don't know how ><)would a code like iff([company id]= "#",add) be close to what i'm looking for? because that's all i'm been fiddling with to try to get my query to do what i want. so far i'm stuck with: AR: IIf([company ID]=1,[AR$$],0) whereas 1 is the ID of the specific company i'm trying to pull records for and AR$$ is the field that i'm trying to pull the information from.2. find a way so that i can get my table to print all of the records for a specific company based on the ID. so for example, if i wanted all of the invoices from company #9 then i would pull them out and print them. i tried expanding the + sign on the company ID table and printing it, but it obviously failed -.-"i would really appreciate any help that can be provided to me! i just started access so anyone that can link me to an article related to this would be greatly appreciated because i don't know the terminology/keywords that i should be searching for in the article page that i'm using for this case.
I am trying to figure out how to create a cumulative sum field for a test report I am working on. On a very simple level, I can run queries to get my data into the following format:
Test # | Article # | Test Time | Test Parameters 1 | 1 | 8 | A,B,C 2 | 2 | 5 | A,B,C 3 | 2 | 7 | A,B,C 4 | 2 | 9 | A,B,C
For each test number, I want a separate report page showing the article number, test time, and test parameters. Also on each page, I want to display the cumulative test time for all test numbers up to the displayed test number, but only for the article used in that test.
For this example, page three would have test number 3, article number 2, test time 7, test parameters A,B,C, and cumulative test time on article 2 of 12 (5+7), while page four would have a cumulative test time on article 2 of 21 (5+7+9).
How can I convince Access to do this for me? So far I've only been able to have a cumulative test time for all test articles, not just the test article related to the test number. To make this more complicated, Access needs to be able to have another article added at any time without having to rework the code to create the test reports.
I appreciate any help you can give me that will get me on my way.
The Database is coming along nicely now, but in need of guidice with this one:
On the main table of the contacts I have what you could call a date of 01/01/06 and a combo box next or below that that I select what risk levels to asign the user.
Each user would have a no of year review based on the risk level: High = 1 Year Med = 2 Years Low = 3 Years.
There is another date box that is blank at the moment, what I am looking for query wise or another way is on change/update the risk level is to add the start start in this case 01/01/06 (if user is Med Risk - then add 2 years) and adding risk level giving me a new date of 01/01/08 in the blank date box (this option will make the main table update.
Can anyone point me in the right direction for this one?
I have a query that displays the difference between the number of scanned and processed invoices for each date (invoices scanned minus processed). Now I would like to make a query that for each date based on this shows the total backlog accumulated.
Example table:
The first column contains the date, and the second number of invoices scanned minus invoices processed.
2014-01-02 53 2014-01-03 -15 2014-01-04 24
Would it be possible somehow to make a query that for each lines shows the complete sum of all previous lines?
Based on the above data the result should be:
2014-01-02 53 2014-01-03 38 2014-01-04 62
Or would I have to write some VBA code to create some kind of temp table?
well i tried asking for help on this earlier, but i wasn't able to find any help so here is my problem.http://img330.imageshack.us/img330/2850/pictureaccessro7.jpg (http://imageshack.us)the sales - net = profit is fine but what i want to do is have a totals at the very bottem. everytime i try to do this, i can only get the totals to show and each individual record dissapears, or nothing shows at all. i've tried using a report but i can't seem to pull it off either. if anyone knows what i'm talking about and knows what i have to do then please help me :)
I'm creating a query for a someone who wants quantities summed by a weekly range and correspond to a week number. This person wants Access to do something that is a simple function on Excel.
I have no clue how to do this or if Access is even made to do something like this.
I have a query that requires a Start-Date and an End-Date to be input by user for the Where clause. It is asking for both over and over. I've had it ask from 1 up to 4 times! :eek: Shouldn't it store the input and only ask for it once? I'm thinking that the way my query is arranged may be causing it to have to loop through that section more than once to find the data, but that's just my theory. Any help would be great!
Here is my code (abbreviated slightly):
SELECT DISTINCTROW C1.*, C2.* FROM Pen AS C1 INNER JOIN Jobs AS C2 ON C1.subno=C2.[Jobs Acct] WHERE ((C1.typ="SS" Or C1.typ="CC" Or C1.typ="PP" Or C1.typ="TT") And C1.stdate>=[Enter Start Date] And C1.stdate<=[Enter End Date] And C2.[Type]<>"EE" And C2.[Type]<>"QQ" And C1.entdate<=C2.[ChangeDate]+60);
I'm selecting rows from "Pen" and "Jobs" that have the same subno/Jobs Acct numbers (text), then there are criteria for "Pen" types, user inputs criteria for date range (Start Date and End Date) and there are criteria for "Jobs" types. Finally, there's a cross-table criteria based on a date field ("Pen" entdate should not be more than 60 days past the "Jobs" ChangeDate). Tables are in quotes in my explanation here.
So running the above, it asks for user input "Enter Start Date", then again for "Enter End Date"...but then it asks for each again...and again...and sometimes again!
Help! :confused:
P.S..I didn't notice this repeating until I made it user input (parameter query) because it was using whatever dates I hard-coded in there before.
How can I check the Parameter query interval is out of range? Details:
I have a table with Date column. With parameter query(by Date field) I extract records between two dates. How could I make a check when taping in Inputboxes, am I or Not Between the Last and the First Date?
I'm writing a query which when run will prompt the user to enter criteria. In this case it is a workers id (like 000UA or 000UB)
My problem is I would like to have the ability to enter more than one criteria. What I want to do is to have the ability to enter several worker id's in the criteria field, like 000UA, 000UB, 000UC, 000UD, 000VA, 000VB, 000VE, 000VF, 000WA, 00WB
Is this possible and can someone explain to me how to do it?
I am trying to run a parameter query that will prompt the user for "facility", but instead of displaying the results in table format, I want a form to display the results. I have already developed the form. I would also rather for the users to search by a list box instead of typing the facility. I cannot seem to figure this out. Please help.
I am having problems finding a solution to this problem. I am working on 3 databases at the moment..and am at the final hurdle to complete it. I have built an Access database. I would like to generate a number of reports which have to be exported directly into excel individually.
I have a parameter query with 14 columns. One of the columns is called GROUPS. I have a form with a button. When you click on the button the parameter query asks for the parameter value.
The parameter value must be a GROUP. i.e BURR, WIEN.
After you enter the group the parameter query generates results for that particular group.
I have a number of groups which i would like to generate results for at the click of the button on the form.
There are around 30 groups. but i only need to generate reports for about 15 groups. The groups are stored in one of the tables.
So for instance after i click a button on the form the results for each requested "GROUP" are exported individually into excel.
Each group result must be in one excel file.
How can i achieved this. Do i need to use VBA? Please can you help. I need a solution then i can apply this to a number of databases
I want my msgbox to tell my user that this projectId does not exist in our db if the sql search returns an empty value. I get errors that i can't run this action query. please see red text. any ideas?
Private Sub Project_Quick_Find_Click() On Error GoTo Err_Project_Quick_Find_Click
Dim stDocName As String Dim stLinkCriteria As String Dim Ssql As String
stDocName = "Project Status - Full Details" stLinkCriteria = "[projectId]=" & Me![ProjId]
If Ssql = "" Then MsgBox "A Project with this number does not exist in our database", vbExclamation, "Cannot find project" Else DoCmd.OpenForm stDocName, , , stLinkCriteria End If
Exit_Project_Quick_Find_Click: Exit Sub
Err_Project_Quick_Find_Click: If Err.Number = 3075 Then MsgBox " Please enter a Project ID to find! ", vbExclamation, "Empty Field" Else MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error" Resume Exit_Project_Quick_Find_Click End If End Sub
