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 Replies


ADVERTISEMENT

Selective Database

Jul 6, 2005

I'm working on an inventory database for video tapes. Each tape has a Catagory, Sub-title, Sub-Sub-Title, Professor, Location, and Format. What I'd like is to be able to do is select a catagory from a lookup, and only the sub-titles related to that catagory appear in the sub-title lookup, not all sub-titles in the database. Following suit, I'd like the sub-title I select to bring up a limited list of sub-sub titles. I'd like a lot of things with this database. Unfortunately I have no clue how to go about doing this, which doesn't help me much. I've tried six different ways so far of attacking this, and nothing works (I've been at it for about a week). Does anyone have any suggestions, solutions, reccomendations, whatever? Any help would be appreciated. Thank you in advance.

View 1 Replies View Related

Selective Count

Dec 1, 2005

Hi All,

I've created a table that take the results of a questionaire, with

0 = No
1 = Yes
2 = N/A

For each question I want to count the number of Yes, No and N/A in one query. I can get the result for one question by using Count with the critera set to = 0 or =1 or =2, but can't do it for multiple questions in one query and I don't really want to create 33 query for each answer and then join them.

I have attached the table that I'm using cos I'm not usually very good at explaining things.

Thank You

View 8 Replies View Related

Selective/if Fields

Apr 7, 2007

I have a query that has the follwing fields:

Gross Payment :Currancy
Tax Discount: Boolean (Yes/No)

I want to create a new field called Tax Deducted that if the tax discount is no, the Tax deducted = Gross Payment * (22/100) but if tax discount is yes, then Tax Deducted = 0.

I'm not sure if i'm supposed to do this in a query or in a report but that's what i need to do. I ask if anyone can tell me what i need to do so that i end up with something like this:

Person A £29000 Yes 0
Person B £10000 No 2200

View 2 Replies View Related

Selective Update Queries

Jan 12, 2006

Happy New Year Everybody

Can Anyone help me with this please

I have a Employee Table with the following columns
EmpID
Name
Address
Telephone
PostCode
Spending

What I want to do is to update the spending column, for eg. I have the following data in spending
1233
1555
1665

I want to set the last 2 digits of the figure to '00'?
Can anybody tell me how to do this please!! I am desparate for it!

THanks
Si

View 4 Replies View Related

Selective Update Queries

Jan 12, 2006

Happy New Year Everybody

Can Anyone help me with this please

I have a Employee Table with the following columns
EmpID
Name
Address
Telephone
PostCode
Spending

What I want to do is to update the spending column, for eg. I have the following data in spending
1233
1555
1665

I want to set the last 2 digits of the figure to '00'?
Can anybody tell me how to do this please!! I am desparate for it!

THanks
Si

View 3 Replies View Related

Selective Data Acces Depending On Login

Jan 11, 2006

I have a two level security system to protect my date. The first uses the MS Access security, the second is a simple login system wherby a user enters their name and personal password to gain a customised view of the data eg if the user is me, I can see all tool bars, other users cannot. What I would like to be able to do is write a query wherby I can use the users login details to diplay only their data. An example of the script I built is as follows

'IF USERNAME AND PASSWORDS CORRECT THEN LOGIN TO THE MAIN SWITCHBOARD

If Me.qpwd.Value = DLookup("PWD", "AUTHORISED", "USERID = '" & Me.quserid.Value & " '") Or UCase$(Me.qpwd.Value) = "abcd" Then
DoCmd.Close
DoCmd.OpenForm "Course Booking System"
Else
MsgBox "Invalid Password"
End If

Is there a way of including a select criteria like the following

SELECT USERID, DETAIL
FROM COURSE
WHERE USERID="Me.qpwd.Value"

I have tried several differnet versions for this syntax without success!

Any help would be greatly appreciated.

Regards

Peter

View 3 Replies View Related

Reports :: Selective Subform By Record Number?

Jul 2, 2013

I have a DB with 1000+Records. Each record has a "History" field showing the date I last visited (about 20+Visits each). When I re-visit I print Report (A4 size) which gives me the address and previous visits, however I only want my report to show the last 6 visits.

I have tried both a Query & Subform and changed the "Top Values" from ALL to 6, but this only gives the last 6 visits from all records. I need to see the last 6 visits for each record.

View 1 Replies View Related

Tables :: Selective Operation Of DataType Mismatch Error

Aug 22, 2014

I have a table 'Project Details' to which I have recently added a new field 'Last Invoice issued'. This field is a text field with a lookup and will default to 'No' for new records being added. When the related record is updated in my 'Invoices' table, I have set up a query to change 'Last Invoice Issued' to yes. This is all working fine, but it requires all the records in 'Project Details' to contain 'No' already in order for it to work. As there are over 1000 records in 'Project Details' I made a list in Excel of the right number of no's and copied it in. Most records were fine with the exception of 127. I can edit any of the other fields from the 'Project Details' datasheet or my data entry form, but when I try to edit one of the 127, I get a DataType mismatch error. I can't understand since the records are all in one table and have exactly the same DataTypes set up for each field, why I can edit some and not others? Is there some other place where DataType can be set up that may affect some records and not others?

I have tried to copy my table to post here but when I do that, I get a 'Index or Primary Key cannot contain a Null value' error due to the (New) record having the default values already in but the primary key not being filled until I save,.

View 2 Replies View Related

Forms :: Creating A Form Which Copy Selective Entries From Previous Record

Dec 10, 2013

I am tasked with creating a scientific database of fish catches at various different sampling stations. I envisage the entry form having hierarchy levels, ie year, season, date, sample station, species caught, and then the specific associated data with each species (weight, abundance, length etc) will form each distinct record.

In each year there are 2 sampling seasons. In each sampling season there are up to 20 dates on which samples are taken. On each date there are up to 50 sample stations recorded. And at each station up to 20 species can be recorded.

In order to enter each species caught in a survey, the end user will potentially have to fill the season box with an entry 40,000 times. For each season there will be 20,000 date entries to be made. For each date, 1,000 station code entries, and 20 species entries for each station. What I would like to happen is this.

When the end user is entering the data, he or she will be working through the hierarchy from the ground up. Ie., they will enter a particular species' data (abundance, weight, length etc) at a particular station on a particular date during a particular sampling season. The next entry will be a different species at the same station on the same date during the same season. I would like the duplicated information from the previous record to be copied across onto the new one so that all the end user has to do is enter the species name, abundance weight and length data.

Once they have finished entering all the species data for that particular site, they can move onto the next site which they would fill in the data themselves for the station code. So, in the hierarchy, the first button would copy all the previous data except the species name and associated weight length abundance data. The next button would do the same, except leave the station code blank. The next button would leave the species name, station code and date blank. And so on up the hierarchy. The end user would be able to selectively choose which data is copied over by using these buttons, saving lots of potential extra work doing repetitive entries.

View 1 Replies View Related

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

Small Problem With Sums

Aug 9, 2006

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

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

Cumulative Row Function?

May 23, 2005

How to make calculated field that represents cumulative row?
For example – table has fields "transaction_number", "trans_date" and "trans_amount".
In this table I register money transactions on my bank-account. I need calculated field that is going to show how much money is on my account after every transaction.

Thanks

View 8 Replies View Related

Cumulative Totals

Nov 9, 2005

I have two tables, one for budget and the other for actual expenditure. I need a query or queries that will give me a cumulative figure on 3 groups based on the expenditure table. In my expenditure table I have the following fields.

ExpenditureID Autonumber
Period Text
CostCentre Text
CostCode Text
Value Currency
fldDate Short Date

I have managed to fnd a function that will give me a running sum for each individual group, but cannot seem to get it to work for more than 1 group. The function is this.
Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function

Is there any way I can see the results in one query or table? Any help appreciated.

View 6 Replies View Related

Cumulative Totals - Please Help

May 26, 2006

Hi,
I'm having trouble trying to create an expression for generating cumulative totals by Month.

I believe this is the function I need to use -

DSum (expression, domain, [criteria] )

I need the layout to be as follows -

Months - Rows
Cumulative Monthly Despatches - Column

I have a field list with
Months
Despatches
Models

All of these are from another Query and 'Despatches' is an expression generated in the other query

Can anyone please Help?!

View 1 Replies View Related

Queries :: Sum Cumulative By Id?

Aug 22, 2013

how can i do this in a query

Id---Q---Cu
1---10---10
1---10---20
1---10---30
2---10---10
2---10---20
2---10---30

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved