Query

  • Thread starter Thread starter Jaye
  • Start date Start date
J

Jaye

Please can somone advise.

I have a table with (Meter Reading History) in it.

I need to write a qry that will give me the last two
highest values and dates.

ie Serial Number Reading 1 Date Reading 2 Date
123456 25 02/01/03 50 13/04/03
36987 89 06/03/03 500 19/04/03

I will need this to update every time a new reading is
input as I will be wanting to calculate a usage to show
on a form. Does any one know of a good book on complex
queries

Regards

Jaye
 
Try SQL for Mere Mortals

As for the current problem.

SELECT SerialNumber, Readings, ReadingDate
FROM TableName as T1
WHERE ReadingDate In
(SELECT Top 2 ReadingDate
FROM TableName as T2
WHERE T2.SerialNumber = T1.SerialNumber
ORDER BY T2.ReadingDate Desc)
ORDER BY T1.SerialNumber, T1.ReadingDate

If you have more than one reading per Serial Number per Day this can give you
more than two readings for a serial number.

Also, your readings will come out in TWO lines.
 
Hi John,

Thank you for your advise, and I have ordered the book :-)

I have written the below Query but when I run it I am
getting Syntax error 3131.

SELECT Serial_Number, Close_Total, Close_Colour_Total,
Close_Reading_Date
FROM Meter Reading Tbl AS T1
Where Close_Reading_Date In
(SELECT Top 2 Close_Reading_Date
FROM Meter Reading Tbl as T2
WHERE T2.Serial_Number=T1.Serial_Number
ORDER BY T2.Close_Reading_Date DESC)
ORDER BY T1.Serial_Number, T1.Close_Reading_Date

Maybe I got completly the wrong end of the stick

Any more advise would be great
 
Just missing brackets around any field or table names with spaces in them. So try--


SELECT Serial_Number, Close_Total, Close_Colour_Total,
Close_Reading_Date
FROM [Meter Reading Tbl] AS T1
Where Close_Reading_Date In
(SELECT Top 2 Close_Reading_Date
FROM [Meter Reading Tbl] as T2
WHERE T2.Serial_Number=T1.Serial_Number
ORDER BY T2.Close_Reading_Date DESC)
ORDER BY T1.Serial_Number, T1.Close_Reading_Date
 
John,

Much thanks for the below it works.

Is there any way of grouping by Serial_Number so that the
last 2 readings appear on the same line as I would like
to calculate the difference between the reading and the
date.

Jaye
-----Original Message-----
Just missing brackets around any field or table names with spaces in them. So try--


SELECT Serial_Number, Close_Total, Close_Colour_Total,
Close_Reading_Date
FROM [Meter Reading Tbl] AS T1
Where Close_Reading_Date In
(SELECT Top 2 Close_Reading_Date
FROM [Meter Reading Tbl] as T2
WHERE T2.Serial_Number=T1.Serial_Number
ORDER BY T2.Close_Reading_Date DESC)
ORDER BY T1.Serial_Number, T1.Close_Reading_Date
Hi John,

Thank you for your advise, and I have ordered the book :-)

I have written the below Query but when I run it I am
getting Syntax error 3131.

SELECT Serial_Number, Close_Total, Close_Colour_Total,
Close_Reading_Date
FROM Meter Reading Tbl AS T1
Where Close_Reading_Date In
(SELECT Top 2 Close_Reading_Date
FROM Meter Reading Tbl as T2
WHERE T2.Serial_Number=T1.Serial_Number
ORDER BY T2.Close_Reading_Date DESC)
ORDER BY T1.Serial_Number, T1.Close_Reading_Date

Maybe I got completly the wrong end of the stick

Any more advise would be great per
Day this can give you
Click to expand...
.
Click to expand...
 
Sorry, I either I don't know how to do what you want or I don't understand what
you want. Try reposting with a fuller explanation.

I'm going off line tomorrow for a couple of weeks, so I probably won't be
responding to any further questions.

Jaye. said:
John,

Much thanks for the below it works.

Is there any way of grouping by Serial_Number so that the
last 2 readings appear on the same line as I would like
to calculate the difference between the reading and the
date.

Jaye
-----Original Message-----
Just missing brackets around any field or table names with spaces in them. So try--


SELECT Serial_Number, Close_Total, Close_Colour_Total,
Close_Reading_Date
FROM [Meter Reading Tbl] AS T1
Where Close_Reading_Date In
(SELECT Top 2 Close_Reading_Date
FROM [Meter Reading Tbl] as T2
WHERE T2.Serial_Number=T1.Serial_Number
ORDER BY T2.Close_Reading_Date DESC)
ORDER BY T1.Serial_Number, T1.Close_Reading_Date
Hi John,

Thank you for your advise, and I have ordered the book :-)

I have written the below Query but when I run it I am
getting Syntax error 3131.

SELECT Serial_Number, Close_Total, Close_Colour_Total,
Close_Reading_Date
FROM Meter Reading Tbl AS T1
Where Close_Reading_Date In
(SELECT Top 2 Close_Reading_Date
FROM Meter Reading Tbl as T2
WHERE T2.Serial_Number=T1.Serial_Number
ORDER BY T2.Close_Reading_Date DESC)
ORDER BY T1.Serial_Number, T1.Close_Reading_Date

Maybe I got completly the wrong end of the stick

Any more advise would be great
-----Original Message-----
Try SQL for Mere Mortals

As for the current problem.

SELECT SerialNumber, Readings, ReadingDate
FROM TableName as T1
WHERE ReadingDate In
(SELECT Top 2 ReadingDate
FROM TableName as T2
WHERE T2.SerialNumber = T1.SerialNumber
ORDER BY T2.ReadingDate Desc)
ORDER BY T1.SerialNumber, T1.ReadingDate

If you have more than one reading per Serial Number per
Day this can give you
more than two readings for a serial number.

Also, your readings will come out in TWO lines.

Jaye wrote:

Please can somone advise.

I have a table with (Meter Reading History) in it.

I need to write a qry that will give me the last two
highest values and dates.

ie Serial Number Reading 1 Date Reading 2
Date
123456 25 02/01/03 50
13/04/03
36987 89 06/03/03 500
19/04/03

I will need this to update every time a new reading is
input as I will be wanting to calculate a usage to show
on a form. Does any one know of a good book on complex
queries

Regards

Jaye
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top