I have a table with Permanent Address(yes/no) with
Permstreet,permcity,permstate,permzip; then in same table Present
Address(yes/no)with Presentstreet,prescity,prestate,preszip.

How do I setup a query that will pull the permanent address if the Permanent
Address is Yes OR the Present Address if the Present Address is Yes. I need
to do this to send a letter to the address that should be used for mailing.

Sharron, it would be possible to do this with a convoluted combination of
IIf() and DLookup() statements, but it would be considerably easier to
design a correctly normalized address system.

It seems that each client in your database could have more than one address,
such as a present address, a permanent address, and possibly other types.
This suggests a one-to-many relation between clients and addresses.

1. Create a small table to hold the possible address types. Just one field:
AddressTypeID Text (24 char) primary key.
Save the table as tblAddressType.
Enter a couple of records:
Present
Permanent

2. Create another table to hold the addresses. Fields:
AddressID AutoNumber primary key
ClientID relates to the primary key of your Client table.
AddressTypeID relates to tblAddressType.AddressTypeID
Priority Number 1 = top priority, 2 = second
choice, ...
Street
City
State
Zip

3. Remove all the address fields from your existing client table.

4. On your client form, create a subform for entering addresses. The subform
will automatically show only address for the client in the main form. Use a
combo for the AddressTypeID. Set Priority 1 for the preferred address.

5. Create a new query. Switch to SQL View (View menu). Paste this:
SELECT TOP 1 Street, City, State, Zip
FROM tblAddress
ORDER BY Priority, AddressID;
Save the query.

6. Presumably you already have an Access report as the "letter" you wish to
send. Create a subreport to show the address fields, based on the query
saved at step 5. The subreport acts as the address panel just below the name
in your report.

With this kind of structure, you can:
- have as many addresses as you want for any client;
- decide to add other types of address in the future (e.g. work, postal);
- set priorities for each kind of address easily;
- create other queries that show preference for a particular type of address
and so on.

A subquery might also be useful for selecting the preferred address. If
subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"SharronTucker" <(E-Mail Removed)> wrote in message
news:678F7CED-CA13-408F-8DE7-(E-Mail Removed)...
>I have a table with Permanent Address(yes/no) with
> Permstreet,permcity,permstate,permzip; then in same table Present
> Address(yes/no)with Presentstreet,prescity,prestate,preszip.
>
> How do I setup a query that will pull the permanent address if the
> Permanent
> Address is Yes OR the Present Address if the Present Address is Yes. I
> need
> to do this to send a letter to the address that should be used for
> mailing.

Sharron,
You need to create a new query. Include the name fields in the QBE grid in
the bottom half of the window.
Start a new column in the same QBE grid as follows:
Address: IIF([Permanent Address],[Permstreet],[Presentstreet])
create a similar one for city, state, and zip.
In the query you will have one address, one city, one state, and one zip
field to use in the Mail Merge.
This works as [Permanent Address] is a Yes/No field, so the IIF test for YES
and uses PERMSTREET, if NO, it use PRESENTSTREET.
--
Hopefully helpful,


"SharronTucker" wrote:

> I have a table with Permanent Address(yes/no) with
> Permstreet,permcity,permstate,permzip; then in same table Present
> Address(yes/no)with Presentstreet,prescity,prestate,preszip.
>
> How do I setup a query that will pull the permanent address if the Permanent
> Address is Yes OR the Present Address if the Present Address is Yes. I need
> to do this to send a letter to the address that should be used for mailing.

This was fantastic. I really, really appreciate your help on this problem.

"Dick D" wrote:

> Sharron,
> You need to create a new query. Include the name fields in the QBE grid in
> the bottom half of the window.
> Start a new column in the same QBE grid as follows:
> Address: IIF([Permanent Address],[Permstreet],[Presentstreet])
> create a similar one for city, state, and zip.
> In the query you will have one address, one city, one state, and one zip
> field to use in the Mail Merge.
> This works as [Permanent Address] is a Yes/No field, so the IIF test for YES
> and uses PERMSTREET, if NO, it use PRESENTSTREET.
> --
> Hopefully helpful,
>
>
> "SharronTucker" wrote:
>
> > I have a table with Permanent Address(yes/no) with
> > Permstreet,permcity,permstate,permzip; then in same table Present
> > Address(yes/no)with Presentstreet,prescity,prestate,preszip.
> >
> > How do I setup a query that will pull the permanent address if the Permanent
> > Address is Yes OR the Present Address if the Present Address is Yes. I need
> > to do this to send a letter to the address that should be used for mailing.

Dick D solution was much easier and faster to complete and get my user on the
road to success, but you bring up some wonderful points and extra tips
regarding subqueries that I will research and test out on some of my
databases. I thank you for your quick response and detailed solution.

"Allen Browne" wrote:

> Sharron, it would be possible to do this with a convoluted combination of
> IIf() and DLookup() statements, but it would be considerably easier to
> design a correctly normalized address system.
>
> It seems that each client in your database could have more than one address,
> such as a present address, a permanent address, and possibly other types.
> This suggests a one-to-many relation between clients and addresses.
>
> 1. Create a small table to hold the possible address types. Just one field:
> AddressTypeID Text (24 char) primary key.
> Save the table as tblAddressType.
> Enter a couple of records:
> Present
> Permanent
>
> 2. Create another table to hold the addresses. Fields:
> AddressID AutoNumber primary key
> ClientID relates to the primary key of your Client table.
> AddressTypeID relates to tblAddressType.AddressTypeID
> Priority Number 1 = top priority, 2 = second
> choice, ...
> Street
> City
> State
> Zip
>
> 3. Remove all the address fields from your existing client table.
>
> 4. On your client form, create a subform for entering addresses. The subform
> will automatically show only address for the client in the main form. Use a
> combo for the AddressTypeID. Set Priority 1 for the preferred address.
>
> 5. Create a new query. Switch to SQL View (View menu). Paste this:
> SELECT TOP 1 Street, City, State, Zip
> FROM tblAddress
> ORDER BY Priority, AddressID;
> Save the query.
>
> 6. Presumably you already have an Access report as the "letter" you wish to
> send. Create a subreport to show the address fields, based on the query
> saved at step 5. The subreport acts as the address panel just below the name
> in your report.
>
> With this kind of structure, you can:
> - have as many addresses as you want for any client;
> - decide to add other types of address in the future (e.g. work, postal);
> - set priorities for each kind of address easily;
> - create other queries that show preference for a particular type of address
> and so on.
>
> A subquery might also be useful for selecting the preferred address. If
> subqueries are new, see:
> How to Create and Use Subqueries
> at:
> http://support.microsoft.com/?id=209066
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "SharronTucker" <(E-Mail Removed)> wrote in message
> news:678F7CED-CA13-408F-8DE7-(E-Mail Removed)...
> >I have a table with Permanent Address(yes/no) with
> > Permstreet,permcity,permstate,permzip; then in same table Present
> > Address(yes/no)with Presentstreet,prescity,prestate,preszip.
> >
> > How do I setup a query that will pull the permanent address if the
> > Permanent
> > Address is Yes OR the Present Address if the Present Address is Yes. I
> > need
> > to do this to send a letter to the address that should be used for
> > mailing.
>
>
>