Return extra column value on same row

[ormsk]

I have a task that needs to return the last value and the previous to last value on the same row.

I know I can return two rows, but for mailmerging purposes, I need these values on the same row.

For example, to keep things simple, we have 2 tables:

Property Table
PrID (Primary Key), Address
1 , 21 High St
2, 41 Smith Road

Rent Table
PrID (Foreign Key), Date, Amount
1, 01/04/2007, 500
1, 01/10/2007, 450
1, 01/04/2008, 600
2, 04/11/2006, 575
2, 04/03/2007,600

I want the resuts to show

PrID, Address, Date, Amount, Previous Amount
1, 21 High St, 01/04/2008, 600,450
2, 41 Smith Road, 04/03/2007,600,575

The previous amount is based on the amount associated with the penultimate item by date, not by value.

Because of other field values in the tables not showing here, I can easily get at the penultimate value, what I am unable to do is to display this value on the same row.

Not sure if it can be achieved or not.

Thanks for any help.


**********************************
There is more than one way to skin a cat...but who wants a skinned cat?

 

[PHV]

What have you tried so far ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ormsk]

I have seen a few examples of a workaround to this problem, but the ones I have seen involve creating a function or require an iterative process.

Unfortunately, the solution I need has to work within 1 SQL statement as the users only have a window that allows a single SQL statement.

I am unfamiliar with nesting of queries and have limited experience beyond basic SQL.

**********************************
There is more than one way to skin a cat...but who wants a skinned cat?

 

[SeeThru]

I think you need to write a funtion you can call from SQL

The function, in a module, would be something like:

Public Function GetLastRent(inputID as long) as variant
dim dbs as database, rst as recordset
set dbs=currentdb
set rst=dbs.openrecordset("SELECT * FROM Rent Where PRID=" & inputID)
if not rst.eof then rst.movelast
if rst.recordcount=<2 then
  getlastrent=null
else
  rst.moveprevious
  getlastrent=rst!amount
end if
end function

Then, in your SQL you just call the function with
GetlastRent([PrID))

This could be expanded to get multiple previous rents (returned as a string) or to get any rent value from the current (0) to 6 weeks (-6) by just changing the number of records to move. you would need to add another argument with the number of periods to go backwards.





SeeThru
Synergy Connections Ltd - Telemarketing Services

http://www.synergyconnections.co.uk

 

[lespaul]

maybe something like:

SELECT P.PrID, P.Address, R1.Date, R1.Amount, R2.Amount
FROM Property As P
INNER JOIN Rent As R1 on P.PrId = R1.PrID
INNER JOIN Rent As R2 on R1.PrID = R2.PrID AND R1.Date > R2.Date

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com