Subtracting Values From Two Consecutive Records

Jan 17, 2006

I am having a problem, probably due to my inexperience with Access. Here is the seniero:

-I have a form/table set up for operators to enter numbers on a daily basis.
-I am setting up queries/reports to display not only the entered data, but also calculated values from the entered values
--I have no idea on how to subtract a previous day's value from today's value and report that value.
- Since Access gives no "ownership" to any value I do not know how to reference yesterday's value in today's calculation.Any Ideas?

Record #1
Date - 1/17/06
Value - 15,232

Record #2
Date - 1/16/05
Value - 14,111

Desired output
Date 1/17/06
Value - 1121

View Replies


Queries :: Adding Consecutive Values To Get A Running Total

Oct 23, 2013

I have a quick (hopefully) query about adding up consecutive values to get a running total.

Currently I have the following

ID Value
1 0
2 5
3 2
4 0
5 1
6 30
7 2


I am looking for a way to get a running total up to the point of a 0 then restarting. e.g.

ID Value Running total
1 0 0
2 5 5
3 2 7
4 0 0
5 1 1
6 30 31
7 2 33

View 12 Replies View Related

Subtracting Query Values

Sep 7, 2007

How do i subtract one value, worked out using a query, from another value, worked out using a query, and display it in a text box?

View 7 Replies View Related

Difference In 2 Consecutive Records

Jun 18, 2007

Hi there

I am trying to get a very simple thing done in Access and I must have spent almost over 2 days of Internet time to do so. My problem is that I need to get the difference in 2 records to show in a query.

e.g. MyTable has 2 fields: 1. MyDate 2. MtrReading

Everyday I enter date in MyDate and a number in MtrReading. And I need to find out the difference between today's MtrReading and Yesterday's Mtr Reading.

Any help is greatly appreciated!


View 4 Replies View Related

Find Consecutive Number Of Records In DB!

Aug 14, 2006

I am trying to generate some query result on a form.
My DB consists of the following fields : tradeID and profit/loss.
How do i make a query to find the Maximum consecutive number of wins and losses in the database and display the results on a form?

Would really appreciate it if any of you have tried to sort and display results for finding consecutive number of events in any database!

Thanks a lot

View 2 Replies View Related

Count Consecutive Records That Match Criteria

Jan 25, 2006

I want to place a control in a report footer that will return the highest number of consecutive weeks that a profit was earned. The database has fields named WeekNo and Net. The Net field contains positive values (profit) and negative values (losses). The WeekNo field is an integer from 1 to 52 designating the week number.

For example if the figure in the Net field is a positive number I want the control to count the number of consecutive weeks that a profit was earned. I know how to get the value of total weeks that a profit was earned; this is not what I am looking for. The control must return the highest number of CONSECUTIVE weeks that a profit was earned over the year.

Any help would be appreciated.

View 1 Replies View Related

Modules & VBA :: Subtracting Value On Current Record From The Value Of Previous Records?

Jun 5, 2013

I want subtrack a value on the current record from the previous record and display the result in text box in subform datasheet. This is done during data entry. The calculation works most of the time and randomly it returns the wrong value. I have the code in an after update event after I enter a number. Here is what my code looks like:

sysHopSpacing = Abs([sysHop1] - DLookup("[sysHop1]", "tblSystemConfiguration", "[sysBaseNumber]=Forms![frmTempestCoordination]![frmSubSystem]![sysBaseNumber] - 1 "))

This is how I generate the sysBaseNumber

sysBaseNumber = Nz(DMax("sysBaseNumber", "tblSystemConfiguration", "sysAccountID=" & Forms!frmTempestCoordination!accAccountID), 0) + 1

I am using DMax so I can keep the subform records with the parent form record.

My results are inconsistant:

This is what I want the subform to do when subtracking from the previous record

syshop1 sysHopSpacing
20 15
30 10

Instead I get a result that does not make sense like this:

sysHop1 sysHopSpacing
20 15
30 4

I cant trace it down.

View 14 Replies View Related

Adding And Subtracting Short Time Values Together/changing Short Time Into Minutes

Dec 9, 2004

Hello again,

I think my subject heading explains it. How would you add or subtract two short time formatted values and produce a short time value? Also, how would you change a short time value into number minutes. (i.e. 5:30 into 330) Thanks!


View 1 Replies View Related

Queries :: Query Records With Both Duplicate Values And Different Values?

Jun 18, 2013

I'm trying to determine the SQL to return only those records in a table which have duplicate values in each of two fields, but different values in a third field. Here's an example:


AcctNum FoodType FoodClass
------- -------- ---------
A123 Apple Fruit
A123 Apple Fruit
A123 Grape Fruit
A456 Potato Vegetable
A456 Potato Perishable
A789 Carrot Vegetable
A001 Banana Fruit

