Selected records based on option

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alex H - 08 Dec 2005 11:05 GMT

Hi I have a table the records of which have two addresses, work and home.
We also have a preferred address field. I need to do mailings to the whole
database using their preferred address.

Is there a way that I can create a query to select the records into a single
query?

Thanks
A

Reply to this Message

Jeff Boyce - 08 Dec 2005 13:43 GMT

Alex

Not sure what you mean by

> Is there a way that I can create a query to select the records into a single
> query?

From your description, it sounds like you have three address field sets
(home, work, preferred). What are you trying to do (rather than "how" are
you trying to do...)?

Signature

Regards

Jeff Boyce
<Office/Access MVP>

Reply to this Message

John Spencer - 08 Dec 2005 14:15 GMT

So what does the Preferred address field contain? Text, a true/false value,
something else.
You can use an IIF clause to choose the address parts.

SELECT SomeNameField
, IIF(PreferredAddress = "WORK", WorkAddressStreet, HomeAddressStreet) as
StreetAddr
, IIF(PreferredAddress = "WORK", WorkAddressCity, HomeAddressCity) as
CityAddr
FROM YourTable

If you are using the grid to build the query
FIELD: StreetAddr: IIF(PreferredAddress = "WORK", WorkAddressStreet,
HomeAddressStreet)

> Hi I have a table the records of which have two addresses, work and home.
> We also have a preferred address field. I need to do mailings to the
[quoted text clipped - 5 lines]
> Thanks
> A

Reply to this Message

Alex H - 08 Dec 2005 15:20 GMT

sorry wasn't very clear

We have two sets of address fields one for home and one for work. The
preferred address field indicates to the user which tthe addressee would
prefr the user to use - Home or Work.

When its a single letter that is being written, its not a problem, we just
select the address for whichever the preference is set.
However, we need to do a general mailing, and I was trying to set up a
report that would use the correct address based on wether the preference
field was set to Home or Work.

Apologies for not making myself more clearer in the first post

A

> Hi I have a table the records of which have two addresses, work and home.
> We also have a preferred address field. I need to do mailings to the
[quoted text clipped - 5 lines]
> Thanks
> A

Reply to this Message

TedMi - 08 Dec 2005 16:44 GMT

Use a UNION query:
SELECT Name, HomeAddr, HomeCity, HomeSt, HomeZip
FROM YourTable WHERE PrefAddr = "Home"
UNION
SELECT NAME, WorkAddr, WorkCity, WorkSt, WorkZip
FROM YourTable WHERE PrefAddr = "Work"

Signature

Ted

Reply to this Message

John Spencer - 08 Dec 2005 19:15 GMT

Good idea. I wish I would have thought of that.

> Use a UNION query:
> SELECT Name, HomeAddr, HomeCity, HomeSt, HomeZip
> FROM YourTable WHERE PrefAddr = "Home"
> UNION
> SELECT NAME, WorkAddr, WorkCity, WorkSt, WorkZip
> FROM YourTable WHERE PrefAddr = "Work"

Reply to this Message

Alex Hammerstein - 08 Dec 2005 20:01 GMT

Thanks both

Alex

> Hi I have a table the records of which have two addresses, work and home.
> We also have a preferred address field. I need to do mailings to the
[quoted text clipped - 5 lines]
> Thanks
> A

Reply to this Message