Access :: Small Problem With Sums

Sums In A Query

Oct 13, 2005

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

View 5 Replies View Related

Counts And Sums

May 6, 2006

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

View 4 Replies View Related

Sums And Null Value Help Please

Jul 13, 2007

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!

View 2 Replies View Related

Invoicing Sums

May 26, 2005

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?

View 8 Replies View Related

Negative Sums

Jul 17, 2007

I'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

View 3 Replies View Related

Nested Grouping And Sums

Jun 10, 2005

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

View 3 Replies View Related

Sums Showing A Negative Value

Jul 27, 2005

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 4 Replies View Related

Parameter Query With Sums

May 11, 2006

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!!

View 5 Replies View Related

Non-Updatable Query Due To Sums

Aug 14, 2007

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. . . . ._____

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. ____

View 4 Replies View Related

Problem With Sums In Query

Oct 29, 2007

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

View 2 Replies View Related

Selective Cumulative Sums

Sep 24, 2007

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

View 4 Replies View Related

Reports :: Add Sums Together In Report

Apr 19, 2015

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 Related

Help On Sums - Dates This Time + Rewriting Of Data

May 30, 2006

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?

Thanks

Mike

View 1 Replies View Related

Queries :: Sums Up All Previous Lines In A Field

Feb 9, 2014

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?

View 2 Replies View Related

Group By Data From Different Tables Based On Two Sums

May 23, 2014

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

View 10 Replies View Related

Trying To Have Sums And Individual Records On The Same Page (picture Included)

Sep 8, 2006

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 Related

Queries :: Create Sums Based On Weekly Range?

Jun 26, 2014

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.

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

View 14 Replies View Related

I Know Its Something Small

Dec 30, 2004

I have tried the count(field name) and the sum(field name) but neither of them are giving me the grand total only the count/sum of that row. Here is my code, please look - I'm sure its something small and stupid

SELECT [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield], IIF([Cycle Time]<=30,1) AS Yield
FROM qry_Refi_Yield
GROUP BY [qry_Refi_Yield].[LOAN NBR], [qry_Refi_Yield].[BORROWER], [qry_Refi_Yield].[PURPOSE], [qry_Refi_Yield].[FUNDED STAMP], [qry_Refi_Yield].[HSS], [qry_Refi_Yield].[UNIT TEAM], [qry_Refi_Yield].[Cycle Time], [qry_Refi_Yield].[Total Yield]
ORDER BY [unit team], [hss], [funded stamp];

past tries: count([qry_Refi_Yield].[Total Yield])
count(borrower)

View 7 Replies View Related

Options To Use For A Small App

May 10, 2005

Hi,

I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.

Thanks,

scratch

View 2 Replies View Related

Small Rant :p

Aug 29, 2007

There are times when it is just necessary to have a little rant, a canniption if you will.

For the last few weeks I have been working off-and-on with a new database for a salmon hatchery. One of the forms I designed contained a subform in continuous form view that flickered inordinately when first loaded. Now, there's a lot of code going on behind controls etc to run sql updates or deletes or appends depending on what the user clicked. There was also some code to alternate the row color of the continuous form. I spent literally days trying to find what part of my code was causing the problem.

I swore a lot.

I searched.

I eventually gave up as other priorities began to arise at work. I resigned myself to my fate.

Then I discovered, quite by accident, that the problem arose by the use of some unassociated labels being present on my form. I removed them, replaced them with labels associated with a control, and presto: the form flickering/reloading magically vanishes.

Great.

But what a colossal PITA for something so bloody stupid. There's absolutely no reason why this should occur IMHO. Honestly: it's hard enough to learn VBA, SQL, and relational db design without having to deal with poorly documented glitches like this. There are days when I think I'm finally starting to get the hang of Access, then something like this comes along and I want to send a lynch mob to redmond! :mad:

Ok, end of rant :) Hope the solution to my problem comes in handy for someone else someday.

