Newbie query-problems

Hello,
I have just started with SQL and could not find the solution to this
problem:
My access-database lists all the owners with the date they bought
and sold a property.
There are 3 tables:
Owner with fields: ID, Descr_L, Ledgernr
Property with fields: ID, Descr
Transact with fields: Date, Property, Owner, Linenr, Prev.owner,
Prev.Linenr
Each record hold a date, ID of property, ID of previous owner,
linenr of prev.owners ledger, ID of owner and linenr of ownersledger.
When I group by property and sort by date, I get a list where teh
owner on a row equals the prev.owner on the next row.
I like to get a list of breaks in this list (where the owner is NOT
equal prev.owner on the next row) showing:
Property.Descr, Owner.Descr_L, Owner.Ledgernr, Linenr
Please can anyone help me.
Thanksselect yada yada
From Transact t
Inner join ( Select * from Transact t2 where t2.property =
2.property --find the next row
and t2.date = (select min(t3.date) from Transact t3 where
t3.property = t1.property and t3.date < t1.date) ) as PrevRow
--the Previous row is the row in the transact table with the same property
id but the date is the smallest date > than the current row...
THen you can add a where clause
Where t.Prevowner != prevRow.owner
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"E.R. Visser" <ervi@.wxs.nl> wrote in message
news:ct2soi$869$1@.reader10.wxs.nl...
>
> Hello,
> I have just started with SQL and could not find the solution to this
> problem:
> My access-database lists all the owners with the date they bought
> and sold a property.
> There are 3 tables:
> Owner with fields: ID, Descr_L, Ledgernr
> Property with fields: ID, Descr
> Transact with fields: Date, Property, Owner, Linenr, Prev.owner,
> Prev.Linenr
> Each record hold a date, ID of property, ID of previous owner,
> linenr of prev.owners ledger, ID of owner and linenr of ownersledger.
> When I group by property and sort by date, I get a list where teh
> owner on a row equals the prev.owner on the next row.
> I like to get a list of breaks in this list (where the owner is NOT
> equal prev.owner on the next row) showing:
> Property.Descr, Owner.Descr_L, Owner.Ledgernr, Linenr
> Please can anyone help me.
> Thanks
>
>
>
>|||Wayne,
Thank you for your quick respons.
I'll try this solution.
Erik
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> schreef in bericht
news:%23vzTd6hAFHA.2016@.TK2MSFTNGP15.phx.gbl...
> select yada yada
> From Transact t
> Inner join ( Select * from Transact t2 where t2.property =
> 2.property --find the next row
> and t2.date = (select min(t3.date) from Transact t3 where
> t3.property = t1.property and t3.date < t1.date) ) as PrevRow
> --the Previous row is the row in the transact table with the same property
> id but the date is the smallest date > than the current row...
> THen you can add a where clause
> Where t.Prevowner != prevRow.owner
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "E.R. Visser" <ervi@.wxs.nl> wrote in message
> news:ct2soi$869$1@.reader10.wxs.nl...
>