Selected records based on option

  • Thread starter Thread starter Alex H
  • Start date Start date
A

Alex H

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
 
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?
Click to expand...

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...)?
 
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)
 
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
 
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"
 
Back
Top