Help Please

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

Jaye

Hi,

I have the below Qry
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
Click to expand...
Click to expand...

This will give me the last two meter reading for each
serial number on two rows. However I would like this to
appear on one row. As See Below

Serial_Number / Previous_Reading / Previous_Reading_Date
Current_Reading / Current _Reading_Date

Can anyone help with this

Jaye
 
Jaye,

Try this...

SELECT T1.Serial_Number, Min(T1.Close_Total) AS Previous_Reading,
Max(T1.Close_Total) AS Current_Reading, Min(T1.Close_Reading_Date) AS
Previous_Reading_Date, Max(T1.Close_Reading_Date) AS
Current_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)
GROUP BY T1.Serial_Number
ORDER BY T1.Serial_Number

- Steve Schapel, Microsoft Access MVP


Hi,

I have the below Qry
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
Click to expand...
Click to expand...

This will give me the last two meter reading for each
serial number on two rows. However I would like this to
appear on one row. As See Below

Serial_Number / Previous_Reading / Previous_Reading_Date
Current_Reading / Current _Reading_Date

Can anyone help with this

Jaye
Click to expand...
 
Steve,

Thanks a milion, it works a treat.

Jaye
-----Original Message-----
Jaye,

Try this...

SELECT T1.Serial_Number, Min(T1.Close_Total) AS Previous_Reading,
Max(T1.Close_Total) AS Current_Reading, Min (T1.Close_Reading_Date) AS
Previous_Reading_Date, Max(T1.Close_Reading_Date) AS
Current_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)
GROUP BY T1.Serial_Number
ORDER BY T1.Serial_Number

- Steve Schapel, Microsoft Access MVP


Hi,

I have the below Qry
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
Click to expand...

This will give me the last two meter reading for each
serial number on two rows. However I would like this to
appear on one row. As See Below

Serial_Number / Previous_Reading / Previous_Reading_Date
Current_Reading / Current _Reading_Date

Can anyone help with this

Jaye
Click to expand...

.
Click to expand...
 
Back
Top