Help Please

[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

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

 

[Steve Schapel]

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

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]

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

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

.