Adding/Subtracting Across Adjacent Table Rows

Oct 30, 2006

Is it possible to perform calculations across 2 adjacent rows in SQL?

At the moment, I have a running count that tracks the number of updates that go into our database against a specific JobID.

Is it possible to subtract the running count for a previous update from the current update.

For example, I would like my resulting table to end up looking like this

JobID Time Updates Since Last Update
1 10:10:00 1 1
1 10:30:45 3 2
1 11:15:10 9 6

In this example, the Since Last Updates should be found by
subtracting the Updates at 10:10:00 from the Updates at 10:30:45 giving 2 and the Updates at 10:30:45 from 11:15:10 giving 6. It would continue like this for as many rows of data have the same JobID (which could be several hundred!)

What I would then like to do is use the 'Since Last Update' number to display only those updates that are applicable to a certain Time, perhaps using Select Top in SQL.

Am I approaching this from the right direction, or would it perhaps be better to try to do this using Time, as this will always be unique?

Thanks for looking


View Replies


Subtracting Dates From Adjacent Dates In Same Column

Sep 7, 2006


I have a database with 5000 entries, corresponding to about 10 entries for about 500 people. Each of the entries is dated, and I need to calculate the time intervals between each person's sequential entries in the table.

One way of doing this is to create another column that contains the date of the previous entry. I can then use DateDiff to subtract one date from the other and give me the difference in days.

This approach falls down if I then work with only a subset of the entries - I would have to re-enter the previous entry dates as the time intervals would have changed.

What I really need is a way of subtracting the date from the date in the cell directly above it. Will Access let me do this, or is there a better way?

Many thanks, Jules.

View 3 Replies View Related

Queries :: Subtracting Rows In Access

Dec 8, 2013

I have a query with years and sales column. I need to make a column that displays the difference in sales between years. My query is named "Qry" if that is needed. "Year" and "Sales" are the column titles if you need that for the SQL.

View 14 Replies View Related

Adding Rows From A Similar Table To Preexisting Table

Sep 19, 2013

I'd like to learn how to do them quickly and effectively in Access. One of those things seems like it would be an incredibly simple, intuitive operation, but it's not, at least not to me.Say I have some data that really should only be on one table, but it's currently on two tables in Access: "List Main" and "List September Adds." They both have the following fields: "Full Name" "Email" "Address" "City" "State" "ZIP" "Phone."

The first table is my main list of contacts. The second contains new info, consisting of 1. a few new contacts, and 2. updated info for a few of the contacts already in table "List Main."

I would simply like to put all the new contacts from "List September Adds" into my "List Main" table, and I'd also like to fill in a few missing e-mails in "List Main" with newly gathered e-mails for those contacts, info that is in my table "List September Adds."I'm sure there is built-in functionality to do something as basic as essentially turning two pages of the same spreadsheet into one. In fact, I know I could import data from an Excel file and have it "append" to a preexisting table if the fields are all the same.

However, I'd like to know how to do it when the tables are already in Access (without having to learn any SQL, mostly because I'm fairly certain I don't need to know SQL to do something like this). I've heard of Append and Update Queries and given their names, it sounds like they'd be useful, maybe with use of "Totals" and "Group By" to get rid of duplicates; however, I can't seem to get any of this to work right.

Mostly, when I think I am doing an Append correctly, it doesn't add new data at the bottom of a table; it just wipes out all the data that was there, and replaces it with data from the source. For instance, when I've been running an Append Query to get my "List September Adds" rows into my "Main List" table, what I end up with is only the "List September Adds" rows, and all the "Main List" rows gone.

View 7 Replies View Related

Adding And Subtracting Dates From Current Date

May 7, 2013

My first question is how to set a column to be the current date read by the computer.

Example: It will automatically say 5/8/2013 tomorrow without needing updating.

Question 2: How to add a number of days to the current date.


Add 7 days to the current date.

on 5/8/2013 it will read 5/15/2013, then automatically update to 5/16/2013 on 5/9.

View 1 Replies View Related

Adding And Subtracting Totals From 3 Separate Reports

Sep 4, 2015

I have 3 reports

Report 1. is attendance from January to June
Report 2. is from June to December
Report 3. is from January to December

The are all run from the same query with different Between Dates.

The scenario is that Paul attended the support group from Jan to June so in the Jan to June report he is counted once. The query removes duplicate values

Paul attended the same support group from June to December so in the June to December report he is counted once. The query removes duplicate values.

This shows the Paul attended he support group from Jan to December so in the June to December report he is counted once. The query removes duplicate values, if you removed the duplicates from this query he would be counted twice.

Not all people attend the same group for 12 months but if they do the are a duplicate and Paul is a duplicate. That is ok because we need to know now many duplicates there are for the year.

