SQL Query - Shifting Rows Up for certain fields

[Marco123]

Hi all,

I have what, on the face of it, is a very simply issue. I have one table (tblMain), which has 11 columns, the first being the data column and the rest of the columns (Field1 to Field 10) having numerical values. E.g.

Date Field1, Field2, Field3…….Field10
01/01/2011 100, 129, 78
02/01/2011 74, 25, 101
03/01/2011
04/01/2011

What I need to be able to do is to shift the values from Field1 and Field2 from the next row/day into Field9 and Field10 in the previous day. For example, if Field1 and Field2 on 02/01/2011 have values 74 and 25, I want to put these values into Field9 and Firld10 for the row 01/01/2011. Any ideas how to do this in SQL?

I’m doing this is Access SQL by the way but hopefully the solution will be generic.

Cheers,

Marck

 

[dhookom]

What is the reason for this? IMO, this seems like a bad use of a database since your table doesn't appear to be normalized and you are storing the same value more than once.

Duane
Hook'D on Access
MS Access MVP

 

[BigRed1212]

That would be IMO also.

What are these data and can you store them as?:

date value type
01/01/2011 100 1
01/01/2011 129 2
01/01/2011 78 3
02/01/2011 74 1
02/01/2011 25 2
02/01/2011 101 3

 

[Marco123]

It’s not actually a database, I’m initially importing a worksheet from an excel file into the database in order to make the manipulation. Easy to do in vba, but quicker if it’s possible in SQL when you have huge volumes of data.

 

[Marco123]

Hi BigRed1212, in theory it would be possible but if I have more rows and columns, the table could potentially become very large.

 

[dhookom]

I wouldn't worry about "become very large".

Is there a single record for each date?

Duane
Hook'D on Access
MS Access MVP

 

[Marco123]

Yes, there will only be one record/row for each date.

 

[dhookom]

I would try something like:

SELECT A.[Date], A.Field1, A.Field2, A.Field3, B.Field1 as Field9, B.Field2 As Field10
FROM tblMain A JOIN tblMain B on A.[Date] = B.[Date]-1

Duane
Hook'D on Access
MS Access MVP

 

[Marco123]

Thanks, does just the job.