Subtract the second row from the first row in a Query

[brandon20]

Hi,

I was wondering how this would be possible. I come across this kind of problem very often so finding a solution would be amazing!!

I need to subtract the second row from the first row for each well in my access query.

I have data with three fields.

[Name]
[Surface]
[Depth]

This is the table I have

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Surface[/TD]
[TD]Depth[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]A[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]B[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]C[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]D[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]B[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]C[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]D[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What I need: I need to subtract the second row [Depth] from the first row [Depth] for each well in [Name]
As you see below I call this field [Thickness] =SecondRow[Depth]-FirstRow[Depth]

I also drop the row which has last or Max [Depth] for each well in [Name] because I am calculating the thickness and keeping the final row would be redundant.
(this part can be left out if it is too complicated)


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Surface[/TD]
[TD]Depth[/TD]
[TD]Name[/TD]
[TD]Surface[/TD]
[TD]Depth[/TD]
[TD]Thickness[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]A[/TD]
[TD]12[/TD]
[TD]Well1[/TD]
[TD]B[/TD]
[TD]20[/TD]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]B[/TD]
[TD]21[/TD]
[TD]Well1[/TD]
[TD]C[/TD]
[TD]30[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]C[/TD]
[TD]34[/TD]
[TD]Well1[/TD]
[TD]D[/TD]
[TD]40[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]A[/TD]
[TD]5[/TD]
[TD]Well2[/TD]
[TD]B[/TD]
[TD]15[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]B[/TD]
[TD]15[/TD]
[TD]Well2[/TD]
[TD]C[/TD]
[TD]25[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]C[/TD]
[TD]25[/TD]
[TD]Well2[/TD]
[TD]D[/TD]
[TD]35[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help is greatly appreciated!
Thanks!

 

[jtakw]

Hi,

The data in your first table contradicts the data in your second table according to your description, until you clarify (numbers don't match up), it'll be very difficult for anyone to help.

 

[brandon20]

Ah man!!

I dont know how that happend. I really dont like the fact that I cant just cut and paste a table from Excel into this forum.
Or at least I dont know how. I messed up the data writing it in by hand.....

Hopefully this table imports well, although they never do.
Let me know if you have any other questions,


what i have

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Surface[/TD]
[TD]Depth[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]A[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]B[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]C[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]D[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]A[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]B[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]C[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]D[/TD]
[TD]35[/TD]
[/TR]
</tbody>[/TABLE]



what i need

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Surface[/TD]
[TD]Depth[/TD]
[TD]Name[/TD]
[TD]Surface[/TD]
[TD]Depth[/TD]
[TD]Thickness[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]A[/TD]
[TD]12[/TD]
[TD]Well1[/TD]
[TD]B[/TD]
[TD]21[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]B[/TD]
[TD]21[/TD]
[TD]Well1[/TD]
[TD]C[/TD]
[TD]34[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well1[/TD]
[TD]C[/TD]
[TD]34[/TD]
[TD]Well1[/TD]
[TD]D[/TD]
[TD]40[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]A[/TD]
[TD]5[/TD]
[TD]Well2[/TD]
[TD]B[/TD]
[TD]15[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]B[/TD]
[TD]15[/TD]
[TD]Well2[/TD]
[TD]C[/TD]
[TD]25[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Well2[/TD]
[TD]C[/TD]
[TD]25[/TD]
[TD]Well2[/TD]
[TD]D[/TD]
[TD]35[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Do you need help with building the entire second table?, or you already have the second table setup, and just need the formula for "Thickness" column?

 

[brandon20]

really just getting the thickness equation would help.

I just dont know a way to write an equation that would subtract the second row depth from the first row depth of for each well in [Name].

That would solve most of my problems.

Thanks.

 

[jtakw]

really just getting the thickness equation would help.

I just dont know a way to write an equation that would subtract the second row depth from the first row depth of for each well in [Name].

That would solve most of my problems.

Thanks.

Not sure if I understand what you need, because if you already have your second table setup, then it's just a matter of subtracting the H column from the K column...like =K2-H2
But I'll just give you a suggestion based only on your first table...

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Surface	Depth	Thickness		Name	Surface	Depth	Name	Surface	Depth	Thickness
2	Well1	A	12			Well1	A	12	Well1	B	21	9
3	Well1	B	21	9		Well1	B	21	Well1	C	34	13
4	Well1	C	34	13		Well1	C	34	Well1	D	40	6
5	Well1	D	40	6		Well2	A	5	Well2	B	15	10
6	Well2	A	5			Well2	B	15	Well2	C	25	10
7	Well2	B	15	10		Well2	C	25	Well2	D	35	10
8	Well2	C	25	10
9	Well2	D	35	10
Sheet1

Cell Formulas
Range	Formula
L2	=K2-H2
D2	=IF(A2=A1,C2-C1,"")

Copy D2 formula down.

 

[brandon20]

Hey Thanks,

I know how to do this in excel, it is easy.

This is an access forum. I am looking for the answer in ms sql.

I have one table. with the three fields, [name],[surface],[depth]

From this one table I need to figure out how to do something similar to that excel equation.

I am not great at sql. Finding out how to create this in access is a little bit harder,
im sure I could do it in some round about way but I was just asking if there was a best practice to this kind of question because it is so basic.