View 3 Replies View Related

Small Problem I Think,

Mar 22, 2006

Hi,

What i want to do is have a field called "monthlyhourcount" in my query that totals all the hours in a field i have in a table, the field in the table is called "hours worked" were a figure is entered, i need my query to give a total of all the figures entered. so far in the expression builder i have :

monthlyhourcount: [JobListMonthly]![Hours Worked]

when i run the query with this it gives me the seperate values, i need one total value.

any ideas guys?

Thanks

Conor

View 1 Replies View Related

Need Help On A Few Small Things

Nov 21, 2006

I'm fairly new to Access (2000 is the version im using), and I'm not sure how to do several things within a form:

1) make cursor in a text box jump to the front (left) when clicked on
2) Lock (and grey out) a text box once data has been entered into another
3) assign a field to correspond with another, and to have this shown on the form (ie: having a list of names and their corresponding phone numbers assigned to each; then on the form, having a combo or list box with the names that a user can select- once a name is selected, their phone number appears automatically in another text box)
4)Locking screens or subforms when moving on to another (either by pressing a button to open another form, or by selecting a field in another subform)

thank you in advance for your help and advice

View 1 Replies View Related

Help For A Small 1 Table DB Please.

Sep 15, 2005

Hello,

This will probably generate a few good laughs but here goes!

I have been given an Excel file with 6 fields about 20 chars in each field.

This file is a whopping 250 records. I would like to be able to add, change, delete and search records.

The DB would need to be shared by 2 users on a home network. Both are using Windows XP.

What would be(heres your chance!!!) the easiest solution for this?

This is for someone who wants to keep it simple. If it was simple though, I guess I would not be posting this!!! Haha.

They best part is they have MS-Works on their machines.

Any help or better yet a completed solution would be much appreciated.

Thank you
Take Care
Tom

View 1 Replies View Related

Small Code In Need Of Review

Jun 24, 2005

hi. I am trying to write a code to sum the total cost of all jobs that are selected as 'yes' in a combo box.

Once the user selects 'yes' in a combo box, they enter in a cost for that specific job. Each client may have 10s of jobs. I need to write a code that will sum up all the 'yes' selected jobs for a client. I know it should be an 'if' statement but I can't seem to get it to work. I need this value for a report. How would I do this and where would i put the code.

Help is appreciated.

Here is the code that I was thinking of, I don't think its right but it might be a start.

Dim Count As Variant
Dim CountImplementedTotal As Variant

CountCost = 0
CountImplementedTotal = 0

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordSet("ECM Details")
Do While Not rst.EOF
If rst![AUDIT ID] = Forms![audit info]![AUDIT ID] Then
If rst![Has Measure been Selected] = "YES" Then
Count = Count + rst![Total Measure Cost]
End If
End If
rst.MoveNext
Loop

Text47.Text = Count

View 3 Replies View Related

Small Module Help (simple I Think)

Nov 25, 2005

hello all
i need a small thing to change in a module but am not too familiar with vb so i reached a dead end, here's the story:

i have a 'dog pedigree' database that i was working on, now there's a report where a dog's parents, their parents, and their parents need to be shown

that turned out to be out of my league so i paid a guy to do it for me, it worked except he didn't do exactly what's need and i can't get to him now :S

anyway, in the report page,
you can notice that the name of each parent is shown, but i want the registration number of the parents to show as well,
i tried modifying the module but it didn't work (am not a vb user), so can anyone help me add the reg number to the name of the parents?

also, in the report, the dog's color is appearing as a number instead of an actual color so if that can be fixed as well ...

to understand better:
record 1 in dogs, click the preview diploma button
you'll notice "tat" as dogname and "ry245" below that as registration number
now sire is "pat" and dam is "titu", i want their registration numbers to show as well (and same for the rest of the parents)

i uploaded the file here for a closer look:
http://www.designcrafts.org/dogs1.zip

thanks :)

View 14 Replies View Related