For the above table, I'm trying to return records which have multiple entries for AcctNum + FoodType, but DIFFERENT values for FoodClass. So for the above table, the query would return:


AcctNum FoodType FoodClass
------- -------- ---------
A456 Potato Vegetable
A456 Potato Perishable

It returns these two records because there is more than one record with for the AcctNum + FoodType (i.e. 'A456' + 'Potato'), but DIFFERENT values for FoodClass (i.e. one record has 'Vegetable' while the other has 'Perishable').

View 5 Replies View Related

10 Consecutive Years

Dec 5, 2007

I have atable the is storing ID, attendance_year and meeting code. Iw ould like to query the table and identify the IDs that have 10 consecutive attendance years no matter when it may have occured. So IDs have more than 10 row of data but not necassrily 10 years in a row.

How can I do this?

table looks like


View 5 Replies View Related

Detecting Consecutive Dates...

Mar 21, 2006

Firstly I have placed this in the general section as I am not quite sure if any vba would need to be created for the following. I would also like to report on the information but the answer may not be done at report level.

Anyway, I have created a very basic database that holds staff attendance and their reason for being absent.


What i would like to do is group consecutive dates if they are all the same type and show them as DateFrom and DateTo

So for example, for someone who is on holiday between 01/Jan/06 and 06/Jan/06 I could show that as:
DateFrom 01/Jan/06
DateTo 06/Jan/06

I am not sure if that is explained very well but I will try to explain a bit more clearly if needed?

Thanks for any help.

View 1 Replies View Related

Counting Consecutive Days

May 19, 2005


Is it possible to count the number of consecutive days in a table?

I need a query to calculate for a particular customer the number of consecutive days thay have logged a record if its greater than 1

EG: Table layout:

CUST A RC1 05/05/05
CUST A RC2 06/05/05
CUST A RC3 07/05/05
CUST A RC4 08/05/05
CUST A RC5 15/05/05
CUST A RC6 18/05/05

Possible query results:

CUST A 4 Days First 05/05/05 Last 08/05/05

Is it possible to do something like this??

Thanks in Advance

View 1 Replies View Related

General :: Notify After Two Consecutive Absents?

Aug 17, 2014

I have a Student Attendance table and it would be great if I could get some way of automatically determining if the attendance status is Absent - Unexcused for two or more consecutive sessions in order for someone to get in contact with the student.

View 14 Replies View Related

Queries :: Consecutive Days Worked?

Sep 19, 2013

I have a table of employees, and dates they worked on. These are seasonal employees who want to get in as many days as possible before the season ends, but regulation states they must take a compulsory rest day after x days.So I need to create a query that can return the list of employees, with a count of consecutive workdays up until current date.

If today is 20/09/2013, and Johnny worked on, 19, 18, 17, 15, 14, 13, his count must be 3, because he was absent on 16. Therefore only from 17 through 19 is regarded as consecutive.

If Peter worked 19, 18, 17, 16, 15, 14, 13, his count would be 7, because unlike Johnny, Peter still worked on 16.

View 12 Replies View Related

Identify Changes In Data Between Consecutive Rows?

Jul 19, 2015

I have data for multiple account numbers (for work) and dates, and I need to identify when there is a change in account number in order to add a new field with a count - which counts sequentially starting with 1 and then starts over at 1 when the account number changes.

Ex. The red column is what I am trying to create

Account Date_To Counter
12345 06/30/2015 1
12345 05/31/2015 2
12345 04/30/2015 3
19999 06/30/2015 1

View 2 Replies View Related

Consecutive Invoice Numbers, Restarting Each Month

Oct 14, 2005

Hi there,
Some scene setting to my problem. I have a corporate system numbering invoices consecutively globally. For a subsidiary in Poland this presents a problem - all invoices for the subsidiary must be consecutive. ie. where the globally issued invoices for Poland might be 1,4,5,7 these need to be renumbered before issue in Poland.

It gets more complicated as the Polish Office standard is to invoice in the format "PL/Year/Month/Number" with the Number restarting from 1 for each month.

My database is very simple.
Table 1 = Link table to Oracle corporate data
Table 2 = Local table of invoices already produced (Global invoice number and date recorded)

I have a filter to find all new invoices (unmatched items), a report to print them and an Append query to populate the new items to the Local table.

My challenge: (1) to recreate the Polish numbering scheme and (2) have it consistently when existing data goes up to mid month and a new append operation has to follow the invoice number sequence.

Any ideas? I guess Autonumber is no good and I have little array experience.


View 5 Replies View Related

Queries :: Extract Data Between Two Consecutive Comma Set

Jun 16, 2013

102,34112,021502503130010010000,QAR,1,24,1,{NULL:0 },{5:Y},6,14.5,31-MAR-2011

Above data is in one field , i want and expression to extract the data between two consecutive comma set by specifying the comma range.

For example if i specify range 2 to 3 then formula should return "021502503130010010000"

