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?
View RepliesI'll see if I can make this clear and concise.
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
Is there a way to show negative numbers in brackets without selecting the Currency format?
I want to show (75,000.00) and not -75,000.00
I have a query which shows employee Totals it has the following fields:
Emp: [EmployeeLastName] & ", " & [EmployeeFirstName]
LogHours
Total: Sum
TotalWorkedPgs
Total: Sum
WorkDays: Sum([LogHours])/8
AvgPgs: (Sum([TotalWorkedPgs]))/(Sum([LogHours])/8)
JobType
JobEffort
-----
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.
Any help will be appreciated.
Thanks
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
Thankyou
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.
Thanks so much...its driving me crazy!
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
Can anyone help me?
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
Hi,
I'm having a lot of trouble creating a parameter query that will work when i sum a column.
I can get it to work without a seum (code below)
SELECT "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP, "Daily VDNs"."Inbound Calls"
FROM "RSS Data".RssUserRW."Daily VDNs" "Daily VDNs", "RSS Data".RssUserRW."IVR VDN Filter" "IVR VDN Filter"
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'
FROM "RSS Data".RssUserRW."Daily VDNs" "Daily VDNs", "RSS Data".RssUserRW."IVR VDN Filter" "IVR VDN Filter"
WHERE "Daily VDNs".ACD = "IVR VDN Filter".ACD AND "Daily VDNs"."VDN Number" = "IVR VDN Filter".VDN
GROUP BY "Daily VDNs".Date, "Daily VDNs".ACD, "IVR VDN Filter".NSWCTP
HAVING ("Daily VDNs".Date={?}) AND ("IVR VDN Filter".NSWCTP Is Not Null)
However, I can use the above code without a parameter query and define a value. But life would be so much easier with a parameter query.
Any assistance is greatly, greatly appreciated!!
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.
View 7 Replies View RelatedI'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. . . . ._____
Item Table
Item Category Sales
1. . . . . .A . . . . $10
2. . . . . .B . . . . $15
3. . . . . .B . . . . $10
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
Please help this is very frustrating
Thanks
Good afternoon,
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.
Thanks!
Erik
I am trying to add the two highlighted Sums together to show in the box in the footer of the Report.
View 14 Replies View RelatedThe 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?
Thanks
Mike
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?
I have 2 tables and one query as below
1- Order Table
OrderID | CustomerID | CusromerName | OrdDate
2- Payment Table
OrderID | Amount | PYMDate
3- Order Deatails Query
OrderID | ProductName | Quantity | Price | Total ([Quantity]*[Price])
I want to group the orders based on the sum of total and sum of payments. I made another query as following :
OrderID (From order table) | Payment (from payments table) | Total (From Order Details Query )
At the Total field I set the above fields to
Group By, Sum
and Sum respectively
I got true payments sum, however, the sum total are always duplicated.
Note that the three objects (two tables and query ) have relation (type : one to many) by the order ID
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 :)
View 4 Replies View RelatedI'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.
This is an example of what I have so far
Quote:
P/N QTYWork DateWW (Work Week)
25COMP16/11/201428
25COMP26/20/201425
25COMP27/9/201428
25COMP36/20/201425
....And so on
My code is
Code:
SELECT dbo_JBKLG.JKPRT AS P/N, dbo_JOB.JBQOR AS QTY, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4)) AS [Work Date], DatePart("ww",[Work Date]) AS [WW]
FROM dbo_JBKLG LEFT JOIN dbo_JOB ON dbo_JBKLG.JKJOB = dbo_JOB.JBJNO
GROUP BY dbo_JBKLG.JKPRT, dbo_JOB.JBQOR, CDate(Mid([JKDDT],5,2) & "/" & Right([JKDDT],2) & "/" & Left([JKDDT],4))
There are more part numbers and the data from the other parts must be in the same format in the same query.
Quote:
P/N QTY WK WW
25COMP 15506/09-06/15WW24
25COMP 22706/16-06/22WW25
25COMP 16006/23-06/29WW26
25COMP 20006/30-07/06WW27
.....And so on
When i sort values, for some reason the negatives are at the top, followed by the highest positive values. Anyway to turn this off?
Tried google and searching these forums.
I trying to create a table where the primary key is a customerID, using the autonumber function. I would like to leave the new value setting on Random, since two people may be using database. However, I'm getting a lot of negative numbers as CustomerID's. If there a way I can limit the pool of random numbers to positive only.
View 4 Replies View RelatedI have become the default Access expert in my office... which means I know next to nothing, but more than any one else here. We have just converted an Access 2.0 (yes, really) db to Access 2000 db. It seems to function fine in most regards (all that we've tested so far) except that one autonumber in a table now gives negative numbers. It started at the same number as previously (let's say 433199), but has added a - and started counting back towards zero (-433199, -433198, etc.).
The chances of these unique numbers overlapping is slim, but I am concerned about how the negative numbers will effect other aspects of the db or our accounting system which use those distinct numbers as invoice numbers.
I would like to start at a new, positive number, but just don't know how!
Hello all, i have a calculation in a report that sometimes results in a negative number. is there anyway to make that negative number = 0 or equal to a specific text?
View 3 Replies View RelatedI need to make a coupel of statements that display the total (sum) of negative amounts and positive amounts from a single list of results which comtains both negative and positive values. The two answers need to be displayed in the footer of a report.
View 12 Replies View RelatedSounds weird huh?
I have a salesman that has a negative "Net Flow" goal (sales goal). Others have positive goals.
His YTD Net Flows (Sales) is say...$1062 (million) and his goal is "-266 million"
Of course he is doing quite well, when I use the following code to get a % to goal I get "0" whenever there is a negative goal involved.
% to Goal: IIf([NetFlowsGoal]>0,([Total Net Flows]/[NetFlowsGoal]),0)
But it works fine if all numbers are positive as you might guess.
Any pointers would be great...Thank you
Please help.
How do i convert a negative value to zero in a Form?
:confused: