Sub Query Problem

[Geoff Codd]

I am trying to create a query which shows the one yers rates against the others side by side.

ie

31/01/02 5p 31/01/03 5.5p

I am geting the data from one table which is a continuous list of month and rate.

I tried the following query

SELECT [qryEnergy_Report_Data_Unit_Rate_(Table_3)].Code, [qryEnergy_Report_Data_Unit_Rate_(Table_3)].Code2, (Select[Unit_Rate]from [qryEnergy_Report_Data_Unit_Rate_(Table_3)]where[Date]<#31/01/2003#) AS PrevUnit, (Select[Unit_Rate]from [qryEnergy_Report_Data_Unit_Rate_(Table_3)]where[Date]>#31/01/2003#) AS PresUnit
FROM [qryEnergy_Report_Data_Unit_Rate_(Table_3)];

But I get the error - "At most one record can be returned by this subquery"

Does anyone have any ideas as to how I can do this in one query

Thanks in advance
Geoff

 

[Jon K]

Since the table is a continuous list of monthly rate, you can use (Select First() ...) in the subquery so that only one record is returned by the subquery for each month. See the database attached. Hope it helps.

 

[Geoff Codd]

This is great Jon, if I could only pinch your brain for a week all my access problems would be over