If I print all 3 reports with the duplicates remove query and you looked at the totals it would show (Example) 30 attendees in the 1st report and 30 attendees in the 2nd report but in the 3rd report it would show 59 not 60 which is the total of reports 1 & 2. we need to report these figures as 1st 6 months with 30 and the second 6 months with 30 but the whole year would be with 59 and 1 duplicate.

The way it is now I have to run all 3 report and do the math by hand this way.

Is there a way on the to do the math with code calculations on the full year report with the figures from the 2, 6 month reports to automatically show total attendance for the year 59 with 1 duplicate?

View 1 Replies View Related

Queries :: Adding Rows To Table - Bypass Field Controls

Jan 1, 2015

There is a table with many columns. I want to add many rows to this table. The easiest way is to write a query to do that. But most of the columns have validation criteria, lookup tables, default values, nulls not accepted condition and what not. Where as my new records to be added have only the values in 4 columns and the rest are null. Is there a parameter that can be used to bypass all these controls and add the records? You are going to say "You will destroy the integrity and consistency of the database".

View 1 Replies View Related

Subtracting Info Using Data From Another Table

Nov 18, 2007

hi, i've been trying to find a solution to this problem but i can't seem to get my head around it. i have searched through the forums but found all the threads to be confusing although it probably is simple.

im a doing a project for a2 for a trailer tent company

got 2 tables where the problem lies in

they only sell one trailer tent to a customer
when a trailer tent is sold, i want to update no in stock in stock table
i.e. trailer tent sold --> "no in stock"field for that particular trailer tent goes down by one. I would like the user to be able to change stock level any time as well so don't want it to be fully automated

sorry if i'm not making it clear, not really sure how to explain it

thanks for help, much appreciated

View 3 Replies View Related

Adding Multiple Rows At Once

Apr 21, 2006

Could somebody please help me! I am new to access and trying to create a database that brings together information from a number of different sources, into one large database.
I need to import a large amount of data from another access table and I would like to just cut and paste it into my table.
BUT, I canít figure out how to add multiple rows at once to my existing table. The data that I am importing contains about 6,000 rows and 4 columns. Can anybody tell me how I can do this without having to manually create 6,000 rows, one at a time? I really do not want to do that. Thank you for your help.

View 2 Replies View Related

Linking Tables - Adding Rows

Aug 12, 2007


I am trying to figure out how to link 2 tables to add rows based on corresponding data...below is an example...

First table contains general info...
row 1: column 1 = John Doe column 2 = Sports
row 2: coulmn 2 = Bob Smith column 2 = Music

Second table contains detailed info
column 1 = Sports
row 1= Football row 2 = Baseball row 3 = Soccer

Expected Result:

I would like multiple rows to show for each person linking all of detailed info...
row 1: column 1 = John Doe Column 2 = Football
row 2: column 1 = John Doe Column 2 = Soccer
row 3: column 1 = John Doe Column 2 = Baseball


View 7 Replies View Related

Adding Single Date To All Rows

May 14, 2013

I want create a query that adds a field called DATE to my existing data. I want every record to have a date of 1/1/2011 populated in it. I need it to have date formatting so that I can add/subtract other dates from it.


How to do this?

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

Modules & VBA :: Adding Additional Rows To Details

Nov 11, 2013

I have a form where we fill in information for supply of PPE to employees.

Each item must be signed for on a printed report.

I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.

For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.

The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)

IssueIDDateProvidedItem_Name AmountIssued
0001 01/11/2013 Gloves (Orange) 10
0001 01/11/2013 Hard Hats (Black) 2
0002 02/11/2013 Hi Viz (Large) 5

The report I aim to generate from this should look as attached ...

The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.

View 4 Replies View Related

Percentage Based On Adjacent Column

Nov 6, 2005

Here's a sample of the query code for survey I made, for example
Question 1 will be
1) Male or Female?
(radio button answer_id 111) Male
(radio button answer_id 112) Female

etc etc...

My question is how do I set my query to show a graph / percentage value of total unique answer_id's in relation to the question_id.

e.g. Question 1: 66.67% chose 111, 33.3% chose 112


I hope I was clear enough am quite new to Access and I really thought I could do this with pressing a few filter/sort buttons but alas I haven't found a way yet.

View 1 Replies View Related

Access Reports: Adding Lines To Separate Rows And Columns Like Excel

Nov 4, 2004

Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.

Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?

thks in advance...and I will sum.

the ravenman.

View 1 Replies View Related

Modules & VBA :: Unbound Text Boxes And A Search Button Adjacent

Jul 23, 2013

I have two unbound text boxes and a search button adjacent each that allows me to search for a user via two methods:

1) Payroll ID
2) Surname

They have the following code:


Private Sub txtPayrollIDSearch_AfterUpdate()
Dim sWHERE As String
sWHERE = "[PayrollID] = " & Me.txtPayrollIDSearch
sWHERE = "[PayrollID] = '" & Me.txtPayrollIDSearch & "'"