View 14 Replies View Related

Reports :: Listing Consecutive Dates In Report

Dec 1, 2013

Is it possible to list out each consecutive date in a report when given a range of dates without having to create a table of dates?

For example, given the date range 12/01/2013 to 12/05/2013:

In report:

Is it possible to do this dynamically in vba?

I want to ultimately join this to a recordset for the report.

View 1 Replies View Related

Run Multiple Append Queries In Consecutive Order?

Jul 16, 2013

I am setting up VBA to run multiple append queries in consecutive order. The append queries are supposed to pull a four digit number off of a form that will allow them to know which data to append. I want the number to be stored as text to correspond with the tables I am working with, but I am having trouble getting the form to work.

Here is how I would like it to work:

I enter my 4 digits in the text box on the form. Say "1305" for May 2013. I want to then run my vba (my queries update using the forms!txtupdatequery!textbox I have put in the criteria of the queries) and all of them run.

I enter my date (as a number but I want it to be text) and the form gives me a #name? error.

View 3 Replies View Related

Creating Consecutive Identifiers - Automatic Numbering

Sep 17, 2014

I am trying to create a form that creates an automatic identifier with a value one greater than the previous identifier. I was able to create something that updates in batches of two: That is, I end up with identifiers that go 1, 2, 2, 3, 3, 4, 4, etc. How do I get it to create consecutive identifiers (1, 2, 3, 4, etc.)?

View 2 Replies View Related

Reports :: Create Consecutive Numbers In A Row Based On User Input?

Jul 31, 2013

I have list of candidates shown in report. I need each candidate to have a unique number based on user input. And this number increases by 1 for the rest of the candidates in the report.

Example is below:

If user enters 1132 in the user input box then the numbers shown in report should be as below:

Name Assignment Number

Felicity Thomas 1132
Andrew Sen 1133
Andy Luker 1134

Similarly if number entered is 2345 then the numbers shown in the report will be:

Name Assignment Number
Felicity Thomas 2345
Andrew Sen 2346
Andy Luker 2347

View 1 Replies View Related

Subtracting In Access

Oct 10, 2005

I'm trying to do a calculation in access that will allow a user to receive a discount in one field and show the results in another. The form looks like this. Having a hard time figuring out the line total. It's giving me a the wrong amount. The employee discount is $.20 and the price is $2.27 for example.. Thanks

Bound text box [UnitPrice] - Bound text box [EmployeeDiscount] x Bound text box [Quantity] =
Bound text box [LineTotal]

View 1 Replies View Related

Subtracting Time

Jun 20, 2006

I have two times I need to subtract in a query. When I subtract these in the query I get a decimal point. How can I convert this data. Thanks

View 1 Replies View Related

Subtracting One Query From Another?

Feb 23, 2008

Hi everyone,

I am familiar with databases and basic SQL, but never used Access, so your help is greatly appreciated!

What I *think* I need to do here is take the results from one query, subtract another query from that, then create a table of the results, so that I can make a chart in a report. I say that I *think* I need do that because Iím not confident that this approach is the best but thatís a whole other story...

Please look at the following tables below for an example. The Item table has ItemCode as a primary key. The Bids Table has multiple rows for each ItemCode showing bids for the item:

Item Table
ItemCode Date Address
JH231 2008-Feb-20 123 Test St.
KH831 2008-Feb-21 512 Test St.

Bids Table
ItemCode Amount
JH231 100.00
JH231 60.00
JH421 310.00
KH831 20.00
KH831 40.00
KH831 10.00
KH831 90.00

To make a pretty graph, I would like a table (so a query, or macro, or VBA or something?) that returns the minimum Amount for an ItemCode subtracted from the maximum Amount for an ItemCode based on the example above. What I would end up is something like this:

ItemCode Difference Between Maximum and Minimum
JH321 250
KH831 80.00

I hope this is clear. Any help on how to do this from you Access pros would be great, OR if Iím taking the wrong approach, suggestions on what I should try next. Thanks!!!


View 6 Replies View Related

Subtracting Stock Levels

Nov 9, 2005

Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.

View 2 Replies View Related

Subtracting Results From Two Different Queries.

Jun 16, 2005

Hi, here's the situation:

I have a query A, that gives me the a set of numbers.
I have a query B, that gives me another set of numbers.

Numbers in B are always equal or larger than those in A. (B >= A)

I want to create a new query C, that does the subtraction. (C = B-A)

Here's an example to explain what I want.
Let's say I have A=4,7 and B=6,9

Now, when I create the query, I get the following result:
A | B | C=B-A
4 | 6 | 2
4 | 9 | 5
7 | 6 | -1
7 | 9 | 2

What I want is this:
A | B | C=B-A
4 | 6 | 2
7 | 9 | 2

How do I do that?


View 2 Replies View Related

Copyrights 2005-15, All rights reserved