return value only once per unique value in list?

[Lost500]

Hi all,

so i am building a query to show all of the leases in all of the tracts that are set up in my db, being that there are many leases in each tract. however the desired output would be each lease record listed with the tract name repeating however many times it has to. that part is easy, the question that i have is that there is also a size field for the tract and i would like to only list it once for each unique tract record so for example

Lease Tract Size
1 1 5
2 1 null
3 1 null
4 2 7
5 2 null

where the size of tract only get's pull in to the results once and every record following for that tract is left blank, even though the size data is there for the tract. can this be done? Thanks for the help i know this post was pretty long.

 

[Andrzejek]

If you are going after:
[tt]
Lease Tract Size
1 1 5
4 2 7[/tt]

Why not:
[tt]
Select * From YourTable
Where Size Is Not Null[/tt]

Have fun.

---- Andy

 

[Lost500]

Thanks actually the table I listed above is the desired result you see I need to list all of the leases and tracts but size only once so right now it looks like

lease. Tract. Size
1. 1. 5
2. 1. 5
3. 1. 5
4. 2. 7
5. 2. 7


But I need to list all of the leases like it is now and their tracts like it is now but I would like to only list the size once so the people using the spreadsheet can add the size field and not over count. It's not my layout of choice but it's what they want. Thanks for the help!

 

[MajP]

Here are a couple

SELECT
tblLease.Lease,
tblLease.Tract,
DiscreteLease.Size
FROM
tblLease
INNER JOIN
tblLease AS DiscreteLease ON tblLease.Tract = DiscreteLease.Tract
WHERE
(((DiscreteLease.Size) Is Not Null))
ORDER BY
tblLease.Lease, tblLease.Tract;

Or
SELECT
A.Lease,
A.Tract,
(select max([size]) from tbllease where tbllease.tract = A.tract) AS MaxSize
FROM
tblLease AS A;

 

[PHV]

What about this ?

SELECT A.Lease, A.Tract, B.Size
FROM yourTable AS A LEFT JOIN (
SELECT MIN(Lease) AS Lease1, Tract, Size FROM yourTable GROUP BY Tract, Size
) AS B ON A.Lease = B.Lease1

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