Then minor differences between the above and the Surname search.I have two questions:

1) How do I make the search more friendly by allowing it to find partial matches, i.e., a user has a surname of 'Smith' but I want to search for 'Smi'?

2) How can I display further error messages if there aren't any matches?

View 14 Replies View Related

Modules & VBA :: Split Table Record Into Multiple Records / Rows In A New Table

Nov 10, 2014

In a situation where I imported an excel file with so many columns and split them into two temp tables and they are linked using a key.

the data has a fixed part lets say

Field1....Field2.....Filed3.....Field4...then Field5.....Field6.....Field7....Field8 is the same data range as Field9...Field10...Field11...Field12. I would want to split this data into multiple rows like this

Field 1 Field2 Field3 Field4 Field5 Field6 Field7 Field8
Field 1 Field2 Field3 Field4 Field9 field10 field11 field12 and so own...

What is the best approach?

View 3 Replies View Related

Modules & VBA :: Add Rows To Table Based On Record Count Of Another Table

Mar 10, 2015

I am trying to assign teams to players. I have an import table with all of the players information listed. What I want to do is determine the count of players in a given city. For every 9 players I want to add a new record to the Team table and assign the team number (auto incremented for each team created). Then I want to add the players to the Players table with the Team Number that was created.

View 8 Replies View Related

General :: Highlighting Particular Text From Nearby Field To Adjacent Field

Nov 4, 2013

I am having a database in access and i want to highlight particular text from nearby column to the next column. For example,


The first column text it should take as query and then it should only search the adjacent cell and highlight that. While searching in the internet i came across a code also, But i dont know whether i can use that code or not.

ALTER PROCEDURE [dbo].[Search]
@searchTerm nvarchar(100),
@style nvarchar(100) = 'font-weight:bold; background-color:yellow',
@maxLen INT = 100


View 4 Replies View Related

Delete All Rows In Master Table If Identical In Second Table - SQL

Jan 13, 2006

A SELECT works:

from mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield

But none of these do:

FROM mastertable
INNER JOIN secondarytable
ON mastertable.Identicalfield = secondarytable.Identicalfield
**Incorrect syntax near the keyword 'INNER'.**

FROM mastertable JOIN secondarytable
ON (mastertable.Identicalfield = secondarytable.Identicalfield)
**Incorrect syntax near the keyword 'JOIN'.**

FROM mastertable
WHERE (mastertable.Identicalfield = secondarytable.Identicalfield)
**The column prefix 'secondarytable' does not match with a table name or alias name used in the query.**

FROM mastertable
FROM secondarytable)
**Incorrect syntax near the keyword 'IN'.**

Can anyone help? This should be easy, shouldn't it?!
Thank you.

View 2 Replies View Related

Reports :: Can Shrink On Reports With Adjacent Text Boxes

Apr 11, 2013

I have a report that has Bill to and Ship to addresses. They are positioned across from one another (ship to on the right and bill to on the left of the page). The issue is that the 'can shrink' doesn't work when there is data in the same field on the other address..

View 1 Replies View Related

Run Out Of Table Rows

Jun 1, 2007

The following is for a successful pet grooming business.

I have a Customer Table with a Customer ID as primary key. I have a Pet Name Table with a Pet ID as primary key. One customer can have many pets. The Pet Table is linked to the Customer Table via the Cust ID. I have a Visit Table with an Auto Number for each visit. The Visit Table is linked via the Pet ID number. Each pet can have many visits. That's the problem. We have exceeded the 64,000 rows in the Visit Table. All three linked tables support all the queries, forms and reports. How can I continue beyond 64,000 visits.
Thanks, Jed

View 10 Replies View Related

Max # Rows In A Table

Jun 14, 2007

I tried to search for the answer but not luck. I know there is a max size around 2gb for a DB but is there a row limit for a table or is it based upon size?

View 4 Replies View Related

How To Delete Rows From Table?

Nov 7, 2006

How I can delete all rows from my table whit on query? Also I want that when I delete rows from my table index and other settings will stay in table.

View 1 Replies View Related

Select Last 4 Rows Of A Table

Dec 17, 2004

Is there any way to get the last 4 rows which have been entered into the table?

Thank You.

View 2 Replies View Related

Compare Rows In A Table

Nov 14, 2005


I have a table with four columns - A, B, C, D
I want to compare the values in columns A, B and C in row 1, with the values in row 2 and if they are the same, I want to copy the value that is in column D in row 1 into column D in row 2. I then want to move to the next row in the table and compare the values in row 2 with the values in row 3 in the same way. This will continue for all rows in the table.

Any help would be greatly appreciated as my VB is not up to much.

Thanks in advance

View 2 Replies View Related

Copyrights 2005-15, All rights reserved