469,904 Members | 2,498 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,904 developers. It's quick & easy.

multi addresses - only want one per ID

Hi,

I've been having this problem for a while now and seem to be getting closer
to a solution but it's still not perfect.

I have a table of addresses, people (refno's) may have more than one
address, but the preferred one is marked as direct.
I wish to create a query that shows one row for each reference number, and
then either their direct address, or (only if they have no direct address)
any of their other addresses.

This is what I'm working with at the minute...

SELECT ad.*
FROM addresses as ad
WHERE [direct address] = (select min(ad2.[direct address]) from addresses as
ad2 where ad2.refno = ad.refno)

but it doesn't work for people with many addresses that aren't marked as
direct.

Any ideas?
Cheers,
Chris

Nov 12 '05 #1
2 1529
Not Me wrote:
Hi,

I've been having this problem for a while now and seem to be getting closer
to a solution but it's still not perfect.

I have a table of addresses, people (refno's) may have more than one
address, but the preferred one is marked as direct.
I wish to create a query that shows one row for each reference number, and
then either their direct address, or (only if they have no direct address)
any of their other addresses.

This is what I'm working with at the minute...

SELECT ad.*
FROM addresses as ad
WHERE [direct address] = (select min(ad2.[direct address]) from addresses as
ad2 where ad2.refno = ad.refno)

but it doesn't work for people with many addresses that aren't marked as
direct.

Any ideas?
Cheers,
Chris


Chris,
Try....

Select IIf(IsNull([Direct Address]), [Other Address Field Name],[Direct
Address]) as MailAddress, Addresses.[RefNo], Addresses.LastName from
Addresses;
--
Fred
Please reply only to this newsgroup.
I do not respond to personal e-mail.
Nov 12 '05 #2
"fredg" <fg******@example.invalid> wrote in message
news:7B***********************@bgtnsc04-news.ops.worldnet.att.net...
Not Me wrote:
I have a table of addresses, people (refno's) may have more than one
address, but the preferred one is marked as direct.
I wish to create a query that shows one row for each reference number, and then either their direct address, or (only if they have no direct address) any of their other addresses.

This is what I'm working with at the minute...

SELECT ad.*
FROM addresses as ad
WHERE [direct address] = (select min(ad2.[direct address]) from addresses as ad2 where ad2.refno = ad.refno)

but it doesn't work for people with many addresses that aren't marked as
direct.
Select IIf(IsNull([Direct Address]), [Other Address Field Name],[Direct
Address]) as MailAddress, Addresses.[RefNo], Addresses.LastName from
Addresses;


Cheers, but that would result in a list of all addresses for a known person.
I only want to choose one record per ID, and that would be preferably the
direct address, else any of the other addresses for that person.

Thanks,
Chris
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by DebbieG | last post: by
3 posts views Thread by Joe Befumo | last post: by
1 post views Thread by Lyle Fairfield | last post: by
2 posts views Thread by Juuso Hukkanen | last post: by
4 posts views Thread by =?Utf-8?B?U2VyZ2Vp?= | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.