subquery prob (repost)

  • Thread starter Thread starter scubadiver
  • Start date Start date
S

scubadiver

SQL

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current month],
(SELECT monthID from E164src AS temp WHERE temp.monthID=E164src.monthID-1) AS
Expr1
FROM E164src;


Eg data

MonthID Livemonth Current month Expr1
1 Aug 06 5
2 Sep 06 1 1
3 Oct 06 4 2
4 Nov 06 3 3

but what I really need is

MonthID Livemonth Current month New month
1 Aug 06 5
2 Sep 06 1 5
3 Oct 06 4 1
4 Nov 06 3 4

I cant think how to modify the SQL

thanks
 
If you can rely on the MonthID always being sequential

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current month],
(SELECT Max([Total])
FROM E164src AS temp
WHERE temp.monthID=E164src.monthID-1) AS Expr1
FROM E164src;

Note that a Sub-query in a SELECT clause of a query can only return one
record and one value (unless you are using the EXISTS operator). That
usually means the sub-query is going to need to use one of the aggregate
functions - First, Last, Min, Max, Avg.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks, what about retrieving every record? Subqueries aren't my forte at
the moment.

John Spencer said:
If you can rely on the MonthID always being sequential

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current month],
(SELECT Max([Total])
FROM E164src AS temp
WHERE temp.monthID=E164src.monthID-1) AS Expr1
FROM E164src;

Note that a Sub-query in a SELECT clause of a query can only return one
record and one value (unless you are using the EXISTS operator). That
usually means the sub-query is going to need to use one of the aggregate
functions - First, Last, Min, Max, Avg.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
SQL

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current
month],
(SELECT monthID from E164src AS temp WHERE temp.monthID=E164src.monthID-1)
AS
Expr1
FROM E164src;


Eg data

MonthID Livemonth Current month Expr1
1 Aug 06 5
2 Sep 06 1 1
3 Oct 06 4 2
4 Nov 06 3 3

but what I really need is

MonthID Livemonth Current month New month
1 Aug 06 5
2 Sep 06 1 5
3 Oct 06 4 1
4 Nov 06 3 4

I cant think how to modify the SQL

thanks
Click to expand...
Click to expand...
 
The SUB query only returns one value. The main query returns many records.

The query as posted should return the data as outlined in your posting. If
you have tried it and it does not work then post back with the problems you
are having.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
Thanks, what about retrieving every record? Subqueries aren't my forte at
the moment.

John Spencer said:
If you can rely on the MonthID always being sequential

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current
month],
(SELECT Max([Total])
FROM E164src AS temp
WHERE temp.monthID=E164src.monthID-1) AS Expr1
FROM E164src;

Note that a Sub-query in a SELECT clause of a query can only return one
record and one value (unless you are using the EXISTS operator). That
usually means the sub-query is going to need to use one of the aggregate
functions - First, Last, Min, Max, Avg.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
SQL

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current
month],
(SELECT monthID from E164src AS temp WHERE
temp.monthID=E164src.monthID-1)
AS
Expr1
FROM E164src;


Eg data

MonthID Livemonth Current month Expr1
1 Aug 06 5
2 Sep 06 1 1
3 Oct 06 4 2
4 Nov 06 3 3

but what I really need is

MonthID Livemonth Current month New month
1 Aug 06 5
2 Sep 06 1 5
3 Oct 06 4 1
4 Nov 06 3 4

I cant think how to modify the SQL

thanks
Click to expand...
Click to expand...
Click to expand...
 
I misunderstood your message. It works.

thanks

John Spencer said:
The SUB query only returns one value. The main query returns many records.

The query as posted should return the data as outlined in your posting. If
you have tried it and it does not work then post back with the problems you
are having.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
Thanks, what about retrieving every record? Subqueries aren't my forte at
the moment.

John Spencer said:
If you can rely on the MonthID always being sequential

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current
month],
(SELECT Max([Total])
FROM E164src AS temp
WHERE temp.monthID=E164src.monthID-1) AS Expr1
FROM E164src;

Note that a Sub-query in a SELECT clause of a query can only return one
record and one value (unless you are using the EXISTS operator). That
usually means the sub-query is going to need to use one of the aggregate
functions - First, Last, Min, Max, Avg.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..


SQL

SELECT E164src.MonthID, E164src.LiveMonth, E164src.Total AS [Current
month],
(SELECT monthID from E164src AS temp WHERE
temp.monthID=E164src.monthID-1)
AS
Expr1
FROM E164src;


Eg data

MonthID Livemonth Current month Expr1
1 Aug 06 5
2 Sep 06 1 1
3 Oct 06 4 2
4 Nov 06 3 3

but what I really need is

MonthID Livemonth Current month New month
1 Aug 06 5
2 Sep 06 1 5
3 Oct 06 4 1
4 Nov 06 3 4

I cant think how to modify the SQL

thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top